SQL Server Performance

Find scans with profiler

Discussion in 'Performance Tuning for DBAs' started by brimba, Dec 22, 2004.

  1. brimba New Member

    Hi!

    Is there someone who have written a script to find all queries from a profiler log that is using a scan and not a seek in the index?

  2. satya Moderator

    http://www.sql-server-performance.com/sql_server_profiler_tips.asp refers
    To help identify which tables in your database may need additional or improved indexes, use the SQL Server 7.0's Profiler#%92s Create Trace Wizard to run the "Identify Scans of Large Tables" trace. This trace will tell which tables are being scanned by queries instead of using an index to seek the data. This should provide you data you can use to help you identify which tables may need more or better indexes.

    When this trace is run, it will sort events by IndexID, so you can see which column on which the scan is being performed. This way, you can more easily identify which IndexIDs are causing the most table scans.[7.0]


    And also once the trace is captured to a table then sort on Scan: Started column.

    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. brimba New Member

    How do I do this in SQL2000?

    I found the event "scan start", but when I user that it wont give me any duration/query or anything. It just gives me an indexID.
  4. satya Moderator

    http://www.sql-server-performance.com/sql_server_profiler_tips.asp

    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.

Share This Page