SQL Server Performance

Interpretting The PerfMon Full Scans counter results

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by jbates99, Oct 2, 2008.

  1. jbates99 Member

    Hi experts,
    I ran a PerfMon trace today for a few hours on my SQL Server 2005 database on Windows 2003 Server. Attempting to identify table scans, I chose the Full Scans counter. Don't remember which category it is under.

    The average for the period is 35

    Can anyone tell me if 35 is high, low or maybe this counter is useless.

    Please advise. Thanks, John
  2. moh_hassan20 New Member

    Full Scans counter is the number of tables or index scans that the sql server optimizer do in executing queries.
    It is for the entire server, not just a single database.The less number , the best performance.

    There is no absolute good number indicator,
    You may have small tables that do not need indexes, which lead to acceptable table scans number.
    This conter is very important to be monitored, and try to make a reference base number.
    Any increase show less performance, and vise versa.
    You need to review your execution plans for queries and SP to find where scan table occur, and tune it by creating indexes.

  3. Saurabh Srivastava New Member

    If you haven't disabled the default trace in SQL Server 2005 than open those traces and call perfmon file in profiler to look for queries which are causing scans and find out the tables being used. Those traces are small rollover files. You may find something if not whole lot of stuff. I thnk no harm in just trying.
  4. Saurabh Srivastava New Member

    Forgot to mention that for FULL scan values above 10 is not good but some applications have lots of small temp tables which are scanned and increase that number. SYSTEM tables are also scanned sometimes so its all depend on your application behaviour and access pattern.
  5. madhuottapalam New Member

    I agree with Hassan. This counter value may not give you a clear indication. you may run profiler /trace and give the workload to DTA. DTA is a wonderful tool which will help you in this scenario.
    Madhu

Share This Page