SQL Server Performance

How to Identify Table Scans

Discussion in 'SQL Server 2005 General DBA Questions' started by jbates99, Aug 24, 2010.

  1. jbates99 Member

    Hello everyone,
    In 2005, has anyone found a good way of finding the names of Tables that full scans are occurring on? And maybe even the SQL code that is running when the table scan occurs?

    Thanks, John
    jbates99, Aug 24, 2010
    #2

  2. Luis Martin Moderator

    You can try with scan events in Profiler. Check BOL for details.
    Luis Martin, Aug 24, 2010
    #3

  3. satya Moderator

    As Luis referred PROFILER can get information, but running that in PROD environment may prove costly if you have performance issues.
    So you can take help of SYSMON (PERFMON) tool get performance counter under the "SQL Server:Access Methods" category called "Full Scans/sec" for more information.
    satya, Aug 24, 2010
    #4

  4. bseefelt New Member

    Check the user_scans column in sys.dm_db_index_usage_stats
    bseefelt, Aug 25, 2010
    #5

Share This Page