MSSQL: Maintaining SQL DB Statistics.

MSSQL: Maintaining SQL DB Statistics.
May 22, 2019.

**Notes: All queries have been run and validated in MSSQL 2008/2012/2017.

Statistics are a description of the distribution of data in a column or index.

The query optimizer relies heavily on statistics, so if the stats do not represent what you are looking for very well then the optimizer
can create an inefficient query plan. The optimizer’s query plan is determined before execution, and once the plan is made the execution must either complete or be cancelled. (In other words, there is no mechanism by which after running for a while SQL can see that the plan it made is based on bad statistics and start over.)

Knowing how to evaluate the freshness of your stats sure makes troubleshooting easier when problems occur.

The query below will look at all statistics in the current database and for each one show you the time at which statistics were last updated, as well as the approximate number of rows that have been changed in that statistic since the last update.

SELECT DISTINCT
tablename=object_name(i.object_id)
, o.type_desc
,index_name=i.[name]
, statistics_update_date = STATS_DATE(i.object_id, i.index_id)
, si.rowmodctr
FROM sys.indexes i (nolock)
JOIN sys.objects o (nolock) on
i.object_id=o.object_id
JOIN sys.sysindexes si (nolock) on
i.object_id=si.id
and i.index_id=si.indid
where
o.type = ‘S’
–ignore system objects
and STATS_DATE(i.object_id, i.index_id) is not null
order by si.rowmodctr desc

Updating Stats Using T-SQL:

Update statistics for an index
USE AdventureWorks;
GO
UPDATE STATISTICS Adventureworks. ;
GO

Update statistics for a table
USE AdventureWorks;
GO
UPDATE STATISTICS Adventureworks.;
GO

Update all statistics
USE AdventureWorks
GO
EXEC sp_updatestats;
GO
==================================================
Prakash