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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • 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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

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

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