Auto Update Statistics and Update Statistics in SQL Server

Auto Update Statistics and Update Statistics in SQL Server offer two different approaches to maintaining statistics in SQL Server databases.

Today users reported a severe slow down on a report normally taking 10 minutes , now  taking 7 hrs.

I Checked the  execution plan  prior and after . Parallelism decreased . And a Clustered Index Scan , where it was a Seek previously

 Certain statistics hadn’t been generated on certain columns which forced the cardinality estimate of the query to be incorrect.

Even though Auto Update Statistics is turned on for the database , the decision to trigger the auto update statistics is based on :

1)  the number of data modifications since the last statistics update , compared against the threshold 2) )the threshold is based on the number of rows.

 Auto Update Statistics is on by checking :

SELECT DATABASEPROPERTY('MYDB','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics

To turn on AUTO_UPDATE_STATISTICS  use :

ALTER DATABASE MYDB SET AUTO_UPDATE_STATISTICS ON

  This indicates Auto Update Statistics is on , but depending on the size of the table the sample rate can be low.  

 There is a maintenance job running every Sunday rebuilding Indexes if they are over a certain fragmentation level , with a side effect of doing a 100% sample of the data. But if the index is under the threshold , a defragmentation  process runs on the index.

 This means that when sizeable (strategic ) amounts of data are loaded , a robust method must be implemented to force an update of the statistics – specifically the right statistics.

It’s important to note , Auto statistcs update is performed  by a table or index sample. If an explicit sample is required use CREATE or UPDATE statistcs.

 Examples of a robust method of updating statistcs are:

 --For UPDATE STATISTICS on all tables

EXEC sp_updatestats

 --For a table level use UPDATE STATISTICS

UPDATE STATISTICS myTable

 There are differences in the way these two methods are executed . I’ll expand in a different post.

 Consider SQL Server Update Statistics with FULL SCAN   as a thorough approach

Read More

SQL Server Query Optimizer and Statistics - SQL Server DBA

SQL Server - Delete all statistics from a table - SQL Server DBA

SQL Server - Index missing Statistics - SQL Server DBA

SQL Server statistics and Datawarehouse - SQL Server DBA