x

How to determine that STATISTICS are out of date

Hi,

How do i find out whats the accuracy of current statistics and whether they need the update?

Regards Shant

more ▼

asked Mar 12, 2012 at 05:24 AM in Default

avatar image

shashantpundir
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

As far as out of date statistics are concerned, it would be totally upto you which would you consider out of date. In following script I have marked the statistics out of date if the actual rows count are not equal to the rowscount for the stats. Now you can tweak the criteria according to your requirement.

IF OBJECT_ID('tempdb..#AllTables') IS NOT NULL DROP TABLE #AllTables

IF OBJECT_ID('tempdb..#SHOWSTATISTICSOUTPUT') IS NOT NULL DROP TABLE #SHOWSTATISTICSOUTPUT

DECLARE @SchemaName NVARCHAR(128) , @TableName NVARCHAR(128) , @Index_STATS_Name NVARCHAR(128)

CREATE TABLE #SHOWSTATISTICSOUTPUT ( ID INT IDENTITY PRIMARY KEY ,[NAME] NVARCHAR(128) ,LastUpdatedDate DATETIME ,[Rows] BIGINT ,[Rows Sampled] BIGINT ,[Steps] INT ,Density INT ,[Average key length] INT ,[String Index] VARCHAR(10) )

SELECT DISTINCT ISNULL(SCHEMA_NAME([T].[schema_id]),'dbo') SchemaName , [T].[name] TableName , ISNULL(( SELECT SUM(spart.rows) FROM sys.partitions spart WHERE spart.object_id = T.object_id AND spart.index_id < 2 ), 0) AS [RowCount] , ISNULL([i].[name], [s].[name]) AS Index_STATS_Name , CAST(0 AS BIGINT) AS StatsRowCount INTO #AllTables FROM sys.[tables] AS T INNER JOIN sys.[indexes] AS I ON [T].[object_id] = [I].[object_id] INNER JOIN sys.[stats] AS S ON [I].[object_id] = [S].[object_id] WHERE [i].[index_id] > 0

CREATE CLUSTERED INDEX IX_CLUS ON [#AllTables] ([TableName],[SchemaName], [Index_STATS_Name])

DECLARE PopulateStatsRows CURSOR FAST_FORWARD LOCAL FOR SELECT [SchemaName] , [TableName] , [Index_STATS_Name] FROM [#AllTables]

OPEN PopulateStatsRows

FETCH NEXT FROM PopulateStatsRows INTO @SchemaName , @TableName , @Index_STATS_Name

WHILE @@FETCH_STATUS = 0 BEGIN INSERT [#SHOWSTATISTICSOUTPUT] ( [NAME] ,[LastUpdatedDate] ,[Rows] ,[Rows Sampled] ,[Steps] ,[Density] ,[Average key length] ,[String Index] ) EXECUTE ('DBCC SHOW_STATISTICS('''+ @SchemaName + '.' + @TableName + ''',' + @Index_STATS_Name + ') WITH STAT_HEADER')

UPDATE AT SET [StatsRowCount] = ISNULL([Rows],0) FROM [#AllTables] AS AT INNER JOIN [#SHOWSTATISTICSOUTPUT] AS S ON [NAME] = [Index_STATS_Name] WHERE [Index_STATS_Name] = @Index_STATS_Name AND [TableName] = @TableName AND [SchemaName] = @SchemaName

FETCH NEXT FROM PopulateStatsRows INTO @SchemaName , @TableName , @Index_STATS_Name

END

CLOSE PopulateStatsRows DEALLOCATE PopulateStatsRows

SELECT , CASE WHEN [RowCount] <> [StatsRowCount] THEN 'Yes' ELSE 'No' END AreStatsOutOfDate FROM [#AllTables] AS AT --SELECT FROM [#SHOWSTATISTICSOUTPUT] AS S

DROP TABLE [#AllTables], [#SHOWSTATISTICSOUTPUT]

But for general purpose, I like Glenn Berry's scripts to find the statistics which are not updated

-- Glenn Berry -- http://glennberrysqlperformance.spaces.live.com/

  • When were Statistics last updated on all indexes? SELECT o.name, i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], s.auto_created, s.no_recompute, s.user_created FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id] INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id WHERE o.[type] = 'U' ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

  • Find indexes with no_recompute turned on SELECT o.name, i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], s.auto_created, s.no_recompute, s.user_created FROM sys.objects AS o WITH (NOLOCK) INNER JOIN sys.indexes AS i WITH (NOLOCK) ON o.[object_id] = i.[object_id] INNER JOIN sys.stats AS s WITH (NOLOCK) ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_id WHERE o.[type] = 'U' AND no_recompute = 1 ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;

more ▼

answered Mar 12, 2012 at 05:42 AM

avatar image

Usman Butt
13.9k 6 12 21

Thanks for the reply, but i suspect this script will output, when was the last time table statistics were updated.

Question is to find out, whether the table needs STATISTICS update or not?

Mar 12, 2012 at 06:19 AM shashantpundir

Please see my edited response.

Mar 12, 2012 at 07:35 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x46

asked: Mar 12, 2012 at 05:24 AM

Seen: 3356 times

Last Updated: Mar 12, 2012 at 07:35 AM

Copyright 2016 Redgate Software. Privacy Policy