SQL Server Performance

Capturing full table scans

Discussion in 'T-SQL Performance Tuning for Developers' started by holyterror_1, Feb 27, 2004.

  1. holyterror_1 New Member

    Hi,

    How do i capture queries that use full table scans? i tried using Profiler but it doesn't really show any of the queries. is there any other way/software that can be of use?

    Raymond
  2. satya Moderator

    Profiler will show if any scans are performed.

    From one of the tips about PERFMON counters, it lists :
    To find out how many table scans your server is performing, use the SQL Server Access Methods Object: Full Scans/sec. Note that this counter is for an entire server, not just a single database. One thing you will notice with this counter is that there often appears to a pattern of scans occurring periodically. In many cases, these are table scans SQL Server is performing on a regular basis for internal use.
    What you want to look for are the random table scans that represent your application. If you see what you consider to be an inordinate number of table scans, then break out the Profiler and Index Tuning Wizard to help you determine exactly what is causing them, and if adding any indexes can help reduce the table scans. Of course, SQL may just be doing its job well, and performing table scans instead of using indexes because it is just plain more efficient. [6.5, 7.0, 2000]



    HTH


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. holyterror_1 New Member

    I did try using the Profiler, selected 'Scans' in the Events tab... when i run the trace, the only thing i see under Event Class is 'Scan: Started', and 'Scan: Stopped'. In the Text Data column, there's nothing... how do i get the query that is causing sql server to perform a table scan?
  4. satya Moderator

    Make a note of ObjectID & IndexID for that trace from data columns.
    By monitoring the Index ID default data column, you can determine the identification number of the index being used by a specific query.

    May also use PERFMON if only one database is involved for the fullscan.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. holyterror_1 New Member

    Thanks a bunch for the info!

    Raymond

Share This Page