没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
1、sys.dm_exec_query_stats
SELECT s2.dbid,
(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 sql_statement,
execution_count, plan_generation_num, last_execution_time, 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
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 s1.total_worker_time desc
使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。例如下面的语句就可以列出前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
go
返回最经常运行的100条语句
SELECT TOP 100 cp.cacheobjtype,cp.usecounts,cp.size_in_bytes,
qs.statement_start_offset,qs.statement_end_offset,qt.dbid
,qt.objectid
,SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
SELECT s2.dbid,
(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 sql_statement,
execution_count, plan_generation_num, last_execution_time, 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
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 s1.total_worker_time desc
使用DMV来分析SQL Server启动以来累计使用CPU资源最多的语句。例如下面的语句就可以列出前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
go
返回最经常运行的100条语句
SELECT TOP 100 cp.cacheobjtype,cp.usecounts,cp.size_in_bytes,
qs.statement_start_offset,qs.statement_end_offset,qt.dbid
,qt.objectid
,SUBSTRING(qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as statement FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle and cp.usecounts>4
ORDER BY [dbid],[Usecounts] DESC
返回做IO数目最多的50条语句以及它们的执行计划
select top 50 (total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
statement_end_offset as stmt_end_offset,
substring(sql_text.text, (statement_start_offset/2),
case when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,
sql_text.text,plan_text.*
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
order by (total_logical_reads + total_logical_writes) /Execution_count Desc
-- 计算signal wait占整wait时间的百分比
-- 指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ,说明 CPU 紧张
select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms))
from Sys.dm_os_wait_stats
else qs.statement_end_offset end -qs.statement_start_offset)/2)
as statement FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
inner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle
where cp.plan_handle=qs.plan_handle and cp.usecounts>4
ORDER BY [dbid],[Usecounts] DESC
返回做IO数目最多的50条语句以及它们的执行计划
select top 50 (total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
statement_end_offset as stmt_end_offset,
substring(sql_text.text, (statement_start_offset/2),
case when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,
sql_text.text,plan_text.*
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
order by (total_logical_reads + total_logical_writes) /Execution_count Desc
-- 计算signal wait占整wait时间的百分比
-- 指令等待 CPU 资源的时间占总时间的百分比。如果超过 25% ,说明 CPU 紧张
select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms))
from Sys.dm_os_wait_stats
剩余11页未读,继续阅读
peterzeng20210530
- 粉丝: 178
- 资源: 16
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功
- 1
- 2
- 3
前往页