Updating index statistics sql
Now let’s issue an update against statistic object automatically updated.
We could simply assume that the optimizer used this object in the plan.
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.
For a regular table, statistics are out of date when: 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). Sales Order Detail table has 121317 rows: (121317 * 0.20) 500 = 24764 The bulk insert below loads 24775 rows, which should be enough to invalidate statistics. The statistics have not updated, but the modification counter has changed, as expected.
Sales Order Detail IX_Sales Order Detail — Update one column on a table specifying sample size UPDATE STATISTICS Production.
Product(Products) WITH SAMPLE 50 PERCENT [/cc] Using update statistics can give you the granularity of control to only update the out of date statistics, thus having less impact on your production system.
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.
Invoking the Automatic Update We have not run any query against except our bulk insert.