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; 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
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)
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
WHERE name = ‘YOUR DATABASE NAME HERE’
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.
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 :)