I have chosen this title for my blog post since troubleshooting this issue was like solving a mystery. One of my customer had recently faced a slow performance while loading the data in a partitioned table. Whenever there is performance issue for an insert statement, the usual suspects which come to our mind is blocking or Disk IO so we started investigating the waits for the query in sys.dm_exec_requests & sys.dm_os_waiting_tasks but to our surprise the insert query was either in running or runnable state which didn’t justify the slow performance of the query.
Further, tracking the Disk IO activity we found that actual insert operation is completed within few mins however before the writes are performed, there is increased read activity on the sql instance & some kind of wait which cannot be identified from the waiting_tasks & dm_exec_requests DMV since they appear to be in running or runnable state.
The table is a partitioned table which is around 1TB in size & during the data load, there is an internal memory pressure on the SQL Server with PLE & Free Pages on the SQL Server going down while Lazy writes/sec increasing which too some extend is expected during the data load but we didn’t have any performance baseline to compare against.
Normally, I wouldn’t check the query plan for slower insert performance since unlike a select query which can be optimized by indexing or by stats refresh, there is limited scope for tuning insert/update or deletes. However I captured the query plan to understand the execution path chosen by the query which helped me solved the mystery.
In the Execution Plan, we see a Sorted Merge Join Operator & Index Scan operation which is used to perform Unique Key validation before performing the actual insert of data. Performing an index scan on 1TB table is definitely not a good plan.
First question, why did optimizer chose a Merge Join & Index Scan for Unique Key validation, the answer to that is yellow exclamation warning shown in the Index Scan operator which is missing statistics indication.
When we see more details on the Index Scan operator, we find missing statistics on Ptnid which is hidden column in partitioned index added starting SQL 2008. More details on Ptn id can be found in Craig’s blog below http://blogs.msdn.com/b/craigfr/archive/2008/08/05/partitioned-indexes-in-sql-server-2008.aspx
The only supported way to rebuild the missing statistics for Ptnid is to rebuild the partitioned index on the partitioned table. Although rebuilding a large partitioned table is a not an easy task & can run upto multiple days, it was worth the time & resources spend.
After rebuilding the indexes on the partitioned table, the performance of the data load improved as per the expectation & we saw the following Execution Plan which improved the performance
The Good Execution Plan uses Nested Loop Join which is non-blocking operator & performs Index Seek operation on the Partitioned table which reduces the memory footprint, Disk IO & improves the performance of insert statement drastically.
Hope you enjoyed the mysterious case of data loading in a partitioned table
Hope this helps !!!
Parikshit Savjani
Premier Field Engineer