sql server使用DMV监控

数据库系统异常是DBA经常要面临的情景，一名有一定从业经验的DBA，都会有自己一套故障排查的方法和步骤，此文为为大家介绍一下通过系统

性能视图（SQLServer05以上版本）来排查系统异常的基本方法，希望能对大家有所帮助。

【0】DMV监控

需要的权限

USE master;
GO
CREATE LOGIN [telegraf] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW SERVER STATE TO [telegraf];
GO
GRANT VIEW ANY DEFINITION TO [telegraf];
GO
For Azure SQL Database, you require the View Database State permission and can create a user with a password directly in the database.

CREATE USER [telegraf] WITH PASSWORD = N'mystrongpassword';
GO
GRANT VIEW DATABASE STATE TO [telegraf];
GO

【0.1】基本版本

效果计数器：来自1000多个指标 `sys.dm_os_performance_counters`

等待统计：等待任务分类为 sys.dm_os_wait_stats
内存业务员：来自的内存故障 sys.dm_os_memory_clerks
数据库大小：数据库大小趋势从 sys.dm_io_virtual_file_stats
数据库IO：来自的数据库I / O sys.dm_io_virtual_file_stats
数据库延迟：来自的数据库延迟 sys.dm_io_virtual_file_stats
数据库属性：数据库属性，状态和恢复模型，来自 sys.databases
操作系统容量：可用空间，已用空间和总空间 sys.dm_os_volume_stats
CPU：CPU使用率 sys.dm_os_ring_buffers

【0.2】更新版本

数据库IO：来自的IO统计信息 sys.dm_io_virtual_file_stats
记忆文员：来自的记忆文员分类 sys.dm_os_memory_clerks，大多数文员都给了一个友好的名字。
性能计数器：来自的性能计数器的选择列表 sys.dm_os_performance_counters。一些重要的指标包括：服务器属性：处于所有可能状态（联机，脱机，可疑等）的数据库数，cpu计数，物理内存，SQL Server服务正常运行时间和SQL Server版本。对于Azure SQL相关属性，例如Tier，＃Vcores，Memory等。
- 活动：事务/秒/数据库，批处理请求/秒，被阻止的进程，以及更多
- 可用性组：发送到副本的字节，从副本接收的字节，已接收的日志字节，日志发送队列，事务延迟等
- 日志活动：日志字节/秒刷新，日志刷新/秒，日志刷新等待时间
- 内存：PLE，每秒页面读取，每秒页面写入等
- TempDB：可用空间，版本存储使用率，活动临时表，临时表创建率等
- 资源调控器：每个工作负载组的CPU使用率，每秒请求数，已排队请求数和已阻止任务等
等待统计信息：等待时间（以毫秒为单位），等待任务的数量，资源等待时间，信号等待时间，最大等待时间（以毫秒为单位），等待类型和等待类别。使用查询存储中使用的相同类别对等待进行分类。
计划程序 -捕获 sys.dm_os_schedulers。
SqlRequests-捕获 dm_exec_requests 和 dm_exec_sessions 的快照，使您可以运行请求以及等待类型和阻止会话。
VolumeSpace-使用 sys.dm_os_volume_stats 来获取每个包含数据或日志文件的磁盘上的总空间，已使用空间和已占用空间。（请注意，即使启用，它也不会从Azure SQL数据库或SQL托管实例获取任何数据）。高频运行（即每10秒一次）是没有意义的，但是不会造成任何问题。
CPU -使用缓冲环（sys.dm_os_ring_buffers）获得CPU的数据，该表是每分钟更新一次。（请注意，即使启用，它也不会从Azure SQL数据库或SQL托管实例获取任何数据）。为了允许在每个语句的基础上进行跟踪，此查询为每个查询生成唯一的标记。根据数据库的工作量，这可能会导致基数较高。有关管理系列基数的提示，请参考FAQ 。

可以直接使用以下指标，而无需进行增量计算：

SQLServer：Buffer Manager 缓冲区高速缓存命中率
SQLServer：缓冲区管理器页面预期寿命
SQLServer：缓冲区节点页面寿命期望
SQLServer：数据库副本日志应用暂挂队列
SQLServer：数据库副本日志应用就绪队列
SQLServer：数据库副本日志发送队列
SQLServer：数据库副本恢复队列
SQLServer：数据库数据文件的大小（KB）
SQLServer：数据库日志文件的大小（KB）
SQLServer：数据库日志文件使用的大小（KB）
SQLServer：数据库使用的XTP内存（KB）
SQLServer：常规统计信息活动临时表
SQLServer：常规统计信息进程已阻止
SQLServer：General Statistics Temp表进行销毁
SQLServer：常规统计信息用户连接
SQLServer：内存代理文员内存代理文员大小
SQLServer：内存管理器内存授予待定
SQLServer：内存管理器目标服务器内存（KB）
SQLServer：内存管理器服务器总内存（KB）
SQLServer：资源池统计信息活动内存授予量（KB）
SQLServer：资源池统计信息磁盘读取字节/秒
SQLServer：资源池统计信息磁盘读取IO限制/秒
SQLServer：资源池统计信息磁盘读取IO /秒
SQLServer：资源池统计信息磁盘写字节数/秒
SQLServer：资源池统计信息磁盘写IO限制/秒
SQLServer：资源池统计信息磁盘写入IO /秒
SQLServer：资源池统计信息已用内存（KB）
SQLServer：Transactions tempdb中的可用空间（KB）
SQLServer：事务版本存储大小（KB）
SQLServer：用户可设置查询
SQLServer：工作负载组统计信息阻止的任务
SQLServer：工作负载组统计信息 CPU使用率％
SQLServer：工作负载组统计信息排队的请求
SQLServer：工作负载组统计请求完成/秒

