Updating index statistics sql
Invalidating Statistics 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.This command gives much better granularity of control: [cc lang=”sql”] — Update all statistics on a table UPDATE STATISTICS Sales.
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.
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.
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.