1.查询最占资源的前50个脚本
select
c.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time,c.last_elapsed_time,
q.[text]
from
(select top 50 qs.*
from sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as c
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by c.total_worker_time desc
2.查找目前SQL Server所执行的SQL语法,并展示资源情况:
SELECT s2.dbid ,
DB_NAME(s2.dbid) AS [数据库名] ,
--s1.sql_handle ,
( SELECT TOP 1
SUBSTRING(s2.text, statement_start_offset / 2 + 1,
( ( CASE WHEN statement_end_offset = -1
THEN ( LEN(CONVERT(NVARCHAR(MAX), s2.text))
* 2 )
ELSE statement_end_offset
END ) - statement_start_offset ) / 2 + 1)
) AS [语句] ,
execution_count AS [执行次数] ,
last_execution_time AS [上次开始执行计划的时间] ,
total_worker_time AS [自编译以来执行所用的 CPU 时间总量(微秒)] ,
last_worker_time AS [上次执行计划所用的 CPU 时间(微秒)] ,
min_worker_time AS [单次执行期间曾占用的最小 CPU 时间(微秒)] ,
max_worker_time AS [单次执行期间曾占用的最大 CPU 时间(微秒)] ,
total_logical_reads AS [总逻辑读] ,
last_logical_reads AS [上次逻辑读] ,
min_logical_reads AS [最少逻辑读] ,
max_logical_reads AS [最大逻辑读] ,
total_logical_writes AS [总逻辑写] ,
last_logical_writes AS [上次逻辑写] ,
min_logical_writes AS [最小逻辑写] ,
max_logical_writes AS [最大逻辑写]
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid IS NULL
ORDER BY last_worker_time DESC
3.当前进程及其语句:
SELECT PRO.loginame AS LoginName
,DB.name AS DatabaseName
,PRO.[status] as ProcessStatus
,PRO.cmd AS Command
,PRO.last_batch AS LastBatch
,PRO.cpu AS Cpu
,PRO.physical_io AS PhysicalIo
,SES.row_count AS [RowCount]
,STM.[text] AS SQLStatement
FROM sys.sysprocesses AS PRO
INNER JOIN sys.databases AS DB
ON PRO.dbid = DB.database_id
INNER JOIN sys.dm_exec_sessions AS SES
ON PRO.spid = SES.session_id
CROSS APPLY sys.dm_exec_sql_text(PRO.sql_handle) AS STM
WHERE PRO.spid >= 50 -- Exclude system processes
ORDER BY PRO.physical_io DESC
,PRO.cpu DESC;
4. 开销较大的查询
SELECT ss.SUM_execution_count ,
t.text ,
ss.SUM_total_elapsed_time ,
ss.sum_total_worker_time ,
ss.sum_total_logical_reads ,
ss.sum_total_logical_writes
FROM ( SELECT S.plan_handle ,
SUM(s.execution_count) SUM_Execution_count ,
SUM(s.total_elapsed_time) SUM_total_elapsed_time ,
SUM(s.total_worker_time) SUM_total_worker_time ,
SUM(s.total_logical_reads) SUM_total_logical_reads ,
SUM(s.total_logical_writes) SUM_total_logical_writes
FROM sys.dm_exec_query_stats s
GROUP BY S.plan_handle
) AS ss
CROSS APPLY sys.dm_exec_sql_text(ss.plan_handle) t
ORDER BY sum_total_logical_reads DESC
5.显示IO占用情况
SET STATISTICS IO ON
SET STATISTICS TIME ON
with dbio as
(
select
DB_NAME(t.database_id) databasename,
case mf.type when 1 then 'log' else 'data' end file_type,
SUM(t.num_of_bytes_read+t.num_of_bytes_written) as io,
SUM(t.io_stall) io_stall
from
sys.dm_io_virtual_file_stats(null,null) as t
join sys.master_files mf on t.database_id=mf.database_id and t.file_id=mf.file_id
group by DB_NAME(t.database_id),mf.type
)
select
t.databasename,
t.file_type,--文件类型:data:数据,log:日志
CAST(1.0*t.io/(1024*1024) as decimal(12,2)) io_mb,--IO操作大小(包含读写,单位:MB)
CAST(t.io_stall/1000 as decimal(12,2)) as io_stall_s,--IO等待时间(单位:S)
CAST(100.0 * t.io_stall/SUM(t.io_stall) over() as decimal(12,2)) io_stall_pict,--IO等待占总等待时间的百分比
ROW_NUMBER() over(order by io_stall desc) rn --行号
from
dbio t
order by t.io_stall desc