How to Find and Update Outdated Statistics in SQL Server
We can find when the last update statistics happened and how many row modifications happened since the last update statistics using sys.dm_db_stats_properties DMV and a few joins. We can refine this by Data Last updated and total rows and modified rows as needed.
--Set the thresholds when to consider the statistics outdated
DECLARE @hours int
DECLARE @rows INT
DECLARE @modified_rows int
DECLARE @update_statement nvarchar(300);
SET @hours=72
SET @modified_rows=1
SET @rows=1000000
SELECT Update_Query = 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N' ' + QUOTENAME(s.name)+' WITH FULLSCAN'
,Name = QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N' ' + QUOTENAME(s.name)
, ddsp.stats_id , last_updated = CONVERT(datetime2(1), ddsp.last_updated)
, ddsp.rows, mod_count = ddsp.modification_counter, ddsp.unfiltered_rows
FROM sys.schemas sch
INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
INNER JOIN sys.stats s ON o.object_id = s.object_id
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp
WHERE o.is_ms_shipped = 0
AND last_updated <=DATEADD(Hour,-@hours,GETDATE())
AND ddsp.modification_counter >= @modified_rows
--AND ddsp.rows < @rows
ORDER BY last_updated, rows DESC
Using the result of the above query you can identify if the update statistics are happening at regular intervals or if you need to schedule the update statistics from time to time.
Using the below will update all the statistics as needed from the above. ** Please note that we have refined this as depending upon the table size and volume this can cause a lot of IO and you should run this during a time that is best for you business and application needs **
Using the below will update all the statistics as needed from the above. ** Please note that we have refined this as depending upon the table size and volume this can cause a lot of IO and you should run this during a time that is best for you business and application needs **
--Set the thresholds when to consider the statistics outdated
DECLARE @hours int
DECLARE @rows INT
DECLARE @modified_rows int
DECLARE @update_statement nvarchar(300);
SET @hours=72
SET @modified_rows=1
SET @rows=1000000
--Update all the outdated statistics
DECLARE statistics_cursor CURSOR FOR
SELECT Update_Query = 'UPDATE STATISTICS ' + QUOTENAME(sch.name) + N'.' + QUOTENAME(o.name) + N' ' + QUOTENAME(s.name)+' WITH FULLSCAN'
FROM sys.schemas sch
INNER JOIN sys.objects o ON sch.schema_id = o.schema_id
INNER JOIN sys.stats s ON o.object_id = s.object_id
OUTER APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) ddsp
WHERE o.is_ms_shipped = 0
AND last_updated <=DATEADD(Hour,-@hours,GETDATE())
AND ddsp.modification_counter>=@modified_rows
AND ddsp.rows < @rows
ORDER BY last_updated, rows DESC
OPEN statistics_cursor;
FETCH NEXT FROM statistics_cursor INTO @update_statement;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXECUTE (@update_statement);
PRINT @update_statement;
FETCH NEXT FROM statistics_cursor INTO @update_statement;
END;
PRINT 'The outdated statistics have been updated.';
CLOSE statistics_cursor;
DEALLOCATE statistics_cursor;
GO
|
Using the result of the above query you can identify if the update statistics are happening at regular intervals or if you need to schedule the update statistics from time to time.
Comments
Post a Comment