Updating and creating indexes unsuccessful Masterbation chat sites
The technical article, Statistics Used by the Query Optimizer in Microsoft SQL Server 2008, documents these thresholds.
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 = 24764The 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.
One thing I want to point out: the for three rows (the modification counter for the statistic increased from 24775 to 24778), the optimizer did not use that statistic in the plan.
If the plan does not use a statistic, the statistic will not automatically update, even if the query modifies columns in said statistic key.
Now let’s issue an update against statistic object automatically updated.
We could simply assume that the optimizer used this object in the plan.
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.
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.
Combined with “fullscan”, updating all statistics can become a significant amount of work.
“Fullscan” means that to update a statistic, SQL Server will scan 100% of the values in the index or column. If SQL Server needs to update column level statistics for the same table, it could potentially use a single scan and update multiple stats, right?