SQL SERVER – Enable Automatic Statistic Update on Database

SQL SERVER - Enable Automatic Statistic Update on Database statisticssqlserver 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. Let us learn about how to Enable Automatic Statistic Update on Database.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situations 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;
/* Enable Stats and Auto Update */
-- Enable Auto Create of Statistics
-- Enable Auto Update of Statistics
-- Update Statistics for whole database
EXEC sp_updatestats

You may not believe but this feature is one of the most efficient feature for the performance tuning. I strongly suggest that you watch my video on this URL where I have demonstrated by example how this feature can help us improve the performance of the system.

I also cover this featured in depth in my SQL Server Performance Tuning Practical Workshop (3-4 Hours).

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


SQL Scripts, SQL Server, SQL Statistics
Previous Post
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 3
Next Post
SQLAuthority News – PASS 2009 Sessions on Query Optimization and Performance Tuning

Related Posts

11 Comments. Leave new

  • Hi,

    How can I check that whether Automatic Statistic Update is already enabled or not?

    Is it Enabled by-default?

  • SELECT is_auto_create_stats_on,is_auto_update_stats_on
    FROM sys.databases

  • Hi Pinal,

    how to export & import statistics from production server to development server?

    thanks in advance

  • 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?

  • Hi Pinal,
    Is it always a good idea to have the Auto update statistics turned on. Are there instances where it needs to be turned off.

  • Hi Pinal,
    when statistic updates are performed? after every insert/update.. or when server has some free resources?

  • 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.

  • Gaius Gracchus
    July 6, 2012 2:05 am

    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.

    • 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.

  • To check the autostatistics on or off try this…

    SELECT DATABASEPROPERTYEX(‘database name’,’IsAutoUpdateStatistics’)

  • 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 :)


Leave a Reply