question

shashantpundir avatar image
shashantpundir asked

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
statistics
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Usman Butt avatar image
Usman Butt answered

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;
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
Please see my edited response.
1 Like 1 ·
shashantpundir avatar image shashantpundir commented ·
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?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.