Did you know... The stats can degrade the performance if outdated but how to check when stats gets updated

  • Article

Sometimes the query performance degrades due to the out of sync stats on the tables in the database. But how do we know it and how we do reach at the conclusion that the stats are the cause of the performance degradation. This is pretty well known but have you checked for the frequency of the updates of the stats. Read on for some info on stats in the SQL Server.

We can execute “update statistics” manually for the tables of the database with full scan. That would resolve the issue due to the out of sync stats.

 

ð When/What threshold will start update statistics if we set below on DB?

AUTO_CREATE_STATISTICS ON(True)

AUTO_UPDATE_STATISTICS_ASYNC ON(True)

When the AUTO_CREATE_STATISTICS database option is set to ON (default), the Database Engine automatically creates statistics for columns without indexes that are used in a predicate.

When the AUTO_UPDATE_STATISTICS database option is set to ON (the default), the query optimizer automatically updates this statistical information periodically as the data in the tables changes.

The database option AUTO_UPDATE_STATISTICS_ASYNC provides asynchronous statistics updating. When this option is set to ON, queries do not wait for the statistics to be updated before compiling. Instead, the out-of-date statistics are put on a queue for updating by a worker thread in a background process.

 

All these options are described in detail in the document below,

https://msdn.microsoft.com/en-us/library/ms190397.aspx

 

ð Is update statistics logged? How to see/capture when statistics was updated?

The update stats are not logged in the errorlog and you can find the details regarding the update of the stats with the below command,

DBCC SHOW_STATISTICS

This command will give you the date and time when the stats were updated the last time.

You can find more regarding the syntax and the details of this command from the below MSDN link,

https://msdn.microsoft.com/en-us/library/ms174384(SQL.90).aspx

 

Also you can use the stats_date() function to get the date when the specified stats was updated.

More details are in the below link,

https://msdn.microsoft.com/en-us/library/ms190330(SQL.90).aspx

Sanjaya Padhi
SE, Microsoft SQL Server