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