Find Missing Table and Index Statistics

Missing statistics can severely impact execution plan generation. If the statistics are out of date SQL Server will behave in bizarre ways. When this becomes the problem, it is very hard to troubleshoot because there are no physical indicators. Instead, everything just runs poorly. In order to find missing our out of date statistics, run the following:

[cc lang=”sql”]
SELECT
Last_Updated = STATS_DATE(si.id, si.indid)
,TableName = object_name(si.id)
,Name = RTRIM(si.name)
,Size = DATALENGTH (si.statblob)
FROM
sysindexes si WITH (nolock)
WHERE OBJECTPROPERTY(si.id, N’IsUserTable’) = 1
–AND INDEXPROPERTY (si.id , si.name , ‘IsAutoStatistics’ ) = 0
order by last_updated, tablename
[/cc]

2 comments
CaveMan 02 May 2013 at 12:46 pm

Thank you for the code, it helps.

Note: the “name” column is null when there is no index on the table.

Brett Shearer 07 Nov 2012 at 12:35 am

This does not find missing statistics.

Featured Articles

 Site Author

  • Thanks for visiting!