Starting with SQL Server 2005, the execution plans are stored as XML. Combining this fact with powerful XQueries the result is endless (well, almost) possibilities. For example, somebody could write a simple SELECT statement to find out all execution plans including [ [ Clustered] Index | Table ] Scan operators.
Step #1 Run this script to create a stored procedure:
Code Snippet
USE AdventureWorks2008R2;
GO
CREATE PROCEDURE Sales.spTest
AS
SELECT soh.SalesOrderID
FROM Sales.SalesOrderHeader soh
WHERE soh.ShipDate>='20070101';
GO
GO
CREATE PROCEDURE Sales.spTest
AS
SELECT soh.SalesOrderID
FROM Sales.SalesOrderHeader soh
WHERE soh.ShipDate>='20070101';
GO
Step #2 Execute the newly created stored procedure (EXEC Sales.spTest) using Actual Execution Plan option activated (see here how). The result will be the following execution plan:
Step #3: Now, run the following SELECT statement to find out all execution plans which include an [ [ Clustered] Index | Table ] Scan operator:
Code Snippet
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WITH XMLNAMESPACES
(
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT TOP(10)
crt_db.[dbid],
DB_NAME(crt_db.[dbid]) AS database_name,
pl.objectid,
QUOTENAME(OBJECT_SCHEMA_NAME(pl.objectid, crt_db.[dbid]))+'.'+QUOTENAME(OBJECT_NAME(pl.objectid, crt_db.[dbid])) AS object_name,
pl.query_plan,
src.text AS source_code,
pl.query_plan.exist('//RelOp[@PhysicalOp="Clustered Index Scan"]') AS [Has Clustered Index Scan],
pl.query_plan.exist('//RelOp[@PhysicalOp="Index Scan"]') AS [Has Index Scan],
pl.query_plan.exist('//RelOp[@PhysicalOp="Table Scan"]') AS [Has Table Scan],
cp.size_in_bytes,
cp.usecounts
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) pl
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) src
CROSS APPLY (
SELECT CONVERT(INT, plat.value) AS [dbid]
FROM sys.dm_exec_plan_attributes(cp.plan_handle) plat
WHERE plat.attribute = 'dbid'
) crt_db
WHERE pl.query_plan.exist('//RelOp[
@PhysicalOp = "Clustered Index Scan"
or @PhysicalOp = "Index Scan"
or @PhysicalOp = "Table Scan"
]')=1
ORDER BY cp.usecounts DESC;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
WITH XMLNAMESPACES
(
DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT TOP(10)
crt_db.[dbid],
DB_NAME(crt_db.[dbid]) AS database_name,
pl.objectid,
QUOTENAME(OBJECT_SCHEMA_NAME(pl.objectid, crt_db.[dbid]))+'.'+QUOTENAME(OBJECT_NAME(pl.objectid, crt_db.[dbid])) AS object_name,
pl.query_plan,
src.text AS source_code,
pl.query_plan.exist('//RelOp[@PhysicalOp="Clustered Index Scan"]') AS [Has Clustered Index Scan],
pl.query_plan.exist('//RelOp[@PhysicalOp="Index Scan"]') AS [Has Index Scan],
pl.query_plan.exist('//RelOp[@PhysicalOp="Table Scan"]') AS [Has Table Scan],
cp.size_in_bytes,
cp.usecounts
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) pl
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) src
CROSS APPLY (
SELECT CONVERT(INT, plat.value) AS [dbid]
FROM sys.dm_exec_plan_attributes(cp.plan_handle) plat
WHERE plat.attribute = 'dbid'
) crt_db
WHERE pl.query_plan.exist('//RelOp[
@PhysicalOp = "Clustered Index Scan"
or @PhysicalOp = "Index Scan"
or @PhysicalOp = "Table Scan"
]')=1
ORDER BY cp.usecounts DESC;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Results:
Revision History
2013-10-07 Update #1 (Changes: step 2 and 3)
Leave a Reply