By: Tibor Nagy | Updated: 2012-08-03 | Comments (7) | Indexing
In the previous tip we learned that outdated statistics can cause SQL Server performance problems. My question is: How can I find out which statistics are outdated in my SQL Server database?
We can use the STATS_DATE (object_id, stats_id) function to check when the last update happened for the given statistics. The input is the ID of the table and the ID of the statistics. This function returns the last update date in datetime format. In the next section I will show how to collect data about the outdated statistics into a table.
First, we will create a table for the results:
--Create a table for the outdated statistics CREATE TABLE Outdated_statistics ([Table name] sysname, [Index name] sysname, [Last updated] datetime NULL, [Rows modified] int NULL) GO
Now we will query every statistics object which was not updated in the last day and has rows modified since the last update. We will use the rowmodctr field of sys.sysindexes because it shows how many rows were inserted, updated or deleted since the last update occurred. Please note that it is not always 100% accurate in SQL Server 2005 and later, but it can be used to check if any rows were modified.
--Get the list of outdated statistics INSERT INTO Outdated_statistics SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr FROM sys.sysindexes WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE()) AND rowmodctr>0 AND id IN (SELECT object_id FROM sys.tables) GO
After collecting this information, we can decide which statistics require an update.
If we are sure that all out dated statistics should be immediately updated, then we can write a cursor to update them:
--Set the thresholds when to consider the statistics outdated DECLARE @hours int DECLARE @modified_rows int DECLARE @update_statement nvarchar(300); SET @hours=24 SET @modified_rows=10 --Update all the outdated statistics DECLARE statistics_cursor CURSOR FOR SELECT 'UPDATE STATISTICS '+OBJECT_NAME(id)+' '+name FROM sys.sysindexes WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,[email protected],GETDATE()) AND rowmodctr>[email protected]_rows AND id IN (SELECT object_id FROM sys.tables) 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
As you can see, you can define the thresholds as to when to consider the statistics outdated. You can run this as an ad-hoc query or you can create a stored procedure and execute it with a scheduled job as needed. Either way, after you execute this query, the statistics should be up to date.
- Check out these related tips to learn more about Statistics and Indexing:
- Performance Tuning Tips category articles
- Indexing Tips category articles
- A strange situation when Automatic Statistics Update Slows Down SQL Server 2005
- Read more tips by the author here.
Last Updated: 2012-08-03
About the author
View all my tips