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

This Transact-SQL statements filters the cached query plans for existing table scan operators and returns the statement and query statistics. An additional filter is set on the attribute "EstimateRows * @AvgRowSize" = "Estimate size" to filter out scans on small tables.

 
 
 
 
 
4.1 Star
(15)
5,747 times
Add to favorites
Databases
8/23/2011
E-mail Twitter del.icio.us Digg Facebook
  • Query doesn't finish
    2 Posts | Last post November 11, 2013
    • I'm with craig here, I had it running for over an hour w/o it returning results.
    • This query not run.
      
      Script with bug.
  • Query doesn't finish
    4 Posts | Last post April 30, 2013
    • Reporting the same issue as mkal55 and craig1974; the query never completes.  What size DB was this tested against?
      
      I even added (NOLOCK) hints to the query and it did not improve performance.
    • Hi, 
      
      Here is a work around for this problem - not the cleanest but works: (insert the data into a temp table, index the temp table then query it).
      
      CREATE TABLE #PlanCacheAnalysis (
      id BIGINT IDENTITY(1,1) PRIMARY KEY
      ,[ExecutionCount] BIGINT NOT NULL
      ,[TotalWorkTime] BIGINT NOT NULL
      ,[TotalLogicalReads] BIGINT NOT NULL
      ,[TotalLogicalWrites] BIGINT NOT NULL
      ,[TotalElapsedTime] BIGINT NOT NULL
      ,[LastExecutionTime] DATETIME NULL
      ,[ObjectType] varchar(15) NULL
      ,[CacheObjectType] varchar(15) NULL
      ,[DatabaseName] varchar(25) NULL
      ,[ObjectName] varchar(500) NULL
      ,[Statement]  varchar(MAX) null    
      ,[QueryPlan] XML)
      
      
      -- 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) 
      INSERT INTO #PlanCacheAnalysis  
      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 
           CRO
      
      
    • hmm - didn't realise there was a limit! here is the rest :)
      
      CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
      WHERE EQS.[ExecutionCount] > 1  -- No Ad-Hoc queries
            AND ECP.[usecounts] > 1
      ORDER BY EQS.TotalElapsedTime DESC
              ,EQS.ExecutionCount DESC;
      
      
      CREATE PRIMARY XML INDEX XMLIndex ON #PlanCacheAnalysis([QueryPlan])
      
      SELECT * FROM #PlanCacheAnalysis
      WHERE queryplan.exist('declare namespace NS="http://schemas.microsoft.com/sqlserver/2004/07/showplan";data(//NS:RelOp[@PhysicalOp="Clustered Index Scan"][1])') = 1
            AND [ExecutionCount] > 1  -- No Ad-Hoc queries
      ORDER BY TotalElapsedTime DESC
              ,ExecutionCount DESC;
              
              
      -- DROP TABLE #PlanCacheAnalysis
    • The main problem here is, that the XML data isn't indexed. So if you have a lot of cached query plans, then the query on it will take several times.
      
      The solution of ChrisLound to copy the query plans into a user defined table + index the XML data is pretty good.
  • Query takes forever?
    1 Posts | Last post September 13, 2012
    • I tried to run this query on two databases. One large one (150 GB) and one small one (3 GB) and on both it was still running after 30 minutes so I cancelled them. Is that normal?