注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

水滴石穿

破浪是阵风!

 
 
 

日志

 
 

Sql Server CPU 性能排查及优化的相关  

2012-01-09 13:38:15|  分类: 默认分类 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

--Begin Cpu 分析优化的相关 Sql

--使用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

-- 计算'Cxpacket'占整wait时间的百分比
--
Cxpacket:Sql Server 在处理一句代价很大的语句,要不就是没有合适的索引或筛选条件没能筛选足够的记录,使得语句要返回大量的结果,当 >5% 说明有问题
declare @Cxpacket bigint
declare @Sumwaits bigint
select @Cxpacket = wait_time_ms
from Sys.dm_os_wait_stats
where wait_type = 'Cxpacket'
select @Sumwaits = sum(wait_time_ms)
from Sys.dm_os_wait_stats
select convert(numeric(5,4),@Cxpacket/@Sumwaits)

-- 查询当前数据库上所有用户表格在Row lock上发生阻塞的频率
declare @dbid int
select @dbid = db_id()
Select dbid=database_id, objectname=object_name(s.object_id)
, indexname
=i.name, i.index_id    --, partition_number
, row_lock_count, row_lock_wait_count
,
[block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2))
, row_lock_wait_in_ms
,
[avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))
from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,     sys.indexes i
where objectproperty(s.object_id,'IsUserTable') = 1
and i.object_id = s.object_id
and i.index_id = s.index_id
order by row_lock_wait_count desc

--End Cpu 分析优化的相关 Sql

  评论这张
 
阅读(218)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018