数据库优化--找出SQL瓶颈的SQL语句

打开一些查询性能的参数：
SET STATISTICS time ON
SET STATISTICS IO ON
GO
--执行一些SQL语句 消息TAB中有相应的参数


2、查出瓶颈的SQL语句
--查找Query Plan的常规统计信息
--查找Query Plan的CPU相关统计
--总计耗费CPU时间最长的查询计划

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*--
declare @ORDERBY varchar(30)
select @ORDERBY='cpu'
--*/
--查询获得的列
--creation_time        last_execution_time        execution_count      total_worker_time  last_worker_time    min_worker_time         max_worker_time   total_physical_reads       last_physical_reads         min_physical_reads         max_physical_reads         total_logical_writes         last_logical_writes  min_logical_writes  max_logical_writes total_logical_reads last_logical_reads         min_logical_reads   max_logical_reads  total_clr_time last_clr_time   min_clr_time   max_clr_time  total_elapsed_time         last_elapsed_time  min_elapsed_time  max_elapsed_time dbname   ObjectName    statement_text       statement_text2         refcounts usecounts         cacheobjtype   objtype

Select top 100
qs.creation_time, qs.last_execution_time, qs.execution_count
, qs.total_worker_time, qs.last_worker_time, qs.min_worker_time, qs.max_worker_time
, qs.total_physical_reads, qs.last_physical_reads, qs.min_physical_reads, qs.max_physical_reads
, qs.total_logical_writes, qs.last_logical_writes, qs.min_logical_writes, qs.max_logical_writes
, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads
, qs.total_clr_time, qs.last_clr_time, qs.min_clr_time, qs.max_clr_time
, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time

,DB_NAME(st.dbid) AS dbname
,OBJECT_SCHEMA_NAME(st.objectid, st.dbid)+'.'+OBJECT_NAME(st.objectid, st.dbid) AS ObjectName
  
,SUBSTRING(st.text, isnull(qs.statement_start_offset/2,0) + 1,
    ((CASE qs.statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text

,SUBSTRING(st.text, isnull(qs.statement_start_offset/2,0) + 1,
    ((CASE WHEN qs.statement_end_offset in (0,-1) or qs.statement_end_offset is null THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END
            - CASE WHEN qs.statement_start_offset=0 or qs.statement_start_offset is null THEN 2 ELSE qs.statement_start_offset END)/2) + 1) AS statement_text2
,cp.refcounts,cp.usecounts,cp.cacheobjtype,cp.objtype
FROM sys.dm_exec_query_stats qs
INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
--CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
--CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--where qs.execution_count>=10 and DB_NAME(st.dbid)='ucar'
--qs.creation_time>=dateadd(mi,-1,getdate()) --1分钟前
--and qs.last_execution_time<=dateadd(mi,-1,getdate())
order by
--总计耗费CPU时间最长的查询计划(哪个查询累计使用CPU时间最多，也可以查询sys.dm_exec_cached_plans并通过使用过滤器查找可疑的类似于‘%Hash Match%', ‘%Sort%'这样CPU敏感的各种操作)
qs.total_worker_time desc,st.dbid

--单次执行耗费CPU时间最长的查询计划
--qs.max_worker_time DESC,st.dbid

--执行次数最多的查询计划
--qs.execution_count DESC

--平均耗费CPU时间最长的查询计划
--qs.total_worker_time*1.0/qs.execution_count desc,st.dbid

--平均I/O最大的
--(qs.total_logical_reads+qs.total_logical_writes)*1.0/qs.execution_count DESC,st.dbid

/*--
CASE WHEN @ORDERBY = 'CPU' THEN qs.total_worker_time
WHEN @ORDERBY = 'EC' THEN qs.execution_count
END DESC
--*/


--得到SQLServer当前运行的语句(目前正在运行的sql语句，当前正在运行)
Select [Spid] = session_Id
, [Database] = DB_NAME(sp.dbid)
--, [User] = nt_username
, [User] = sp.loginame
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (st.text,er.statement_start_offset/2,

(CASE WHEN er.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2

ELSE er.statement_end_offset END -

er.statement_start_offset)/2)

,[Parent Query] = st.text

, Program = program_name

, Hostname

, nt_domain

, start_time

FROM sys.dm_exec_requests er

INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) as st

Where session_Id > 50 -- Ignore system spids.

AND session_Id NOT IN (@@SPID) -- Ignore this current statement.

orDER BY 1, 2