QLServer:Buffer ManagerBuffer cache hit ratio
SQLServer:Buffer ManagerPage life expectancy
SQLServer:Buffer NodePage life expectancy
SQLServer:Database ReplicaLog Apply Pending Queue
SQLServer:Database ReplicaLog Apply Ready Queue
SQLServer:Database ReplicaLog Send Queue
SQLServer:Database ReplicaRecovery Queue
SQLServer:DatabasesData File(s) Size (KB)
SQLServer:DatabasesLog File(s) Size (KB)
SQLServer:DatabasesLog File(s) Used Size (KB)
SQLServer:DatabasesXTP Memory Used (KB)
SQLServer:General StatisticsActive Temp Tables
SQLServer:General StatisticsProcesses blocked
SQLServer:General StatisticsTemp Tables For Destruction
SQLServer:General StatisticsUser Connections
SQLServer:Memory Broker ClerksMemory broker clerk size
SQLServer:Memory ManagerMemory Grants Pending
SQLServer:Memory ManagerTarget Server Memory (KB)
SQLServer:Memory ManagerTotal Server Memory (KB)
SQLServer:Resource Pool StatsActive memory grant amount (KB)
SQLServer:Resource Pool StatsDisk Read Bytes/sec
SQLServer:Resource Pool StatsDisk Read IO Throttled/sec
SQLServer:Resource Pool StatsDisk Read IO/sec
SQLServer:Resource Pool StatsDisk Write Bytes/sec
SQLServer:Resource Pool StatsDisk Write IO Throttled/sec
SQLServer:Resource Pool StatsDisk Write IO/sec
SQLServer:Resource Pool StatsUsed memory (KB)
SQLServer:TransactionsFree Space in tempdb (KB)
SQLServer:TransactionsVersion Store Size (KB)
SQLServer:User SettableQuery
SQLServer:Workload Group StatsBlocked tasks
SQLServer:Workload Group StatsCPU usage %
SQLServer:Workload Group StatsQueued requests
SQLServer:Workload Group StatsRequests completed/sec

【1】从数据库连接情况来判断异常

【1.1】目前数据库系统所有请求情况

--request info
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

某台生产机运行情况：

这个查询将目前数据库中的所有请求都显示出来了，其中比较重要的有Status、Login_name、Host_Name，Client_Net_Address、Program_name

等，但是信息比较多，我们很难查看有什么异常，但是可以通过一图中红色圈的数字：441 初步判断连接数是否超过了平时的标准（很多时候系统异常是连接

数过多造成的，而连接数过多又是因为其他原因影响的）。

【1.2】哪个用户连接数最多

--request info by user
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

运行结果：

从图中我们可以很方便的看出用户连接数情况，如果我们的不同的功能是使用不同的的数据库账号的话，就能初步判断是哪部分功能可能出现了异常。

【1.3】哪台机器发起到数据库的连接数最多

--request info by hostname
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

运行结果：

这个查询能够一下就帮我们找出来哪些机器发起了对数据库的链接，它们的链接数量是否有异常；这个其实对调查某些问题非常有用，我有一次就遇

到一个case：

用户反映，过一两个星期，系统就会出现一次异常，出问题时数据库连接数量很高，大量的访问被数据库拒绝，过半个小时左右，系统又自动恢复了，但是

在数据库里面查看，并没有发现有异常的进程和错误的信息，问题一时很棘手，很难定位，系统不稳定领导不满，DBA顶着压力一时不知道如何是好；后面

转换方向，通过调查问题发生时，为什么会产生这么多连接，这些连接是那些机器发过来的，这些连接发过来正常吗，是数据库不砍业务的重负，还是业务

在某个时间段内会出现暴涨等一系列原因，最终找出是一台Web因为开发人员代码写的有问题，内存出现内存泄露，导致大量的连接不能释放，出问题是，

发出的数据库连接数比平时高3-4倍，最终影响到了数据库，问题压根和数据库没关系（从这个事实看出，DBA真是的炮灰角色，不是自己的问题，也得顶

