SQL Server 2005 Statistics Primer

If you have a large, active OLTP database, it is extremely important that you keep your statistics up to date. Statistics are critical to the SQL Server query optimizer. Missing or outdated statistics can allow the query optimizer to choose very inefficient query plans, which can quickly bring your database server to its knees.

Unless you have a very good reason not to do so, it is generally a good idea to allow SQL Server 2005 to automatically create statistics and to automatically update statistics (like you see in the screenshot below). These are the default options when you create a new database.

 

Beyond that, here are a few useful commands and queries that will help you keep your statistics in good shape.

— List Statistics names and keys for a single table (will be deprecated in future version of SQL Server)
sp_HelpStats ‘CurrentPost’

— Get index name, description and keys for all indexes in a single table
sp_HelpIndex ‘UserAccount’

— Show Statistics with all information
DBCC SHOW_STATISTICS (‘CurrentPost’, ‘PK_CurrentPost’)  — TableName, IndexName

— Show Statistics options (these are subsets of not specifying an option)
DBCC SHOW_STATISTICS (‘CurrentPost’, ‘PK_CurrentPost’) WITH STAT_HEADER;
DBCC SHOW_STATISTICS (‘CurrentPost’, ‘PK_CurrentPost’) WITH DENSITY_VECTOR;
DBCC SHOW_STATISTICS (‘CurrentPost’, ‘PK_CurrentPost’) WITH HISTOGRAM;

— Get the last time Statistics were updated for all the indexes in a single table
SELECT ‘Index Name’ = i.name, ‘Statistics Date’ = STATS_DATE(i.object_id, i.index_id)
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.name = ‘UserAccount’
AND o.object_id = i.object_id
ORDER BY STATS_DATE(i.object_id, i.index_id);

— Get the last time Statistics were updated for all indexes in multiple tables
SELECT ‘Index Name’ = i.name, ‘Statistics Date’ = STATS_DATE(i.object_id, i.index_id)
FROM sys.objects AS o
JOIN sys.indexes AS i
ON o.name IN (‘ArchivePost’, ‘CurrentPost’, ‘FeedInfo’, ‘SubscriptionInfo’, ‘SubscriptionRead’, ‘UserAccount’ )
AND o.object_id = i.object_id
ORDER BY STATS_DATE(i.object_id, i.index_id);

— Update all statistics for a single table using default 10% sample
UPDATE STATISTICS UserAccount;

— Update statistics for a single index in a single table using default 10% sample
UPDATE STATISTICS UserAccount IX_UserAccount_Cover1;

— Update all statistics for a single table using 50% sample
UPDATE STATISTICS UserAccount WITH SAMPLE 50 PERCENT;

— Update statistics for all user tables in the current database
— that need to be updated based on the rowmodctr info in sys.sysindexes
EXEC sp_UpdateStats

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s