Can we consider removing the auto generated statistics if the update
statistic within a given database is taking too long?
Dropping all auto created statistics
In theory you could drop all your auto generated statistics. Doing so will generate temporary stress on the system as they will be recreated when queries need statistics on objects.
as long as auto create statistics is still on in the database
If you are interested on more of that and a script to remove these statistics, go to this blog by Amy Levin.
Dropping duplicate statistics
Duplicate statistics could exist.
An example of this is due to an index being added after an auto created statistic was made on the column. There should be no issue in dropping these, but as always double check.
More on that, and a way to drop these duplicate statistics by ShaunJStuart here.
The script will only drop the auto created stat that is the same as the stat created by an index
We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but
often have to stop the script as it overruns into business hours.
Update statistics impact
Remember that while
UPDATE STATISTICS uses a lot of CPU and IO depending on the size of the table+column it has a statistic on, it is not blocking.
In theory you could run it during production hours if the increase in resources used does not impact your queries.
Even though it is not blocking, one would prefer to do it outside of business hours (or has no choice due to hardware / workload limitations). This brings us to the last main point:
When is it most suitable to use RESAMPLE and FULLSCAN?
FULLSCAN <> RESAMPLE
Resample definition from microsoft docs
Update each statistic using its most recent sample rate.
Meaning that if the previous update was
RESAMPLE will also be fullscan.
FULLSCAN as the name implies scans all the rows in the table / indexed view to
update the statistic, where
RESAMPLE will reuse the previous sample rate.
They are different but can sometimes execute the same
UPDATE STATISTICS statements.
Take a look at the following three (simplified) scenario's.
An example of when updating stats with
FULLSCAN does not work
When auto update statistics is triggered hourly or even daily due to modifications, the fullscan will be gone. Another consequence of this is plan regression at random times.
If you are updating the stats on a table with
fullscan and an hour later they have already been 'auto updated', this did not have a long lasting effect.
When FULLSCAN could work
If you are able to keep the fullscan on the statistics for a specific table for close to or the entire week,
fullscan could be an option for these tables.
When doing weekly fullscan stat updates on these tables.
Consequences of doing fullscan updates
When updating with
fullscan, auto update statistics can become your enemy as degrading might happen due to different query plans at random times.
RESAMPLE is also only used in specific cases where you need to use a previously used stat update, in most real world scenario's you would not need this, unexpected things can happen when doing this.
To make your update statistics go faster you could look at doing it without
FULLSCAN or with a
What you could start out with
You could start with:
- Decide on a lower sample rate than
FULLSCAN, that will still be
bigger than the sample rate decided on by SQL Server.
An example of the lower sample rate could be a sample rate of 10 PERCENT.
UPDATE STATISTICS ... WITH SAMPLE 10 PERCENT
This means that 10% of the rows in the table / indexed view are scanned to update the statistics.
You do have to watch out that you do not take too high of a sample rate, because there is a tipping point where even
FULLSCAN is faster than a
More info on that here.
You should also look into which statistics are needed to be updated and which don't. Focus on these statistics in non-business hours.
I would not touch
RESAMPLE unless done so for an explicit reason.
Using Ola Hallengren's index and statistics maintenance could help to reduce the time this takes.
OnlyModifiedStatistics = 'Y' as to only update the modified statistics.
And change the sample rate with
StatisticsSample = 10
Ola added StatisticsModificationLevel as another way to control which
statistics get touched
An example of implementing this would be adding the parameter:
@StatisticsModificationLevel = 5 to only update the statistics when 5 Percent of the rows have changed.
Remember that this is a dynamic threshold, meaning that it can update rows faster than 5% depending on the amount of modified rows. The formula for this is:
SQRT(number of rows * 1000)
You can check how this dynamic threshold is used in a small part of ola's script:
OR (@StatisticsModificationLevel IS NOT NULL
AND @CurrentModificationCounter > 0
AND (@CurrentModificationCounter >= SQRT(@CurrentRowCount * 1000))
A small example to illustrate this (only based on indexes) could be by using this query:
SELECT rowcnt as CurrentRowCount ,
rowmodctr as CurrentModificationCounter,
SQRT(rowcnt * 1000) as sqrtRowCount
In the resultset returned below:
CurrentRowCount CurrentModificationCounter sqrtRowCount
2342 262 1530,3594349041
1118 490 1057,3551910309
16000 15000 4000
If it comes down to the dynamic threshold making the decision for these three indexes, only the statistics created by the third index would be updated.