SQL Server Performance

Please solve my doubt

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by bond_isamashi, May 28, 2009.

  1. bond_isamashi New Member

    1.We have large production database consisting of 50 tables and almost 200000 records.
    we have the script that executes twice in month to update the statistics.The script is
    written in the vbs and is using sqlmaint for updadating the statistics with sampling 10%.
    The database is having large number of insert and updates.
    The sampling rate used in the script is enough to sample all the records?
    I come to know the following fact from the microsoft technet that
    "If the PERCENT, ROWS, or number option results in too few rowsbeing sampled, SQL
    Server automatically corrects the sampling based onthe number of existing rows in the
    table or view."
    is it true?
    How can i tackle this problem?
    what is optimum sample rate for the 20000 records?
    Your help is appreciated
    With regards
    bond_isamashi
  2. madhuottapalam New Member

    In general table with 200000 rows may not considered as a larger table in sql server. You dont need to give the sample here. However, the Books online says ....
    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.
    Check this for more info http://msdn.microsoft.com/en-us/library/ms187348.aspx
    Madhu
  3. moh_hassan20 New Member

    if table size is less than 1000 page (1000*8k =8000k =~8 MB) , is considered small table , so don't care for that tables for statistics.
    if table is large, and it has auto incremented field, and that fields is pk or indexed , like int with auto increment, auto increment datetime , MS recomend run statistics with full scan, because sampling is not accurate.
  4. satya Moderator

    How often the DBCC REINDEX is executed on this database?
    Do you have AUTO STATISTICS enabled on the database?

Share This Page