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 '12 at 05:24 AM in Default

shashantpundir gravatar 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 '12 at 05:42 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

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 '12 at 06:19 AM shashantpundir
Please see my edited response.
Mar 12 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x33

asked: Mar 12 '12 at 05:24 AM

Seen: 2053 times

Last Updated: Mar 12 '12 at 07:35 AM