--强行释放空连接
select 'kill ' + rtrim(spid) from master.dbo.sysprocesses where spid> 50
and waittype = 0x0000 and waittime = 0
and status = 'sleeping '
and last_batch < dateadd(minute, -60, getdate()) and login_time < dateadd(minute, -60, getdate())
----查看当前占用 cpu 资源最高的会话和其中执行的语句(及时CPU) select spid,cmd,cpu,physical_io,memusage,
(select top 1 [text] from ::fn_get_sql(sql_handle)) sql_text from master..sysprocesses order by cpu desc,physical_io desc
----查看缓存中重用次数少，占用内存大的查询语句（当前缓存中未释放的）--全局
SELECT TOP 100 usecounts, objtype, p.size_in_bytes,[sql].[text] FROM sys.dm_exec_cached_plans p OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts,p.size_in_bytes desc
SELECT top 25
qt.text,qs.plan_generation_num,qs.execution_count,dbid,objectid FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt WHERE plan_generation_num >1 ORDER BY qs.plan_generation_num
SELECT top 50 qt.text AS SQL_text ,SUM(qs.total_worker_time) AS total_cpu_time,
SUM(qs.execution_count) AS total_execution_count,
SUM(qs.total_worker_time)/SUM(qs.execution_count) AS avg_cpu_time, COUNT(*) AS number_of_statements FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt GROUP BY qt.text
ORDER BY total_cpu_time DESC --统计总的CPU时间
--ORDER BY avg_cpu_time DESC --统计平均单次查询CPU时间
-- 计算可运行状态下的工作进程数量
SELECT COUNT(*) as workers_waiting_for_cpu,s.scheduler_id FROM sys.dm_os_workers AS o
INNER JOIN sys.dm_os_schedulers AS s
ON o.scheduler_address=s.scheduler_address AND s.scheduler_id<255 WHERE o.state='RUNNABLE' GROUP BY s.scheduler_id
SELECT creation_time N'语句编译时间'
,last_execution_time N'上次执行时间' ,total_physical_reads N'物理读取总次数'
,total_logical_reads/execution_count N'每次逻辑读次数' ,total_logical_reads N'逻辑读取总次数' ,total_logical_writes N'逻辑写入总次数' , execution_count N'执行次数'
, total_worker_time/1000 N'所用的CPU总时间ms' , total_elapsed_time/1000 N'总花费时间ms'
, (total_elapsed_time / execution_count)/1000 N'平均时间ms'
,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) N'执行语句' FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, ((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END
- qs.statement_start_offset)/2) + 1) not like 'ttch%'
ORDER BY total_elapsed_time / execution_count DESC