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)
pinaldave
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?