Track queries that caused index scan

  • Hi there

    I'm looking for a way to find the queries that are causing index scans on the SQL Server instance.I know it's easy to do so with Extended_Events in SQL server 2012 but the "sqlserver.scan_started" event does not exists in 2008 version of extended events collection. By using SQL Profiler it's possible to track indexes and tables which has been scanned but I can't easily figure out which SQL query caused the scan and have to search the plan catch to find the queries referencing the index that has been scanned.

    Is there a better way to do so?

    Pooyan

  • -- Get all SQL Statements with "table scan" in cached query plan

    ;WITH

    XMLNAMESPACES

    (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'

    ,N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)

    ,EQS AS

    (SELECT EQS.plan_handle

    ,SUM(EQS.execution_count) AS ExecutionCount

    ,SUM(EQS.total_worker_time) AS TotalWorkTime

    ,SUM(EQS.total_logical_reads) AS TotalLogicalReads

    ,SUM(EQS.total_logical_writes) AS TotalLogicalWrites

    ,SUM(EQS.total_elapsed_time) AS TotalElapsedTime

    ,MAX(EQS.last_execution_time) AS LastExecutionTime

    FROM sys.dm_exec_query_stats AS EQS

    GROUP BY EQS.plan_handle)

    SELECT EQS.[ExecutionCount]

    ,EQS.[TotalWorkTime]

    ,EQS.[TotalLogicalReads]

    ,EQS.[TotalLogicalWrites]

    ,EQS.[TotalElapsedTime]

    ,EQS.[LastExecutionTime]

    ,ECP.[objtype] AS [ObjectType]

    ,ECP.[cacheobjtype] AS [CacheObjectType]

    ,DB_NAME(EST.[dbid]) AS [DatabaseName]

    ,OBJECT_NAME(EST.[objectid], EST.[dbid]) AS [ObjectName]

    ,EST.[text] AS [Statement]

    ,EQP.[query_plan] AS [QueryPlan]

    FROM sys.dm_exec_cached_plans AS ECP

    INNER JOIN EQS

    ON ECP.plan_handle = EQS.plan_handle

    CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST

    CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP

    WHERE EQP.[query_plan].exist('data(//RelOp[@PhysicalOp="Table Scan"][@EstimateRows * @AvgRowSize > 50000.0][1])') = 1

    -- Optional filters

    AND EQS.[ExecutionCount] > 1 -- No Ad-Hoc queries

    AND ECP.[usecounts] > 1

    ORDER BY EQS.TotalElapsedTime DESC

    ,EQS.ExecutionCount DESC;

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Thanks for your reply. I was looking for a way other than searching the plan cache.

    I was using the same DMVs but your script is a little bit more complete.

    Pooyan

  • Have a look at [sys].[dm_db_index_usage_stats] - it has info about scans and might be what you are after. You will need to join to sys.objects and sys.indexes to get the names of the tables and indexes

  • pooyan_pdm (2/21/2013)


    Thanks for your reply. I was looking for a way other than searching the plan cache.

    The only way to find out what queries had specific query plan operators is to search the query plan cache.

    Also, be careful about scan started and those kind of events, that doesn't necessarily mean a full index scan. Could be a range scan (ie an index seek)

    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
  • It takes almost 6 min for sql server to look into the plan catch on our production. What kind of overhead does this search have? I guess it should put some latches on the memory pages containing the plans am I right?

    Pooyan

  • pooyan_pdm (2/22/2013)


    What kind of overhead does this search have?

    A lot. That XQuery is heavy on CPU on an unindexed column

    Don't do that on a production server. At most, pull the plan cache unfiltered to a table, move the table to another server, do your filtering there.

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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