Post reply

Identify query leading to scans

  • January 11, 2012 at 6:40 am

    #248833

    Hi

    I would like to obtain something like this :

    - First column : SP or ad-hoc query

    - Second column : Table or index Scan due to the query

    I am currently working on a database where a lot of implicit conversions leads to table or index scan (WHERE Varchar_field = int_value , awesome).

    I would like to find a way to list all poor queries without having to analyze all stored procedures and ad-hoc queries.

    I tried doing it with profiler taking into account :

    - Scan:stopped

    - all kinds of StmtCompleted

    events but I can't find any efficient way to filter and keep all information I need.

    Thanks

  • January 11, 2012 at 7:30 am

    #1431777

    Scans are only one indicator of poor performance and are actually not even always a good indicator. SQL Server will run faster using a scan instead of a seek on a table that only consists of a few pages. If you really want to use scans as the mechanism for identifying poorly performing queries, there's no direct way to identify a scan and associate it with a query that I know of except one. You'd have to use an XQuery against the plans in cache. That'll do it.

    I blogged about how to do that here[/url]. This query will actually get you want you want if you filter for physical operations that are table or clustered index scans.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 11, 2012 at 7:53 am

    #1431801

    Perhaps a better option would be to query the plan cache (same method as Grant suggests) for plans that have CONVERT_IMPLICIT in them

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 11, 2012 at 8:08 am

    #1431812

    Querying the plan cache will do the trick ! Good idea !

    Thanks a lot Grant and Gail.

  • January 11, 2012 at 8:19 am

    #1431826

    azdzn (1/11/2012)

    Querying the plan cache will do the trick ! Good idea !

    Thanks a lot Grant and Gail.

    Sure thing. Just remember, as I say in the article, direct queries against the cache are somewhat expensive. Be cautious doing this on a production system.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply