{"id":647,"date":"2014-05-02T07:00:14","date_gmt":"2014-05-02T14:00:14","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=647"},"modified":"2014-05-02T20:21:13","modified_gmt":"2014-05-03T03:21:13","slug":"testing-automatic-updates-statistics","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/","title":{"rendered":"Further Testing with Automatic Updates to Statistics"},"content":{"rendered":"

A new question on an older post, Understanding When Statistics Will Automatically Update<\/a>, came in this week.\u00a0 If you look in the comments, you see that Rajendra asks:<\/p>\n

…I want know, whether schema change of a table (adding new \/ (deleting an existing) column) also qualifies for UPDATE STATS?<\/p><\/blockquote>\n

It’s an interesting question that I’d never considered before.\u00a0 I had an instinct about the answer, but the scientist in me wanted to test and prove it, so here we go.\u00a0 The scenario and code are similar to my original post, so this should be a quick read for many of you \ud83d\ude42
\n<\/cite><\/p>\n

The Setup<\/strong><\/p>\n

Start with a copy of the AdventureWorks2012<\/code> database, which you can download from CodePlex<\/a>, on a 2012 SP1 instance, and confirm that the Auto Update Statistics <\/b>option is enabled:<\/p>\n

IF (SELECT COUNT(*) FROM [sys].[databases] WHERE [name] = 'AdventureWorks2012' AND [is_auto_create_stats_on] = 0) = 0\r\nBEGIN\r\nALTER DATABASE [AdventureWorks2012] SET AUTO_UPDATE_STATISTICS ON\r\nEND;<\/pre>\n
Create a copy of the Sales.SalesOrderDemo table for our testing, and add the clustered index and one nonclustered index:<\/p>\n
\r\nUSE [AdventureWorks2012];\r\nGO\r\n\r\nSELECT *\r\nINTO [Sales].[TestSalesOrderDetail]\r\nFROM [Sales].[SalesOrderDetail];\r\nGO\r\n\r\nCREATE CLUSTERED INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[TestSalesOrderDetail] ([SalesOrderID], [SalesOrderDetailID]);\r\nGO\r\n\r\nCREATE NONCLUSTERED INDEX [IX_TestSalesOrderDetail_ProductID] ON [Sales].[TestSalesOrderDetail] ([ProductID]);\r\nGO\r\n<\/pre>\n
Validate the current statistics and modifications; the statistics should be current since we just created the indexes, and modifications should be 0 since we haven’t run any inserts, updates, or deletes:<\/p>\n
\r\n\/*\r\nNote: I refer to this query throughout the post as "the sys.stats query", rather than including it multiple times!\r\n*\/\r\nSELECT\r\nOBJECT_NAME([sp].[object_id]) AS "Table",\r\n[sp].[stats_id] AS "Statistic ID",\r\n[s].[name] AS "Statistic",\r\n[sp].[last_updated] AS "Last Updated",\r\n[sp].[rows],\r\n[sp].[rows_sampled],\r\n[sp].[unfiltered_rows],\r\n[sp].[modification_counter] AS "Modifications"\r\nFROM [sys].[stats] AS [s]\r\nOUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]\r\nWHERE [s].[object_id] = OBJECT_ID(N'Sales.TestSalesOrderDetail');\r\n<\/pre>\n
\"Current<\/a>
Current statistics and no modifications<\/figcaption><\/figure>\n
Excellent, we’re starting with a clean slate.<\/p>\n
The Test<\/strong><\/p>\n
Let’s first add a little bit of data (1000 rows), to up our modification count:<\/p>\n
\r\nBULK INSERT [AdventureWorks2012].[Sales].[TestSalesOrderDetail]\r\nFROM 'S:\\SQLStuff\\Dropbox\\Statistics\\Data\\sod_1000.txt'\r\nWITH\r\n(\r\nDATAFILETYPE = 'native',\r\nTABLOCK\r\n);\r\n<\/pre>\n
When we run our sys.stats<\/code> query again, we see the 1000 modifications for both indexes, because both<\/em> indexes had rows added:<\/p>\n
\"Statistics<\/a>
Statistics and modifications after adding 1000 rows<\/figcaption><\/figure>\n
Great.\u00a0 Now add a new column to the table…let’s pretend that we have a new ProductID, but we want to keep the existing one for historical reasons, so our new column is, creatively, NewProductID:<\/p>\n
\r\nALTER TABLE [Sales].[TestSalesOrderDetail] ADD [NewProductID] INT;\r\n<\/pre>\n
Did adding this column cause statistics to update?\u00a0 This was our main question, and if we run our sys.stats<\/code> query we see:<\/p>\n
\"Statistics<\/a>
Statistics and modifications after adding new column<\/figcaption><\/figure>\n
Nothing changed…\u00a0 Adding a new column to the table does not invoke an automatic update to statistics.\u00a0 Nor should it.\u00a0 We haven’t modified data in any existing<\/em> statistics.\u00a0 We would certainly expect a change in query plans, for existing queries that are modified to use this new column, but adding a new column to a table does not cause any existing statistics for the table to update.<\/p>\n
More Testing (because it’s good practice to keep proving out what we expect)<\/strong><\/p>\n
Just for fun, let’s see what happens if we modify some more data:<\/p>\n
\r\nUPDATE Sales.TestSalesOrderDetail SET ProductID = 717 WHERE SalesOrderID = 75123;\r\nGO\r\n<\/pre>\n
If we check sys.stats<\/code>, we see that our nonclustered index, IX_TestSalesOrderDetail_ProductID, has three more modifications than it had previously, but not enough data has changed overall for statistics to be invalidated.<\/p>\n
\"Statisitics<\/a>
Statisitics and modifications after updating 3 ProductIDs<\/figcaption><\/figure>\n
What happens if we update NewProductID?<\/p>\n
\r\nUPDATE [Sales].[TestSalesOrderDetail]\r\nSET [NewProductID] = [ProductID] + 1000;\r\n<\/pre>\n
Well, we updated 122317 rows, but only the NewProductID column, which isn’t part of either the clustered index or nonclustered index key.\u00a0 Therefore, no update to statistics:<\/p>\n
\"Statistics<\/a>
Statistics and modifications after updating NewProductID<\/figcaption><\/figure>\n
To prove this out further, create a nonclustered index on NewProductID:<\/p>\n
\r\nCREATE NONCLUSTERED INDEX [NCI_TestSalesOrderDetail_NewProductID] ON [Sales].[TestSalesOrderDetail] ([NewProductID]);\r\nGO\r\n<\/pre>\n
And if we verify the statistics and modifications, we see that our new NCI has zero modifications, as expected.<\/p>\n
\"Statistics<\/a>
Statistics and updates after adding the nonclustered index on NewProductID<\/figcaption><\/figure>\n
We’ll make another massive update to NewProductID, because the first update wasn’t correct, and then check sys.stats<\/code> again:<\/p>\n
\r\nUPDATE [Sales].[TestSalesOrderDetail]\r\nSET [NewProductID] = [ProductID] + 10000;\r\n<\/pre>\n
\"Statistics<\/a>
Statistics and modifications after updating NewProductID a second time<\/figcaption><\/figure>\n
Now we see the modifications – all 122317 of them.\u00a0 If we query the NewProductID column now, we should invoke an automatic update to statistics, because they are invalid:<\/p>\n
\r\nSELECT NewProductID, SalesOrderID\r\nFROM [Sales].[TestSalesOrderDetail]\r\nWHERE NewProductID = 10718;\r\n<\/pre>\n
\"Statistics<\/a>
Statistics and modifications after an auto update was invoked<\/figcaption><\/figure>\n
And there you have it.\u00a0 Adding a column to a table will not cause an automatic update to statistics to occur.\u00a0 Automatic updates occur only when the threshold for modifications has been exceeded for a statistics key.<\/p>\n
\r\n\/*\r\nclean up code\r\n*\/\r\nUSE [AdventureWorks2012];\r\nGO\r\nDROP TABLE [Sales].[TestSalesOrderDetail];\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
A new question on an older post, Understanding When Statistics Will Automatically Update, came in this week.\u00a0 If you look in the comments, you see that Rajendra asks: …I want know, whether schema change of a table (adding new \/ (deleting an existing) column) also qualifies for UPDATE STATS? It’s an interesting question that I’d […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"yoast_head":"\nFurther Testing with Automatic Updates to Statistics - Erin Stellato<\/title>\n<meta name=\"description\" content=\"Changes in data distribution invalidate statistics and cause automatic updates, does adding a column to a table do the same? Read on to find out...\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Further Testing with Automatic Updates to Statistics - Erin Stellato\" \/>\n<meta property=\"og:description\" content=\"Changes in data distribution invalidate statistics and cause automatic updates, does adding a column to a table do the same? Read on to find out...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/\" \/>\n<meta property=\"og:site_name\" content=\"Erin Stellato\" \/>\n<meta property=\"article:published_time\" content=\"2014-05-02T14:00:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-05-03T03:21:13+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2014\/04\/1-1024x56.png\" \/>\n<meta name=\"author\" content=\"Erin Stellato\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Erin Stellato\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/\",\"name\":\"Further Testing with Automatic Updates to Statistics - Erin Stellato\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\"},\"datePublished\":\"2014-05-02T14:00:14+00:00\",\"dateModified\":\"2014-05-03T03:21:13+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\"},\"description\":\"Changes in data distribution invalidate statistics and cause automatic updates, does adding a column to a table do the same? Read on to find out...\",\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Further Testing with Automatic Updates to Statistics\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/\",\"name\":\"Erin Stellato\",\"description\":\"The SQL Sequel\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158\",\"name\":\"Erin Stellato\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g\",\"caption\":\"Erin Stellato\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/erin\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Further Testing with Automatic Updates to Statistics - Erin Stellato","description":"Changes in data distribution invalidate statistics and cause automatic updates, does adding a column to a table do the same? Read on to find out...","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/","og_locale":"en_US","og_type":"article","og_title":"Further Testing with Automatic Updates to Statistics - Erin Stellato","og_description":"Changes in data distribution invalidate statistics and cause automatic updates, does adding a column to a table do the same? Read on to find out...","og_url":"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/","og_site_name":"Erin Stellato","article_published_time":"2014-05-02T14:00:14+00:00","article_modified_time":"2014-05-03T03:21:13+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-content\/uploads\/2014\/04\/1-1024x56.png"}],"author":"Erin Stellato","twitter_misc":{"Written by":"Erin Stellato","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/","name":"Further Testing with Automatic Updates to Statistics - Erin Stellato","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website"},"datePublished":"2014-05-02T14:00:14+00:00","dateModified":"2014-05-03T03:21:13+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158"},"description":"Changes in data distribution invalidate statistics and cause automatic updates, does adding a column to a table do the same? Read on to find out...","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/testing-automatic-updates-statistics\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/erin\/"},{"@type":"ListItem","position":2,"name":"Further Testing with Automatic Updates to Statistics"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/erin\/","name":"Erin Stellato","description":"The SQL Sequel","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/erin\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/76170223ffffa1df03fd9be5b66cb158","name":"Erin Stellato","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/erin\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0c8b485bd54ea26b57e99f79b525f409?s=96&d=mm&r=g","caption":"Erin Stellato"},"sameAs":["http:\/\/3.209.169.194\/blogs\/erin"],"url":"https:\/\/www.sqlskills.com\/blogs\/erin\/author\/erin\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/647"}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/comments?post=647"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/posts\/647\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/media?parent=647"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/categories?post=647"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/erin\/wp-json\/wp\/v2\/tags?post=647"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}