{"id":500,"date":"2013-01-04T07:35:54","date_gmt":"2013-01-04T15:35:54","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/erin\/?p=500"},"modified":"2018-12-14T09:10:04","modified_gmt":"2018-12-14T17:10:04","slug":"understanding-when-statistics-will-automatically-update","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/erin\/understanding-when-statistics-will-automatically-update\/","title":{"rendered":"Understanding When Statistics Will Automatically Update"},"content":{"rendered":"

During the PASS Summit in November I presented a session on Demystifying Database Statistics<\/a> (if you attended Summit but missed it, you can stream it from here). During the session I went through a demo that showed the automatic update of a statistic. The database had the Auto Update Statistics<\/b> option enabled, and I had added almost 25,000 rows to a table with 121,000 rows. The number of rows added via the bulk import was just over the limit to trigger an auto-update of statistics for the next query, which I showed. I had many people ask why statistics did not update immediately after<\/i> the bulk load of the 25,000 rows completed, so I want to step through the demo that I did at Summit to clarify when an automatic update will occur.<\/p>\n

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

Start with a copy of the AdventureWorks2012<\/code> database, which you can download from CodePlex<\/a>. Ensure that the Auto Update Statistics <\/b>option is enabled:<\/p>\n

\r\nIF (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;\r\n<\/pre>\n
We\u2019re going to use a copy of the Sales.SalesOrderDetail<\/code> table for the demo. After we create the table, we will check to see when statistics last updated. We can use various methods to check statistics date, such as DBCC SHOW_STATISTICS<\/a><\/code> or STATS_DATE<\/a><\/code>, but since the release of SP1 for SQL Server 2012, I have exclusively used sys.dm_db_stats_properties<\/a><\/code> to get this information.<\/p>\n
\r\nUSE [AdventureWorks2012];\r\nGO\r\nSELECT *\r\nINTO [Sales].[TestSalesOrderDetail]\r\nFROM [Sales].[SalesOrderDetail];\r\nGO\r\nCREATE CLUSTERED INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[TestSalesOrderDetail] ([SalesOrderID], [SalesOrderDetailID]);\r\nGO\r\nCREATE UNIQUE NONCLUSTERED INDEX [AK_TestSalesOrderDetail_rowguid] ON [Sales].[TestSalesOrderDetail] ([rowguid]);\r\nGO\r\nCREATE NONCLUSTERED INDEX [IX_TestSalesOrderDetail_ProductID] ON [Sales].[TestSalesOrderDetail] ([ProductID]);\r\nGO\r\nSELECT\r\nOBJECT_NAME([sp].[object_id]) AS &quot;Table&quot;,\r\n[sp].[stats_id] AS &quot;Statistic ID&quot;,\r\n[s].[name] AS &quot;Statistic&quot;,\r\n[sp].[last_updated] AS &quot;Last Updated&quot;,\r\n[sp].[rows],\r\n[sp].[rows_sampled],\r\n[sp].[unfiltered_rows],\r\n[sp].[modification_counter] AS &quot;Modifications&quot;\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
Here’s the output:<\/p>\n
\"sys.dm_db_stats_properties<\/a>
sys.dm_db_stats_properties after initially creating the table<\/figcaption><\/figure>\n
This is my go-to DMV for statistics because in addition to including the date that statistics were last updated and row information, I also get a count of modifications since the last statistic update. I just created the table, so the Last Updated date is current, and I have not made any changes so the modification count is 0.<\/p>\n
Invalidating Statistics<\/strong><\/p>\n
For my demo I want to bulk insert enough rows to invalidate the statistics. SQL Server has pre-determined thresholds where it considers statistics to be out-of-date and therefore invalid. The technical article, Statistics Used by the Query Optimizer in Microsoft SQL Server 2008<\/a>, documents these thresholds. For a regular table, statistics are out of date when:<\/p>\n
o The table size has gone from 0 to >0 rows (test 1).<\/span><\/p>\n
o The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then (test 2).<\/span><\/p>\n
o The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered (test 3).<\/span><\/p><\/blockquote>\n
The Sales.SalesOrderDetail table has 121317 rows:<\/p>\n
(121317 * 0.20) + 500 = 24764<\/p>\n
The bulk insert below loads 24775 rows, which should be enough to invalidate statistics. Download the .txt file for the bulk insert here<\/a>.<\/p>\n
\r\nBULK INSERT AdventureWorks2012.Sales.TestSalesOrderDetail\r\nFROM 'C:\\SQLStuff\\Statistics\\Data\\sod.txt'\r\nWITH\r\n(\r\nDATAFILETYPE = 'native',\r\nTABLOCK\r\n);\r\n(24775 row(s) affected)\r\n<\/pre>\n
After the bulk load completes, re-run the query against sys.dm_db_stats_properties<\/code> and review the output:<\/p>\n
\"sys.dm_db_stats_properties<\/a>
sys.dm_db_stats_properties after the bulk import<\/figcaption><\/figure>\n
The statistics have not updated, but the modification counter has changed, as expected. The statistics are now out of date based on the threshold defined previously, and we would expect that a query or data modification against Sales.TestSalesOrderDetail<\/code> would trigger an update of statistics. But before we try that, let\u2019s review what causes the automatic update.<\/p>\n
The aforementioned article states:<\/p>\n
The statistics auto update is triggered by query optimization or by execution of a compiled plan, and it involves only a subset of the columns referred to in the query.<\/span><\/p>\n
When a query is first compiled, if the optimizer needs a particular statistics object, and that statistics object exists, the statistics object is updated if it is out of date. When a query is executed and its plan is in the cache, the statistics the plan depends on are checked to see if they are out of date. If so, the plan is removed from the cache, and during recompilation of the query, the statistics are updated. The plan also is removed from the cache if any of the statistics it depends on have changed.<\/span><\/p><\/blockquote>\n
To be clear, if a query plan exists in cache and that plan uses specific statistics, when the query executes SQL Server checks to see if any of the statistics used in the plan are out of date. If they are, then the automatic update of those statistics occurs.<\/p>\n
If a plan does not exist in cache for a query, then if the optimizer uses a statistics object that is out of date when the plan compiles, SQL Server will automatically update those statistics.<\/p>\n
Invoking the Automatic Update<\/strong><\/p>\n
We have not run any query against Sales.TestSalesOrderDetail<\/code> except our bulk insert. At the time that the query compiled for the bulk insert, no statistics for Sales.TestSalesOrderDetail were out of date; therefore no statistics required an automatic update.<\/p>\n
Now let\u2019s issue an update against Sales.TestSalesOrderDetail<\/code> that will change the ProductID<\/code> for a specific SalesOrderID<\/code>, and then query sys.dm_db_stats_properties<\/code>:<\/p>\n
\r\nUPDATE Sales.TestSalesOrderDetail SET ProductID = 717 WHERE SalesOrderID = 75123;\r\nGO\r\n(3 row(s) affected)\r\n<\/pre>\n
Output:<\/p>\n
\"sys.dm_db_stats_properties<\/a>
sys.dm_db_stats_properties after the update<\/figcaption><\/figure>\n
We can see that the PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID<\/code> statistic object automatically updated. We could simply assume that the optimizer used this object in the plan. However, in SQL Server 2012 we can look at the plan XML and confirm this.<\/p>\n
In his post, Statistics used in a cached query plan<\/a>, Fabiano Neves Amorim describes a method to capture statistics information from the plan. Please<\/em> note the following:<\/p>\n