解决sqlserver占用CPU过多的问题.docxVIP

现象 他说是这几天才出现的，而且在每天的某一个时间段才会岀现CPU占用高的情况 内存占用不太高，只占用了 30个G CPU 占用 100% 冋题：cpu-直占用过高，经常aw卡在使用100%处~ SWJQ^eryl. x . r (? 006)) SJKZ-DATA - SWJQ^eryl. x . r (? 006)) SJKZ-DATA -舌动孟襪乱 8 Yindovs任势管理3S 文件(F)选顷(0) (V)落助0) 应用程序邊程愀劳I性能1联网I用户I 应用程序 sqlservr.exe SYSTEM 23 301,640 K SQL Server Windows KT - 64 Bit 排查方向 一般排查都是用下面的脚本，一般会用到三个视图 sys.sysprocesses ,dm_exec_sessions ,dm_exec_requests USE master GO ―如果要指定数据库就把注释去掉 SELECT * FROM sys. [sysprocesses] WHERE [spid]50 --AND DB_NAME([dbid])二gposdb， SELECT COUNT(*) FROM [sys]. [dm_exec_sessions] WHERE [session id]50 看一下当前的数据库用户连接有多少 然后使用下面语句看一下各项指标是否正常,是否有阻塞,这个语句选取了前10个最耗 CPU时间的会话 SELECT TOP 10 [sessionid], [request_id], [start_time] AS 开始时间’， [status] AS 状态, [command] AS 命令, dest. [text] AS sql 语句, DB_NAME([database_id]) AS 数据库名 [blocking_session_id] AS 正在阻塞其他会话的会话ID, [wait_type] AS 等待资源类型， [wait time] AS 等待时间 [wait resource] AS 等待的资源， [reads] AS 物理读次数, [writes] AS 写次数,， [logical_reads] AS 逻辑读次数， [row count] AS 返回结果行数’ FROM sys?[dmexecjrequests] AS der CROSS APPLY sys. [dm_exec_sql_text](der. [sql_handle]) AS dest WHERE [session_id]50 AND DB_NAME(der. [database_id])二gposdb ORDER BY [cpu time] DESC 如果想看具体的SQL语句可以执行下面的SQL语句记得在SSMS里选择以文本格式显示 结果 --在SSMS里选择以文本格式显示结果 SELECT TOP 10 dest. [text] AS sql 语句 FROM sys?丨dm exec_ requests] AS der CROSS APPLY sys. [dm exec sql text] (der?[sql handle]) AS dest WHERE [session_id]50 ORDER BY [cpu_time] DESC ?跖曙忆応|占joe. GPOSDB . . . LQuery9. sql* i23USE masterGO ?跖曙忆応|占 joe. GPOSDB . . . LQuery9. sql* i 2 3 USE master GO SELECT TOP 10 I_：_ =—=—— ? ?宀 5 6 7 8 .? LQuQTyB? s*l尊厂 jo?. GPOSDB ??? LQuoryT? sql* dest. [text] AS 1 s 丄语句丨 FROM sys. [din exec/ requests] AS der CROSS APPLY sys. [din exec sql text] SELECT * FROM [dbo].[SalesOrderDetail test] SELECT * FROM [dbo]?[CT_FuelingData] SELECT TOP 10 dest. [text] AS 弓q丄语句 FROM sys?[ditL—QXQC—irequests] AS der CROSS APPLY sys. [dm_exec_sql_text.] (der. [sql_handle] ) AS 3行受影响） 模拟了一些耗CPU时间的动作 GOSELECT TOP dest?[text] FROM sys.[c GO SELECT TOP des