2

Can we consider removing the auto generated statistics if the update statistic within a given database is taking too long? We do a weekly maintenance using UPDATE STATISTICS WITH RESAMPLE but often have to stop the script as it overruns into business hours.

When is it most suitable to use RESAMPLE and FULLSCAN?

  • 1
    Do you update statistics on all tables/indexes indiscriminately? Do you have many operations (insert, delete, update) that affect those tables/indexes that might need to update the stats so frequently? – EzLo Mar 26 at 7:57
4

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

RESAMPLE

Update each statistic using its most recent sample rate.

Meaning that if the previous update was FULLSCAN, the 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

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 SAMPLE RATE


What you could start out with

You could start with:

  • Dropping the duplicate auto created statistics

  • Updating the statistics either without FULLSCAN (Plain UPDATE STATISTICS and let SQL Server decide on the sample rate)

Or

  • 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 SAMPLE RATE.

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.


Extra considerations

Using Ola Hallengren's index and statistics maintenance could help to reduce the time this takes.

Set OnlyModifiedStatistics = 'Y' as to only update the modified statistics.

And change the sample rate with StatisticsSample = 10

@ErikDarling mentioned:

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
FROM sys.sysindexes;

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.