Automatic statistics updates respond to changing data and query needs. I inspect the setting and estimates before treating it as a tuning fix.
In the original project, indexes and query edits did not produce the expected improvement. I found auto-update statistics disabled, and enabling it helped that environment. That observation is a useful incident, not a promise that the setting fixes every slow query.
SELECT name, is_auto_create_stats_on, is_auto_update_stats_on,
is_auto_update_stats_async_on
FROM sys.databases
WHERE database_id = DB_ID();
-- Administrator reference, not executed as part of this inspection:
-- ALTER DATABASE [YourDatabase] SET AUTO_CREATE_STATISTICS ON;
-- ALTER DATABASE [YourDatabase] SET AUTO_UPDATE_STATISTICS ON;
-- EXEC sys.sp_updatestats;Automatic statistics creation and automatic updates are separate options. Updates depend on statistics becoming stale and being needed. Every data change doesn’t immediately update every statistics object.
Choose additional updates deliberately
The original script also ran sp_updatestats for the whole database. A broad update can add work and change plans. Review the affected statistics and maintenance policy before repeating that step.
Synchronous and asynchronous updates behave differently. I inspect the option and the workload before changing it. The useful question is how the query waits for and uses its statistics.
Reference: SQL Server statistics.
Related reading
Automatic statistics maintenance is not an instant cure, it is support for the estimates that influence query plans.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
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 :)