To find the oldest statistics in SQL Server, read the last update time and the number of changes since. This post builds a small demo, lists every statistic with its age and percent changed, and shows why a high change count on its own is not a reason to update by hand.
SET STATISTICS IO ON – SQL in Sixty Seconds #128
I am working with clients on helping them tune their SQL Server Performance. Let us learn how we can use SET STATISTICS IO to check the query’s performance.
Statistics That Never Auto Update: Find the NORECOMPUTE Flag
Statistics that never auto update usually carry a hidden NORECOMPUTE flag, set when an index was created. This post builds a demo table, shows the flagged statistic go stale while its neighbor updates itself, and lists every flagged statistic in a database. It also shows which maintenance commands clear the flag and which keep it.
Column Statistics in SSMS: Update One With the Dialog
You can update column statistics in SSMS from the Properties window of a statistic, but a dialog gives no proof that anything happened. This post lists the steps, then uses T-SQL to prove the update with the modification counter and the sample size. It also shows why a full scan needs a statement of its own.
Background Job Queue: List Active Jobs and Kill a Stats Job
The background job queue holds work that SQL Server runs outside your queries, such as asynchronous statistics updates. This post lists the active jobs, reads the queue counters, catches a real statistics job in a demo and kills it with KILL STATS JOB. It also corrects a common mix-up: the command takes the job ID, not the session ID.
WITH NORECOMPUTE: Stop Auto Updates on One Table
WITH NORECOMPUTE tells SQL Server to stop updating the statistics of one table automatically. This post sets it with UPDATE STATISTICS, proves that the modification counter keeps growing while nothing refreshes, and shows how to turn it off again. It also shows a safer option for the same problem, a persisted sample percent.
SQL SERVER – Rows Sampled – sys.dm_db_stats_properties
Here is the question asked in my yesterday’s Comprehensive Database Performance Health Check about rows sampled from sys.dm_db_stats_properties.