Updating index statistics sql Adultchatdirect

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; GO DBCC TRACEON (8666); GO WITH XMLNAMESPACES (' as p) SELECT AS SQLCommand, qp.query_plan, Stats Used.XMLCol.value('@Field Value','NVar Char(500)') AS Stats Name FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp CROSS APPLY sys.dm_exec_sql_text (cp.plan_handle) qt CROSS APPLY query_plan.nodes('//p: Field[@Field Name="wsz Stat Name"]') Stats Used(XMLCol) WHERE LIKE '%UPDATE%' AND LIKE '%Product ID%'; GO DBCC TRACEOFF(8666); GO statistic object in the query plan, and because it was out of date, SQL Server automatically updated it.

The statistics are now out of date based on the threshold defined previously, and we would expect that a query or data modification against 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.

At the time that the query compiled for the bulk insert, no statistics for Sales.

Test Sales Order Detail were out of date; therefore no statistics required an automatic update.

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.

I had many people ask why statistics did not update immediately after 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.

The Setup Start with a copy of the USE [Adventure Works2012]; GO SELECT * INTO [Sales].[Test Sales Order Detail] FROM [Sales].[Sales Order Detail]; GO CREATE CLUSTERED INDEX [PK_Sales Order Detail_Sales Order ID_Sales Order Detail ID] ON [Sales].[Test Sales Order Detail] ([Sales Order ID], [Sales Order Detail ID]); GO CREATE UNIQUE NONCLUSTERED INDEX [AK_Test Sales Order Detail_rowguid] ON [Sales].[Test Sales Order Detail] ([rowguid]); GO CREATE NONCLUSTERED INDEX [IX_Test Sales Order Detail_Product ID] ON [Sales].[Test Sales Order Detail] ([Product ID]); GO SELECT OBJECT_NAME([sp].[object_id]) AS "Table", [sp].[stats_id] AS "Statistic ID", [s].[name] AS "Statistic", [sp].[last_updated] AS "Last Updated", [sp].[rows], [sp].[rows_sampled], [sp].[unfiltered_rows], [sp].[modification_counter] AS "Modifications" FROM [sys].[stats] AS [s] OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp] WHERE [s].[object_id] = OBJECT_ID(N'Sales.

If you are running earlier versions of SQL Server, you can see what statistics the optimizer loads using the undocumented trace flags 92.

Paul White has an excellent post, How to Find the Statistics Used to Compile an Execution Plan, which I recommend reading if you’re interested.

However, in SQL Server 2012 we can look at the plan XML and confirm this.

Tags: , ,