随着数据量和并发量的增大，数据库有时会遇到CPU,内存，IO 性能问题；整理了一下有关排查数据相关的SQL脚本，以便排查问题之用；
1，哪些SQL 消耗CPU
/* 查看哪些SQL语句消耗CPU，找出有问题的SQL语句进行优化，或者索引优化*/ SELECT TOP 50 total_worker_time/1000 as '总消耗CPU 时间(ms)', execution_count '运行次数', qs.total_worker_time/qs.execution_count/1000. as '平均消耗CPU 时间(ms)', SUBSTRING(qt.text,qs.statement_start_offset/2+1, (case when qs.statement_end_offset = -1 then DATALENGTH(qt.text) else qs.statement_end_offset end -qs.statement_start_offset)/2 + 1) as '使用CPU的语法', qt.text '完整语法', qt.dbid, dbname=db_name(qt.dbid), qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt order by total_worker_time desc
2, 查看SQL 阻塞进程
/***高并发情况下，容易产生进程阻塞，查看阻塞的SQL**/ SELECT t1.request_session_id AS 'wait_sid' , t1.resource_type AS '锁类型' , DB_NAME(resource_database_id) AS '库明称' , t1.request_mode AS 'wait锁类型' , t2.wait_duration_ms AS 'wait_time_ms' , ( SELECT text FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) WHERE r.session_id = t1.request_session_id ) AS 'wait_run_batch' , ( SELECT SUBSTRING(qt.text, r.statement_start_offset / 2 + 1, ( CASE WHEN r.statement_end_offset = -1 THEN DATALENGTH(qt.text) ELSE r.statement_end_offset END - r.statement_start_offset ) / 2 + 1) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt WHERE r.session_id = t1.request_session_id ) AS 'wait 运行的SQL语句' , t2.blocking_session_id AS '锁定sid' , ( SELECT text FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) WHERE p.spid = t2.blocking_session_id ) AS '锁定SQL' FROM sys.dm_tran_locks AS t1 INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address
3, SQL SERVER 后台连接情况
--请求 select s.session_id, s.status,db_name(r.database_id) as database_name, s.login_name,s.login_time, s.host_name, c.client_net_address,c.client_tcp_port,s.program_name, r.cpu_time, r.reads, r.writes,c.num_reads,c.num_writes, s.client_interface_name, s.last_request_start_time, s.last_request_end_time, c.connect_time, c.net_transport, c.net_packet_size, r.start_time, r.status, r.command, r.blocking_session_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count, r.percent_complete,r.granted_query_memory from Sys.dm_exec_requests r with(nolock) right outer join Sys.dm_exec_sessions s with(nolock) on r.session_id = s.session_id right outer join Sys.dm_exec_connections c with(nolock) on s.session_id = c.session_id where s.session_id >50 order by s.session_id --用户连接 select login_name,COUNT(0) user_count from Sys.dm_exec_requests r with(nolock) right outer join Sys.dm_exec_sessions s with(nolock) on r.session_id = s.session_id right outer join Sys.dm_exec_connections c with(nolock) on s.session_id = c.session_id where s.session_id >50 group by login_name order by 2 DESC --机器连接 select s.host_name,c.client_net_address,COUNT(0) host_count from Sys.dm_exec_requests r with(nolock) right outer join Sys.dm_exec_sessions s with(nolock) on r.session_id = s.session_id right outer join Sys.dm_exec_connections c with(nolock) on s.session_id = c.session_id where s.session_id >50 group by host_name,client_net_address order by 3 DESC --进程状态 select s.status,COUNT(0) host_count from Sys.dm_exec_requests r with(nolock) right outer join Sys.dm_exec_sessions s with(nolock) on r.session_id = s.session_id right outer join Sys.dm_exec_connections c with(nolock) on s.session_id = c.session_id where s.session_id >50 group by s.status order by 2 DESC
4， 内存使用情况
--查看内存状态 SELECT m.total_physical_memory_kb, m.available_physical_memory_kb, m.total_page_file_kb, m.available_page_file_kb, m.system_memory_state_desc FROM sys.dm_os_sys_memory m --查看各内存对象使用内存情况 --在SQL SERVER 2012及以上版本运行 SELECT M.type, sum(M.virtual_memory_reserved_kb) AS VirtualMemoryReservedKB, SUM(M.virtual_memory_committed_kb) AS VirtualMemortCommitedKB, SUM(M.shared_memory_committed_kb) AS SharedMemroyCommittedKB, SUM(M.shared_memory_reserved_kb) AS SharedMemroyReservedKB, SUM(M.pages_kb) AS PagesKB FROM sys.dm_os_memory_clerks M GROUP BY M.type ORDER BY PagesKB DESC --查看各数据库的内存使用情况 SET TRAN ISOLATION LEVEL READ UNCOMMITTED SELECT ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName , CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2)) AS [Size (MB)] FROM sys.dm_os_buffer_descriptors GROUP BY database_id ORDER BY DatabaseName --查看数据库内存相关性能计数器 SELECT * , CAST(P.cntr_value*8.0/1024.0 AS INT) AS MemoryMB FROM sys.sysperfinfo p WHERE p.object_name LIKE '%SQLServer:Buffer Manager%' SELECT * , CAST(P.cntr_value*8.0/1024.0 AS INT) AS MemoryMB FROM sys.sysperfinfo p WHERE p.object_name LIKE '%SQLServer:Memory Manager%'
5，CPU 历史使用情况
/* 查询数据库服务器CPU利用率的历史情况，每一分钟统计一次，看看被各个程序占用情况 */ DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info WITH (NOLOCK)); SELECT SQLProcessUtilization AS [SQL Server Process CPU Utilization], SystemIdle AS [System Idle Process], 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization], DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle], record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], [timestamp] FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers WITH (NOLOCK) WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' AND record LIKE N'%<SystemHealth>%') AS x) AS y ORDER BY record_id DESC OPTION (RECOMPILE);