随着数据量和并发量的增大，数据库有时会遇到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);