--前10名其他等待類(lèi)型
SELECT TOP 10 * from sys.dm_os_wait_stats ORDER BY wait_time_ms DESC
SELECT *FROM sys.dm_os_wait_stats WHERE wait_type like 'PAGELATCH%'
OR wait_type like 'LAZYWRITER_SLEEP%'
--CPU的壓力
SELECT scheduler_id, current_tasks_count, runnable_tasks_count
FROM sys.dm_os_schedulers WHERE scheduler_id < 255
--表現(xiàn)最差的前10名使用查詢(xún)
SELECT TOP 10 ProcedureName = t.text, ExecutionCount = s.execution_count, AvgExecutionTime = isnull ( s.total_elapsed_time / s.execution_count, 0 ),AvgWorkerTime = s.total_worker_time / s.execution_count,TotalWorkerTime = s.total_worker_time,MaxLogicalReads = s.max_logical_reads,MaxPhysicalReads = s.max_physical_reads,
MaxLogicalWrites = s.max_logical_writes,CreationDateTime = s.creation_time,CallsPerSecond = isnull ( s.execution_count / datediff ( second , s.creation_time, getdate ()), 0 ) FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t ORDER BY
s.max_physical_reads DESC
SELECT SUM(signal_wait_time_ms) AS total_signal_wait_time_ms總信號(hào)等待時(shí)間 ,SUM(wait_time_ms - signal_wait_time_ms) AS source_wait_time_ms資源的等待時(shí)間,SUM(signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [signal_wait_percent信號(hào)等待％],SUM(wait_time_ms - signal_wait_time_ms) * 1.0 / SUM (wait_time_ms) * 100 AS [resource_wait_percent資源等待％] FROM sys.dm_os_wait_stats
--一個(gè)信號(hào)等待時(shí)間過(guò)多對(duì)資源的等待時(shí)間那么你的CPU是目前的一個(gè)瓶頸。
--查看進(jìn)程所執(zhí)行的SQL語(yǔ)句
if (select COUNT(*) from master.dbo.sysprocesses) > 500
begin
select text,CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) from master.sys.sysprocesses a
end
select text,a.* from master.sys.sysprocesses a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle) where a.spid = '51'
dbcc inputbuffer(53)
with tb
as
(
select blocking_session_id,session_id,db_name(database_id) as dbname,text from master.sys.dm_exec_requests a CROSS APPLY master.sys.dm_exec_sql_text(a.sql_handle)
),
tb1 as
(
select a.*,login_time,program_name,client_interface_name,login_name,cpu_time,memory_usage*8 as 'memory_usage(KB)',total_scheduled_time,reads,writes,logical_reads
from tb a inner join master.sys.dm_exec_sessions b
on a.session_id=b.session_id
)
select a.*,connect_time,client_tcp_port,client_net_address from tb1 a inner join master.sys.dm_exec_connections b on a.session_id=b.session_id
--當(dāng)前進(jìn)程數(shù)
select * from master.dbo.sysprocesses order by cpu desc
--查看當(dāng)前活動(dòng)的進(jìn)程數(shù)
sp_who active
--查詢(xún)是否由于連接沒(méi)有釋放引起CPU過(guò)高
select * from master.dbo.sysprocesses where spid> 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -10, getdate()) and login_time < dateadd(minute, -10, getdate())
--強(qiáng)行釋放空連接
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())
--查看當(dāng)前占用 cpu 資源最高的會(huì)話和其中執(zhí)行的語(yǔ)句(及時(shí)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
--查看緩存中重用次數(shù)少壁涎，占用內(nèi)存大的查詢(xún)語(yǔ)句（當(dāng)前緩存中未釋放的）--全局
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 --統(tǒng)計(jì)總的CPU時(shí)間
--ORDER BY avg_cpu_time DESC --統(tǒng)計(jì)平均單次查詢(xún)CPU時(shí)間
-- 計(jì)算可運(yùn)行狀態(tài)下的工作進(jìn)程數(shù)量
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
--表空間大小查詢(xún)
create table #tb(表名 sysname,記錄數(shù) int,保留空間 varchar(100),使用空間 varchar(100),索引使用空間 varchar(100),未用空間 varchar(100))
insert into #tb exec sp_MSForEachTable 'EXEC sp_spaceused ''?'''
select * from #tb
go
SELECT 表名,記錄數(shù),cast(ltrim(rtrim(replace(保留空間,'KB',''))) as int)/1024 保留空間MB,cast(ltrim(rtrim(replace(使用空間,'KB',''))) as int)/1024 使用空間MB,cast(ltrim(rtrim(replace(使用空間,'KB',''))) as int)/1024/1024.00 使用空間GB,cast(ltrim(rtrim(replace(索引使用空間,'KB',''))) as int)/1024 索引使用空間MB,cast(ltrim(rtrim(replace(未用空間,'KB',''))) as int)/1024 未用空間MB
FROM #tb WHERE cast(ltrim(rtrim(replace(使用空間,'KB',''))) as int)/1024 > 0
--order by 記錄數(shù) desc
ORDER BY 使用空間MB DESC
DROP TABLE #tb
--查詢(xún)是否由于連接沒(méi)有釋放引起CPU過(guò)高
select * from master.dbo.sysprocesses where spid> 50 and waittype = 0x0000 and waittime = 0 and status = 'sleeping ' and last_batch < dateadd(minute, -10, getdate()) and login_time < dateadd(minute, -10, getdate())
--查找目前緩存批處理或者存儲(chǔ)過(guò)程中使用做多CPU資源的查詢(xún)
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
--強(qiáng)行釋放空連接
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())
----查看當(dāng)前占用 cpu 資源最高的會(huì)話和其中執(zhí)行的語(yǔ)句(及時(shí)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
----查看緩存中重用次數(shù)少封锉，占用內(nèi)存大的查詢(xún)語(yǔ)句（當(dāng)前緩存中未釋放的）--全局
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 --統(tǒng)計(jì)總的CPU時(shí)間
--ORDER BY avg_cpu_time DESC --統(tǒng)計(jì)平均單次查詢(xún)CPU時(shí)間
-- 計(jì)算可運(yùn)行狀態(tài)下的工作進(jìn)程數(shù)量
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'語(yǔ)句編譯時(shí)間',last_execution_time N'上次執(zhí)行時(shí)間'
,total_physical_reads N'物理讀取總次數(shù)',total_logical_reads/execution_count N'每次邏輯讀次數(shù)',total_logical_reads N'邏輯讀取總次數(shù)',total_logical_writes N'邏輯寫(xiě)入總次數(shù)' , execution_count N'執(zhí)行次數(shù)', total_worker_time/1000 N'所用的CPU總時(shí)間ms', total_elapsed_time/1000 N'總花費(fèi)時(shí)間ms', (total_elapsed_time / execution_count)/1000 N'平均時(shí)間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'執(zhí)行語(yǔ)句' 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 '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
