{"id":520,"date":"2016-01-13T09:15:21","date_gmt":"2016-01-13T15:15:21","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=520"},"modified":"2016-01-13T09:15:21","modified_gmt":"2016-01-13T15:15:21","slug":"correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/correct-sql-server-tempdb-spills-in-query-plans-caused-by-outdated-statistics\/","title":{"rendered":"Correct SQL Server TempDB Spills in Query Plans Caused by Outdated Statistics"},"content":{"rendered":"

Statistics are an integral part of SQL Server and query performance. In short, the query optimizer uses statistics to create query plans that will improve the overall performance of the queries ran. Each statistic object is created on a list of one or more table columns and includes a histogram displaying the distribution of values in the first column. The histogram can have up to 200 steps, but no more regardless of the number of rows in the column or index.<\/p>\n

In this post we\u2019ll take a look at one specific performance issue that you might find in an execution plan of a query. If you\u2019ve ever noticed the following warning, then this post is for you:<\/p>\n

\"1
\n<\/a>Within the AdventureWorks2014 DB, I\u2019ll use the following query for my example:<\/p>\n

SELECT BusinessEntityID, FirstName, LastName, EmailPromotion\r\nFROM [AdventureWorks2014].[Person].[Person]\r\nWHERE EmailPromotion > 0\r\nORDER BY LastName\r\n<\/pre>\n
Looking at this query I can already tell contention may be present so I\u2019ll go ahead and add a covering index:<\/p>\n
CREATE NONCLUSTERED INDEX [IX_Person_EmailPromotion_INCLUDES]\r\nON [Person].[Person] ([EmailPromotion])\r\nINCLUDE ([BusinessEntityID],[FirstName],[LastName])\r\n<\/pre>\n
When adding the index above, statistics were automatically created and updated. Since the addition of this index I\u2019ve added a few thousand rows to the Person table.<\/p>\n
Let\u2019s run the query and make sure the \u201cInclude Actual Execution Plan\u201d button is selected.<\/p>\n
\"2<\/a><\/p>\n
After the query executes let\u2019s take a look at the execution plan by clicking on the tab in the Results pane:<\/p>\n
\"3
\n<\/a>These warnings were added to SQL Server Management Studio 2012 so if you\u2019re using an older version you may not see this. The spill data to TempDB warning means that the query was not granted enough memory to finish the operation and spilled over into the TempDB to complete the operation. We all know reading from memory is much faster than reading from disk and this is exactly what is happening here. The query read as much as it could from memory before moving over to the TempDB disk.<\/p>\n
Click here to view the rest of this post.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Statistics are an integral part of SQL Server and query performance. In short, the query optimizer uses statistics to create query plans that will improve the overall performance of the queries ran. Each statistic object is created on a list of one or more table columns and includes a histogram displaying the distribution of values […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,114],"tags":[97,115],"class_list":["post-520","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-query-plans","tag-performance-tuning","tag-query-plans"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/520","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=520"}],"version-history":[{"count":3,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/520\/revisions"}],"predecessor-version":[{"id":526,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/520\/revisions\/526"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=520"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=520"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=520"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}