Description:

"Table scan" (and also "Index scan") can cause poor performance, especially when they are performed on large tables.
To identify queries causing such scans you can use the SQL Profiler with the events "Scans" => "Scan:Started" and "Scan.Stopped".
Other option is to analyse the cached query plans.
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.
Note: The xml data of the cached query plans is not indexed in the DMV, therefore the query can run up to several minutes.

Works with SQL Server 2005 and higher versions in all editions.
Requires VIEW SERVER STATE permissions.

SQL
Skript bearbeiten|Remove
-- 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_countAS ExecutionCount 
           ,SUM(EQS.total_worker_timeAS TotalWorkTime 
           ,SUM(EQS.total_logical_readsAS TotalLogicalReads 
           ,SUM(EQS.total_logical_writesAS TotalLogicalWrites 
           ,SUM(EQS.total_elapsed_timeAS TotalElapsedTime 
           ,MAX(EQS.last_execution_timeAS 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.[objtypeAS [ObjectType] 
      ,ECP.[cacheobjtypeAS [CacheObjectType] 
      ,DB_NAME(EST.[dbid]) AS [DatabaseName] 
      ,OBJECT_NAME(EST.[objectid], EST.[dbid]) AS [ObjectName] 
      ,EST.[textAS [Statement]       
      ,EQP.[query_planAS [QueryPlanFROM 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;