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;
No comments:
Post a Comment