sql server high cpu 排查
refer:
https://www.cnblogs.com/lyhabc/archive/2013/06/12/3133273.html (step by step)
https://www.360kuai.com/mob/transcoding?url=9d1f5d2101d40ebc7&cota=4&kuai_so=1&sign=360_e39369d1
https://stackoverflow.com/questions/1289558/how-much-ram-is-sql-server-actually-using
https://stackoverflow.com/questions/941763/list-the-queries-running-on-sql-server
通常 CPU high 和缺 ram 有密切关系.
start > administrative tools > performance > open resource monitor > 这里看到 ram 和 cpu 的 detail
OS ram 多, 不代表 sql 可以用得多. 要去 server > properties > memory 看 max ram 是多少.
解决方法是定时 clear ram. 比如 定时 restart server (当然指定 clear SQL Server ram 会更理想, 但如果嫌麻烦, restart start 比较容易实现)
常用语句
0. 查 cpu, connection
<=50 都是 sa background job (最好也看一下)
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
1. 查 connections
SELECT DB_NAME(DBID) AS DataBaseName ,COUNT(DBID) AS NumberOfConnections ,LogiName FROM sys.sysprocesses WHERE DBID > 0 GROUP BY DBID, LogiName
2. sum connect count
select SUM(NumberOfConnections) from ( SELECT DB_NAME(DBID) AS DataBaseName ,COUNT(DBID) AS NumberOfConnections ,LogiName FROM sys.sysprocesses WHERE DBID > 0 GROUP BY DBID, LogiName ) a
3. 查 ram usage
SELECT (total_physical_memory_kb/1024) AS Total_OS_Memory_MB, (available_physical_memory_kb/1024) AS Available_OS_Memory_MB, ROUND(100 - CAST(((available_physical_memory_kb/1024) * 100.0/ (total_physical_memory_kb/1024)) AS FLOAT),2) AS Usage_Percentage FROM sys.dm_os_sys_memory; SELECT (physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB, (locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB, (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, process_physical_memory_low, process_virtual_memory_low FROM sys.dm_os_process_memory;