购买流程 付款方式 常见问题 在线提问  续租服务  购物车
首页 域名注册 虚拟主机 成品网站 企业邮局 服务器租用 VPS主机 云主机 智能建站 代理专区 客户中心
用户登陆
为何选择我们
  • 7年优质服务经验
  • 10万用户的共同选择
  • 更低的价格更好的服务
  • CNNIC四星级认证服务商
  • 拥有正规经营许可证(ICP)
  • 先进的解析技术10分钟生效
  • 6组DNS负载均衡更快更稳定
  虚拟主机域名注册-常见问题数据库问题 → 数据库问题


[转帖]SQLSERVER排查CPU占用高的情况
作者:

今天中午,有朋友叫我帮他看一下数据库,操作系统是Windows2008R2 ,数据库是SQL2008R2 64位

64G内存,16核CPU

硬件配置还是比较高的,他说服务器运行的是金蝶K3软件,数据库实例里有多个数据库

他说是这几天才出现的,而且在每天的某一个时间段才会出现CPU占用高的情况

内存占用也很高,占用了30个G

-----------------------------------------------华丽的分割线-------------------------------------------------------

一般排查都是用下面的脚本,一般会用到三个视图sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests

1USE master2GO3--如果要指定数据库就把注释去掉4SELECT*FROM sys.[sysprocesses]WHERE[spid]>50--AND DB_NAME([dbid])='gposdb'5SELECTCOUNT(*) FROM[sys].[dm_exec_sessions]WHERE[session_id]>50

看一下当前的数据库用户连接有多少

然后使用下面语句看一下各项指标是否正常,是否有阻塞,这个语句选取了前10个最耗CPU时间的会话

复制代码
 1SELECTTOP10 2[session_id], 3[request_id], 4[start_time]AS'开始时间', 5[status]AS'状态', 6[command]AS'命令', 7 dest.[text]AS'sql语句' 8DB_NAME([database_id]) AS'数据库名', 9[blocking_session_id]AS'正在阻塞其他会话的会话ID',10[wait_type]AS'等待资源类型',11[wait_time]AS'等待时间',12[wait_resource]AS'等待的资源',13[reads]AS'物理读次数',14[writes]AS'写次数',15[logical_reads]AS'逻辑读次数',16[row_count]AS'返回结果行数'17FROM sys.[dm_exec_requests]AS der 18CROSS APPLY 19 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 20WHERE[session_id]>50ANDDB_NAME(der.[database_id])='gposdb'21ORDERBY[cpu_time]DESC
复制代码


如果想看具体的SQL语句可以执行下面的SQL语句,记得在SSMS里选择以文本格式显示结果

复制代码
1--在SSMS里选择以文本格式显示结果2SELECTTOP103 dest.[text]AS'sql语句'4FROM sys.[dm_exec_requests]AS der 5CROSS APPLY 6 sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 7WHERE[session_id]>508ORDERBY[cpu_time]DESC
复制代码

模拟了一些耗CPU时间的动作

-----------------------------------------华丽的分割线-----------------------------------------------------------

还有查看CPU数和user scheduler数和最大工作线程数,检查worker是否用完也可以排查CPU占用情况

1--查看CPU数和user scheduler数目2SELECT cpu_count,scheduler_count FROM sys.dm_os_sys_info3--查看最大工作线程数4SELECT max_workers_count FROM sys.dm_os_sys_info

查看机器上的所有schedulers包括user 和system
通过下面语句可以看到worker是否用完,当达到最大线程数的时候就要检查blocking了


对照下面这个表
各种CPU和SQLSERVER版本组合自动配置的最大工作线程数
CPU数                 32位计算机                        64位计算机
<=4                     256                                   512
  8                        288                                   576
 16                       352                                   704
 32                       480                                   960

复制代码
1SELECT2scheduler_address,3scheduler_id,4cpu_id,5status,6current_tasks_count,7current_workers_count,active_workers_count8FROM sys.dm_os_schedulers
复制代码

 如果大家有什么需要补充的,或者文章有不正确的,欢迎大家拍砖!!

----------------------------------------------------------------------------------------------

 2013-6-15 做了一下补充,如果SQLSERVER存在要等待的资源,那么执行下面语句就会显示出会话中有多少个worker在等待

结合[sys].[dm_os_wait_stats]视图,如果当前SQLSERVER里面没有任何等待资源,那么下面的SQL语句不会显示任何结果

