11

From MSDN:

When none of the sample options (SAMPLE, FULLSCAN, RESAMPLE) are specified, the query optimiser samples the data and computes the sample size by default.

How to identify default sample size of statistics?

I went through MSDN but didn't find any formula or method to identify the default sample size. Everywhere there are only formulas present to trigger the auto statistics update. Any pointers will be helpful.

19

How to identify default sample size of statistics?

I would quote from Blogs.msdn article

Auto Update stats Algorithm:

So the Auto Update stats will fire for every 500 + 20% change in table rows. Of course, we have an improved algorithm in SQL 2012 which is SQRT(1000 * Table rows) which is much better.

When it fires it will use the default sampling rate and here is the algorithm how it calculates the sampling rate.

1) If the table < 8MB then it updates the statistics with a fullscan.

2) If the table > 8MB, it follows an algorithm. It reduces the sampling rate as the number of rows in the table are increased to make sure we are not scanning too much data. This is not a fixed value but is under the control of optimizer. It is not a linear algorithm either.

Example: if we have 1,000,000 rows it would use a sampling rate of 30% but when the number of rows increase to 8,000,000 it would reduce the sampling rate to 10%. These sampling rates are not under the DBAs control but optimizer decides it.

To understand further I suggest you read

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.