SQL Server Performance

Should I drop statistics on tables with zero records?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by DBADave, Sep 22, 2009.

  1. DBADave New Member

    I'm tuning our Update Statistics job and wonder if I should add logic to drop statistics on tables with zero records?

    Two reasons for this are:
    (1) I don't want SQL Server to use statistics that were created when a table had several records, because I'm not sure how that will impact the optimizer, especially when joining to other tables, and
    (2) I want our Update Statistics job to run as quickly as possible.
    At some point I will create a process to determine if tables are no longer being used and can be deleted, but that will be at a later date.

    Thanks, Dave
  2. Luis Martin Moderator

    How many statistics do you have in those tables?
  3. ndinakar Member

    If you have Auto Update Stats property set to TRUE your stats will be updated as your data changes (whether its updates or inserts or deletes). However, if you have that disabled, the burden is on you to manually run the UPDATE stats job as you see appropriate.
    Unless you are running high end OLTP apps with thousands of transactions per min, you should not have to worry about update stats taking longer. Its a completely online operation.
    If you have tables with 0 records, perhaps you can remove those UPDATE stats scripts (or comment them out).
  4. rohit2900 Member

    one small query.... Does updating statistics of a table with zero records consumes time & resources??
  5. moh_hassan20 New Member

    [quote user="DBADave"]if I should add logic to drop statistics on tables with zero records?[/quote]
    that table wil not be updated at all except there is at least 500+ change (update /insert /delete)
    Suppose you have a table with , e.g., 1+ million record , and truncated that table
    so number of rows will be 0
    But,
    you SHOULD update statistics manually for that table , otherwise , they mislead optimizer.
    keep statistics for tables with zero records[;)]
  6. satya Moderator

    Dave
    The cost of this automatic statistics update is minimized by sampling the data rather than analyzing all of it. Under some circumstances, statistical sampling will not be able to accurately characterize the data in a table.
    You can control the amount of data that is sampled during manual statistics updates on a table-by-table basis by using the SAMPLE and FULLSCAN clauses of the UPDATE STATISTICS statement. The FULLSCAN clause specifies that all of the data in the table is scanned to gather statistics, whereas the SAMPLE clause can be used to specify either the percentage of rows to sample or the number of rows to sample.
    I would suggest to run FULLSCAN on heavily used tables during the night times to reduce the performance lag...

Share This Page