复制代码
 1SELECTTOP10 2[session_id], 3[request_id], 4[start_time]AS'开始时间', 5[status]AS'状态', 6[command]AS'命令', 7  dest.[text]AS'sql语句' 8DB_NAME([database_id]) AS'数据库名', 9[blocking_session_id]AS'正在阻塞其他会话的会话ID',10  der.[wait_type]AS'等待资源类型',11[wait_time]AS'等待时间',12[wait_resource]AS'等待的资源',13[dows].[waiting_tasks_count]AS'当前正在进行等待的任务数',14[reads]AS'物理读次数',15[writes]AS'写次数',16[logical_reads]AS'逻辑读次数',17[row_count]AS'返回结果行数'18FROM sys.[dm_exec_requests]AS der 19INNERJOIN[sys].[dm_os_wait_stats]AS dows 20ON der.[wait_type]=[dows].[wait_type]21CROSS APPLY 22  sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 23WHERE[session_id]>5024ORDERBY[cpu_time]DESC
复制代码

 比如我当前执行了查询SalesOrderDetail_test表100次,由于表数据非常多,所以SSMS需要把SQLSERVER执行的结果慢慢的取走,

造成了ASYNC_NETWORK_IO等待

1USE[AdventureWorks]2GO3SELECT*FROM dbo.[SalesOrderDetail_test]4GO100

------------------------------------------------------------------------------------------------

经过排查和这几天的观察情况,确定是某些表缺失索引导致,现在在这些表上增加了索引,问题解决了

复制代码
1select*from t_AccessControl        --权限控制表权限控制2select*from t_GroupAccess            --用户组权限表用户组权限3select*from t_GroupAccessType        --用户组权限类表用户组权限类4select*from t_ObjectAccess        --对象权限表对象权限5select*from t_ObjectAccessType    --对象权限类型表对象权限类型6select*from t_ObjectType            --对象类型表对象类型
复制代码

查询CPU占用高的语句

复制代码
 1SELECTTOP10 2    total_worker_time/execution_count AS avg_cpu_cost, plan_handle, 3   execution_count, 4    (SELECTSUBSTRING(text, statement_start_offset/2+1, 5       (CASEWHEN statement_end_offset =-1 6THENLEN(CONVERT(nvarchar(max), text)) *2 7ELSE statement_end_offset 8END- statement_start_offset)/2) 9FROM sys.dm_exec_sql_text(sql_handle)) AS query_text10FROM sys.dm_exec_query_stats11ORDERBY[avg_cpu_cost]DESC
复制代码

查询缺失索引

复制代码
1SELECT2     DatabaseName =DB_NAME(database_id)3     ,[Number Indexes Missing]=count(*4FROM sys.dm_db_missing_index_details5GROUPBYDB_NAME(database_id)6ORDERBY2DESC;
复制代码
复制代码
 1SELECTTOP10 2[Total Cost]=ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0 3        , avg_user_impact 4         , TableName = statement 5         , [EqualityUsage]= equality_columns  6         , [InequalityUsage]= inequality_columns 7         , [Include Cloumns]= included_columns 8FROM        sys.dm_db_missing_index_groups g  9INNERJOIN    sys.dm_db_missing_index_group_stats s 10ON s.group_handle = g.index_group_handle 11INNERJOIN    sys.dm_db_missing_index_details d 12ON d.index_handle = g.index_handle13ORDERBY[Total Cost]DESC;
复制代码

定位问题后,新建非聚集索引

复制代码
1CREATENONCLUSTEREDINDEX IX_t_AccessControl_F4 ON dbo.t_AccessControl2(3    FObjectType4 )include([FUserID], [FAccessType], [FAccessMask]) WITH( STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON) ON[PRIMARY]5GO67dropindex IX_t_AccessControl_F4 on t_AccessControl
复制代码

 CPU占用恢复正常

跟踪模板和跟踪文件下载,请使用SQL2008R2 版本:files.cnblogs.com/lyhabc/跟踪模板和trace.rar




来源:
阅读:1276
日期:2013-11-15

【 双击滚屏 】 【 推荐朋友 】 【 收藏 】 【 打印 】 【 关闭 】 【 字体: 】 
上一篇:[转帖]Linux下mysql主从配置
下一篇:PHP-redis中文文档
  >> 相关文章
 
返回首页 |关于我们 | 联系我们 | 付款方式 | 广告联盟 | 有问必答
版权所有 西部数码代理,西数代理,vps_空间优惠,网站性能优化·严禁复制
在线客服: 点击发送消息给对方3528938
服务热线:18382390188 传真:028-86264041
信息反馈:业务咨询 技术问题 问题投诉 《中华人民共和国增值电信业务经营许可证》川B2-20030065号
友情链接:美美哒网