着压力调查出原因呀）；如果在类似问题发生时，我们能通过这个查询及早知道问题是出在某台Web机器上，那就不用费尽心力来调查数据库了。

【1.4】这些连接在访问哪个库

--request info by databases
select db_name(r.database_id) as database_name,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 r.database_id
order by 2 desc

结果（为NULL的估计是没办法定位库）：

【1.5】进程状态

--request info by status
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

结果（running数比较多，表面数据库压力比较大）：

【2】从阻塞情况来判断异常

（这部分内容不再一一贴图，直接上脚本）：

【2.1】查看数据库阻塞情况

----------------------------------------Blocked Info----------------------------------
--记录当前阻塞信息 
select t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]    
,t1.resource_associated_entity_id as [blk object]    
,t1.request_mode as [lock req]                          -- lock requested    
,t1.request_session_id as [waiter sid]                      -- spid of waiter    
,t2.wait_duration_ms as [wait time]          
,(select text from sys.dm_exec_requests as r with(nolock)                 --- get sql for waiter    
cross apply sys.dm_exec_sql_text(r.sql_handle)     
where r.session_id = t1.request_session_id) as waiter_batch    
,(select substring(qt.text,r.statement_start_offset/2,     
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2     
else r.statement_end_offset end - r.statement_start_offset)/2+1)     
from sys.dm_exec_requests as r with(nolock)     
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt    
where r.session_id = t1.request_session_id) as waiter_stmt    --- statement executing now    
,t2.blocking_session_id as [blocker sid]                --- spid of blocker    
,(select text from sys.sysprocesses as p with(nolock)    --- get sql for blocker    
cross apply sys.dm_exec_sql_text(p.sql_handle)     
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time   
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)      
where t1.lock_owner_address = t2.resource_address

【2.2】查看阻塞其他进程的进程（阻塞源头）

--阻塞其他session的session 
select  t2.blocking_session_id,COUNT(0) counts
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)    
where t1.lock_owner_address = t2.resource_address
group by blocking_session_id
order by 2

【2.3】被阻塞时间最长的进程

--被阻塞时间最长的session
select top 10  t1.resource_type as [lock type] ,db_name(resource_database_id) as [database]    
,t1.resource_associated_entity_id as [blk object]    
,t1.request_mode as [lock req]                          -- lock requested    
,t1.request_session_id as [waiter sid]                      -- spid of waiter    
,t2.wait_duration_ms as [wait time]          
,(select text from sys.dm_exec_requests as r with(nolock)                 --- get sql for waiter    
cross apply sys.dm_exec_sql_text(r.sql_handle)     
where r.session_id = t1.request_session_id) as waiter_batch    
,(select substring(qt.text,r.statement_start_offset/2,     
(case when r.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2     
else r.statement_end_offset end - r.statement_start_offset)/2+1)     
from sys.dm_exec_requests as r with(nolock)     
cross apply sys.dm_exec_sql_text(r.sql_handle) as qt    
where r.session_id = t1.request_session_id) as waiter_stmt    --- statement executing now    
,t2.blocking_session_id as [blocker sid]                --- spid of blocker    
,(select text from sys.sysprocesses as p with(nolock)    --- get sql for blocker    
cross apply sys.dm_exec_sql_text(p.sql_handle)     
where p.spid = t2.blocking_session_id) as blocker_stmt,getdate() time   
from sys.dm_tran_locks as t1 with(nolock) , sys.dm_os_waiting_tasks as t2 with(nolock)      
where t1.lock_owner_address = t2.resource_address
order by t2.wait_duration_ms desc

【3】核心dmv

select * from test.sys.dm_tran_locks --库级别：查看该库锁情况
select * from master.sys.dm_os_performance_counters --实例级别：启动后的累计性能计数器
select * from master.sys.dm_os_wait_stats --实例级别：查看当前所有等待统计
select * from master.sys.dm_os_waiting_tasks --实例级别：查看当前所有等待的进程任务情况
select * from master.sys.dm_exec_requests    --实例级别：查看当前所有的请求信息
select * from master.sys.dm_exec_sessions     --实例级别：查看当前所有的登陆会话信息
select * from master.sys.dm_exec_connections --实例级别：查看当前所有的连接信息
select * from master.sys.sysprocesses         --实例级别：查看当前所有的连接进程
select * from master.sys.dm_exec_query_stats --实例级别：查看执行计划/缓存，以此可以查看过去一段时间的慢SQL
cross apply sys.dm_exec_sql_text(sql_handle) --一般用这个函数来解析sql语句 


exec sp_who        --查看实例登陆情况系统sp
exec sp_who2 'sa'  --查看制定用户登录情况
exec sp_lock       --查看实例锁情况

参考：https://www.cnblogs.com/fygh/archive/2012/03/12/2391764.html