SQL Server 查看缓存中使用索引扫描、表扫描、键查找、隐式转换的SQL语句

表扫描和标检查询是最消耗性能的,还好就是SqlServer自动维护执行计划将其保存在内存中.而且动态视图sys.dm_db_index_usage_stats也记录相关信息,这样我们就可以再次分析执行计划,从而找出进行优化

--总体查看哪个数据库扫描查询次数最多
select db_name(database_id),max(user_scans) bigger,avg(user_scans) average
from sys.dm_db_index_usage_stats
group by db_name(database_id)
order by average DESC

USE ReportServer

--使用扫描查询的索引有哪些
select object_name(c.object_id) as [table],c.name  as [index],user_scans,user_seeks
,case a.index_id when 1 then 'CLUSTERED' else 'NONCLUSTERED' end as type
from sys.dm_db_index_usage_stats a
inner join sys.indexes c
on c.object_id=a.object_id and c.index_id=a.index_id
where database_id=DB_ID('ReportServer') 
order by user_scans desc

--指定索引使用的扫描查询的计划和脚本（运行可能很久！需中断操作）
SELECT TOP 10 qp.query_plan,qt.text 
from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//qplan:RelOp/qplan:IndexScan/qplan:Object[@Index="[IX_Event_TimeEntered]"]')=1

--查看语句中有使用扫描查询的执行计划有哪些（按消耗时间的排序）
SELECT TOP 10 qp.query_plan,qt.text,total_worker_time 
from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //qplan:RelOp[@LogicalOp="Index Scan"
            or @LogicalOp="Clustered Index Scan"
            or @LogicalOp="Table Scan"]')=1
order by total_worker_time DESC

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

--使用标检查找(Lookup)最多的数据库
select db_name(database_id),max(user_lookups) bigger,avg(user_lookups) average
from sys.dm_db_index_usage_stats
group by db_name(database_id)
order by average desc

USE ReportServer

--使用标检查找(Lookup)的索引有哪些
select object_name(c.object_id) as [table],c.name as [index],user_lookups
, case a.index_id when 1 then 'CLUSTERED' else 'NONCLUSTERED' end as type
from sys.dm_db_index_usage_stats a
inner join sys.indexes c
on  c.object_id=a.object_id and c.index_id=a.index_id
where database_id=DB_ID('ReportServer')
order by user_lookups desc

----指定索引使用的标检查找(Lookup)的计划和脚本（运行将很久！需中断操作）
select qp.query_plan,qt.text, plan_handle,query_plan_hash 
from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
where qp.query_plan.exist('declare namespace 
AWMI="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
//AWMI:IndexScan[@Lookup]/AWMI:Object[@Index="[PK_Event]"]')=1

以下查询来自桦仔大侠的博文《SQL Server中提前找到隐式转换提升性能的办法》

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT stmt.value('(@StatementText)[1]', 'varchar(max)') AS SQL_Text ,
         t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName ,
         t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName ,
         t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName ,
         ic.DATA_TYPE AS ConvertFrom ,
         ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength ,
         t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo ,
         t.value('(@Length)[1]', 'int') AS ConvertToLength ,
         query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch ( stmt )
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n ( t )
JOIN INFORMATION_SCHEMA.COLUMNS AS ic ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
	AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]','varchar(128)')
	AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]','varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1

原文参考：Exploring Query Plans in SQL