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
)
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?
Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority
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’)