SQL SERVER – Enable Automatic Statistic Update on Database

In one of the recent projects, I found out that despite putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situation like this, please do the following:

1) Enable Auto Creation of Statistics

2) Enable Auto Update of Statistics

3) Update Statistics for whole database

USE AdventureWorks;
GO
/* Enable Stats and Auto Update */
-- Enable Auto Create of Statistics
ALTER DATABASE AdventureWorks
SET AUTO_CREATE_STATISTICS ON;
-- Enable Auto Update of Statistics
ALTER DATABASE AdventureWorks
SET AUTO_UPDATE_STATISTICS ON;
GO
-- Update Statistics for whole database
EXEC sp_updatestats
GO

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

13 thoughts on “SQL SERVER – Enable Automatic Statistic Update on Database

  1. Hello Pinal,

    Is there a possibility of high resources utilization during the execution of the above processes? If yes, is it only executed during the schedule maintenance period?

    Like

  2. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

  3. Hi Pinal,

    I really impressive to your information but I want to ask one thing that how can to check Automatic Statistic Update is already enabled or not… Please suggest to us about that.

    Like

  4. The automatic statistics update uses a sampling rate that is way too small, and you cannot override this sampling rate. On a large table the sampling rate is often well less than 1%. This in turn causes the query optimizer to make bad decisions – and you only need a few such bad decisions to cripple database performance. I’ve spoken to Microsoft about this and they refuse to acknowledge there’s a problem; so the only solution is to run statistics manually with FULLSCAN,NORECOMPUTE on a regular basis. We’ve found every 2 weeks is fine.

    Like

    • We have also found it necessary to run statistics after inserting rows which have data which was not previously in the table index. For example, if you copy a large number of rows from one year to the next. The optimizer apparently has no clue that those values exist. Updating the statistics can have an incredible effect on speed.

      Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

  6. Hi Pinal, I have created a DB in SQL and import excel file into it, now I want my SQL DB should be updated automatically whenever I add/update data in excel file.
    Thanks in Advance :)

    Like

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