SQL Server Update Statistics – Quick Tips & Examples

sql statistics

An important part of obtaining sizzling database performance is ensuring statistics are up to date.

Statistics that are up to date will ensure that queries will be compiled in the most efficient manner.

A few important things to take note of:

  • Performing an index rebuild also updates the index statistics
  • Column statistics are not updated with an index rebuild
  • An index reorganize does not update any statistics
  • Updating statistics ensures that queries compile with up-to-date statistics
  • Updating statistics causes queries to recompile

Column Statistics vs Index Statistics

There are two types of statistics, column and index.

Full scan vs Sample

When you are updating statistics, you can either update them from a sample of the existing stats, or you can do a full scan.

As a rule of thumb normally a sample is enough, however if in doubt a larger sample size, or a full scan will give you the best accuracy of your statistics.

The problem with updating stats by doing a full scan, particularly on large tables, is the greater resources/time involved in the process.

How to update statistics all tables

The method we would recommend would be to use the scripts from hallengren.

If you don’t have the time for that and are looking for something you can use right now, to update statistics on all tables for a database you can use the sp_updatestats command.

----------------------------------------------------------------------
EXEC sp_updatestats
----------------------------------------------------------------------

sql server update statistics all databases

The below script will generate a query in it’s output, that you can then execute to update statistics for all databases on your SQL server.

This script was adapted from here.

----------------------------------------------------------------------
DECLARE @TSQLScript VARCHAR(2000) 
DECLARE @Databases SYSNAME 

DECLARE cUpdateStatistics CURSOR READ_ONLY FOR 
SELECT [name] 
FROM master..sysdatabases 
WHERE [name] NOT IN ('master','msdb','model', 'tempdb') 
ORDER BY [name] 

OPEN cUpdateStatistics 
FETCH NEXT FROM cUpdateStatistics INTO @Databases 
WHILE @@FETCH_STATUS = 0 
BEGIN 
SELECT @TSQLScript = 'USE [' + @Databases +']' + ';' + CHAR(13) + 'EXEC sp_updatestats' + ';' + CHAR(13) 
PRINT @TSQLScript 
FETCH NEXT FROM cUpdateStatistics INTO @Databases 
END 

CLOSE cUpdateStatistics 
DEALLOCATE cUpdateStatistics
----------------------------------------------------------------------

Does Updating Statistics Cause Blocking?

Updating statistics is an online operation, with that in mind it should not cause blocking.

How Often Should I Update Statistics

You can start off doing them once a week, or once a month. If you have performance problems you may need to increase the frequency.

Performance Impact of Updating Statistics

Depending on what method of updating statistics you use, will determine the amount of performance impact that is experienced.

For example if you do a full scan and update both column and index statistics, that will be significantly higher in terms of resources used, compared to updating stats based on a sample.

Using Maintenance Plan to Update Statistics

The built in maintenance plans are not the most efficient method of updating statistics, however due to the ease of implementation, on a smallish database this can be a reasonable solution.

On a larger database, because the defaults are a full scan and both types of stats, the process can cause considerable I/O.

Simple T-SQL Query To Show Outdated Statistics

The below query can be used to return a result set showing those objects that have not had their stats updated since xx/xx/xxxx date.

--Get the list of outdated statistics
--------------------------------------------------

SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr --INTO #Outdated_statistics
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

--------------------------------------------------

Keeping statistics up to date is a core part of troubleshooting, maintaining, and optimizing database performance.

Make sure the stats are not outdated on the SQL Servers you are responsible for!

Image credit

Rob StGeorge
Senior SQL Server Database Administrator residing in Auckland, NZ

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.