October 6, 2008 at 6:14 am
As part of my nightly database maintenance, I update the database stats. In the past, I'd been using a full scan. However, this is taking longer than my maintenance window. How do you determine the proper "sampling" size for updating statistics?
October 6, 2008 at 2:56 pm
Read this from BOL and this should explain the theory behind the selection. I do agree it would be nice to have in plain english 'if your database is x amount of rows than minimum 50%..'
From BOL....
SAMPLE number { PERCENT | ROWS }
Specifies the percentage of the table or indexed view, or the number of rows to sample when collecting statistics for larger tables or views. number must be an integer, whether it is PERCENT or ROWS. To use the default sampling behavior for larger tables or views, use SAMPLE number with PERCENT or ROWS. The SQL Server 2005 Database Engine makes sure that a minimum number of values are sampled to guarantee useful statistics. If the PERCENT, ROWS, or number option causes too few rows to be sampled, the Database Engine automatically corrects the sampling based on the number of existing rows in the table or view. At least 1,000 data pages, approximately, are sampled. If the PERCENT, ROWS, or number option creates more values than are needed for a useful sample, the Database Engine tries to match the requested sample amount. However, because samples are taken by scanning all data pages, the actual sample size may not be exactly the same as the amount specified. When 0 PERCENT or ROWS is specified, the result is an empty statistics set.
Note:
The default behavior is to perform a sample scan on the target table or indexed view. The Database Engine automatically computes the required sample size.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply