Get SQL Statements with "table scan" in cached query plan with filter (inspired by Olaf Helper)

reference:
From http://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-0622af19

http://sqlfascination.com/2010/03/10/locating-table-scans-within-the-query-cache/

http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/


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.

note: this is imporved script to fix some potential performance issues:

--------------------------------------
/*
"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.
*/
-- Get all SQL Statements with "table scan" in cached query plan
;
set transaction isolation level read uncommitted
declare @MinElapsedTime int, @MinExecutionCount int, @MinScanSize int
----------------------------------------------------------------------------------
-- change the values here
-- run at least 5 times and total time elasped at least 5 seconds
--
select @MinElapsedTime=5000, @MinExecutionCount=5, @MinScanSize=5000
-----------------------------------------------------------------------------------
if object_id('tempdb..#EQS') is not null drop table #EQS

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
 into #EQS
 FROM sys.dm_exec_query_stats AS EQS
 GROUP BY EQS.plan_handle
 HAVING (SUM(EQS.total_elapsed_time)>@MinElapsedTime AND SUM(EQS.execution_count)>@MinExecutionCount)
--select * from #EQS
create unique clustered index IDX_EQS_handle on #EQS(plan_handle)

if object_id('tempdb..#ECP') is not null drop table #ECP
;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
 --top 100
 RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID
 ,RelOp.op.value(N'@NodeId', N'int') AS OperationID
 ,RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator
 ,RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator
 ,RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost
 ,RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO
 ,RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU
 ,RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows
 ,RelOp.op.value(N'@AvgRowSize ', N'float') AS AverageRowSize
 ,Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Database', N'varchar(256)') 
   ,RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Database', N'varchar(256)')
   ,RelOp.op.value(N'IndexScan[1]/Object[1]/@Database', N'varchar(256)')
   ,'Unknown') as DatabaseName
 ,Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Schema', N'varchar(256)')
   ,RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Schema', N'varchar(256)')
   ,RelOp.op.value(N'IndexScan[1]/Object[1]/@Schema', N'varchar(256)')
   ,'Unknown') as SchemaName
 ,Coalesce(RelOp.op.value(N'TableScan[1]/Object[1]/@Table', N'varchar(256)')
   ,RelOp.op.value(N'OutputList[1]/ColumnReference[1]/@Table', N'varchar(256)')
   ,RelOp.op.value(N'IndexScan[1]/Object[1]/@Table', N'varchar(256)')
   ,'Unknown') as ObjectName
 ,ECP.plan_handle
 ,ST.TEXT AS QueryText
 ,QP.query_plan AS QueryPlan
 ,ECP.cacheobjtype AS CacheObjectType
 ,ECP.objtype AS ObjectType
 ,QP.dbid
 ,QP.objectid
 ,ECP.usecounts
 into #ECP
 FROM sys.dm_exec_cached_plans ECP
   CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) ST
   CROSS APPLY sys.dm_exec_query_plan(ECP.plan_handle) QP
   CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
   INNER JOIN #EQS EQS
   ON ECP.plan_handle = EQS.plan_handle
 -- can be filtered later , but ...
 -- used multiple times
 WHERE ECP.usecounts>1
   AND RelOp.op.value(N'@PhysicalOp', N'varchar(50)')  IN ('Clustered Index Scan', 'Table Scan' ,'Index Scan')
   AND (RelOp.op.value(N'@AvgRowSize ', N'float')*RelOp.op.value(N'@EstimateRows', N'float'))>@MinScanSize

--create unique clustered index IDX_ECP_handle on #ECP(plan_handle)
create clustered index IDX_ECP_handle on #ECP(plan_handle)
--select plan_handle, count(*) from #ECP group by plan_handle  order by 2 desc--plan_handle

SELECT
 --top 1
 -- query plan related object
 DB_NAME(ECP.[dbid]) AS [DatabaseName]
 ,OBJECT_NAME(ECP.[objectid], ECP.[dbid]) AS [ObjectName]
 -- scanned objects
 -- from query plan scan part
 ,ECP.SchemaName as ScannedSchemaName
 ,ECP.ObjectName as ScannedObjectName
 ,ECP.DatabaseName as ScannedDatabaseName
 ,ECP.CacheObjectType
 ,EQS.[TotalElapsedTime]/6000.00 as [TotalElapsedTime(min)]
 ,EQS.[ExecutionCount]
 ,EQS.[TotalWorkTime]/6000.00 as [TotalWorkTime(min)]
 ,ECP.EstimatedRows*AverageRowSize/8096.00 AS [EstimatedScanSize(Page)]
 ,EstimatedCost
 ,EstimatedIO
 ,ECP.EstimatedRows
 ,ECP.AverageRowSize
 ,EQS.[TotalLogicalReads]
 ,EQS.[TotalLogicalWrites]
 ,EQS.[LastExecutionTime]
 ,ECP.QueryText     
 ,ECP.QueryPlan
 into #FullResult
 FROM #ECP AS ECP
   INNER JOIN #EQS EQS
   ON ECP.plan_handle = EQS.plan_handle     

select * from #FullResult ORDER BY 1,2,3,4,5,6,7 desc
if object_id('tempdb..#EQS') is not null drop table #EQS
if object_id('tempdb..#ECP') is not null drop table #ECP
if object_id('tempdb..#FullResult') is not null drop table #FullResult

--------------------------------------


-- 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;

List all heap tables of a given database

-- List all heap tables 
-- Try this with tempdb and see how many temp tables or table variables have been created there, you will be amazed with large olap databases.  
SELECT s.name + '.' + t.name AS TableName

FROM sys.tables t INNER JOIN sys.schemas s

ON t.schema_id = s.schema_id INNER JOIN sys.indexes i

ON t.object_id = i.object_id

AND i.type = 0 -- = Heap

ORDER BY TableName

Database external fragmentation overview

use MyDatabase




go


select top 50

avg_fragmentation_in_percent*s.page_count/100.00 fragmented_page_count

,o.name as TableName

,i.name as IndexName

,s.partition_number

,s.avg_fragmentation_in_percent

,s.page_count

,s.index_depth

,s.index_level

,s.index_type_desc

,d.name as DataBaseName

,s.*

from sys.dm_db_index_physical_stats(db_id(), null, null, null, 'limited') s

--sys.dm_db_index_physical_stats(db_id(), object_id('MyTableInCurrentDB'), null, null, 'limited') s

inner join sys.databases d on d.database_id=s.database_id

inner join sys.objects o on o.object_id=s.object_id

left join sys.indexes i on i.object_id=s.object_id and i.index_id=s.index_id

where -- have large external fragmentation

s.avg_fragmentation_in_percent>10

-- ignore small table/index

and s.page_count>1

-- ignore heap table

and s.index_id>0

order by avg_fragmentation_in_percent*s.page_count desc

Checking SQL server stats

-- use dm_db_stats_properties to get stats info
-- for SQL 2012 and above
SELECT
 OBJECT_NAME([sp].[object_id]) AS "Table"
 ,[sp].[stats_id] AS "Statistic ID"
 ,[s].[name] AS "Statistic"
 ,[sp].[rows_sampled]
 ,[sp].[modification_counter] AS "Modifications"
 ,[sp].[last_updated] AS "Last Updated"
 ,[sp].[rows]
 ,[sp].[unfiltered_rows]
 FROM [sys].[stats] AS [s]
   OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]
 --WHERE [s].[object_id] = OBJECT_ID(N'Sales.TestSalesOrderDetail');
 ORDER BY 1,2

--
-- quick way to check which stats objects are used by query

DBCC FREEPROCCACHE

SELECT 

    p.Name,

    total_quantity = SUM(th.Quantity)

FROM AdventureWorks.Production.Product AS p

JOIN AdventureWorks.Production.TransactionHistory AS th ON

    th.ProductID = p.ProductID

WHERE

    th.ActualCost >= $5.00

    AND p.Color = N'Red'

GROUP BY

    p.Name

ORDER BY

    p.Name

OPTION

(

    QUERYTRACEON 3604,

    QUERYTRACEON 9292,

    QUERYTRACEON 9204

)

--
-- checking auto created stats which are never updated or updated before 1 week
DECLARE @NumOfDays int, @StateCheckDateTimeDeadLine datetime
SET @NumOfDays=7
SET @StateCheckDateTimeDeadLine=DATEADD(hour, -24*@NumOfDays, getdate())
--SELECT @StateCheckDateTimeDeadLine

SELECT object_name(object_id) as TableName
,name AS StatisticsName
,STATS_DATE(object_id, stats_id) AS StatisticsUpdatDate
,auto_created AS IsAutoCreated
FROM sys.stats
WHERE auto_created=1
-- never updated or updated before dead line
AND (STATS_DATE(object_id, stats_id) IS NULL
OR STATS_DATE(object_id, stats_id)<=@StateCheckDateTimeDeadLine)
order by 1,2

-- after this check detail of each stat
DBCC SHOW_STATISTICS ( table_or_indexed_view_name , target )
[ WITH [ NO_INFOMSGS ] < option > [ , n ] ]
< option > :: =
    STAT_HEADER | DENSITY_VECTOR | HISTOGRAM | STATS_STREAM

DBCC SHOW_STATISTICS ("MyTable", MyPrimayKey);
DBCC SHOW_STATISTICS ("MyTable", MyStatsName);

reference:
1)
http://sqlblog.com/blogs/paul_white/archive/2011/09/21/how-to-find-the-statistics-used-to-compile-an-execution-plan.aspx
2)
http://www.sqlskills.com/blogs/erin/understanding-when-statistics-will-automatically-update/

Sizing database/partition/partitioned table with [dm_db_partition_stats]

--
-- whole database size
SELECT   
        (SUM(reserved_page_count)*8192)/1024000 AS [TotalSpace(MB)]
        ,(SUM([used_page_count])*8192)/1024000 AS [UsedSpace(MB)]
        ,(SUM([row_count])) AS [RowCount]
        FROM sys.dm_db_partition_stats

--
-- size of each partition scheme       
select    s.name as [partition_scheme]
        ,sum(ps.[reserved_page_count])*8192/10240000  AS [TotalSpace(MB)]
        ,sum(ps.[used_page_count])*8192/10240000 AS [UsedSpace(MB)]
        ,sum([row_count]) AS [RowCount]
    from    sys.indexes i inner join sys.partition_schemes s
            on i.data_space_id = s.data_space_id 
            inner join
                (
                SELECT   
                    [object_id]
                    ,[index_id]
                    ,sum([used_page_count]) as [used_page_count]
                    ,sum([reserved_page_count]) as [reserved_page_count]
                    ,sum([row_count]) as [row_count]
                    FROM [sys].[dm_db_partition_stats]
                    group by [object_id],[index_id]
                ) ps
            on ps.object_id=i.object_id and ps.index_id=i.index_id
    group by s.name
    order by 1,2

--
-- size of partitioned table
select
        object_schema_name(i.object_id) as [schema]
        ,object_name(i.object_id) as [object]
        ,i.name as [index]
        ,ps.[used_page_count]
        ,ps.[reserved_page_count]
        ,ps.[row_count]
        ,s.name as [partition_scheme]
        ,f.name as [patition_function]
    from    sys.indexes i inner join sys.partition_schemes s
            on i.data_space_id = s.data_space_id 
            inner join sys.partition_functions f
            on f.function_id = s.function_id
            inner join
                (
                SELECT   
                    [object_id]
                    ,[index_id]
                    ,sum([used_page_count]) as [used_page_count]
                    ,sum([reserved_page_count]) as [reserved_page_count]
                    ,sum([row_count]) as [row_count]
                    FROM [sys].[dm_db_partition_stats]
                    group by [object_id],[index_id]
                ) ps
            on ps.object_id=i.object_id and ps.index_id=i.index_id
    order by 1,2,3