【监控实践】【3.4】使用DMV和函数监控数据库状态和资源使用

0.类别DMV

官网：https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms189741(v=sql.90)

dm_db_*：数据库和数据库对象
dm_exec_*：执行用户代码和关联的连接
dm_os_*：内存、锁定和时间安排
dm_tran_*：事务和隔离
dm_io_*：网络和磁盘的输入/输出

0.1.最常用DMV

　　参考：https://www.cnblogs.com/gered/p/9239996.html

1、sys.dm_exec_cached_plans - 可用于SQL Server的缓存查询计划
2、sys.dm_exec_sessions - SQL Server中的会话
3、sys.dm_exec_connections - 连接到SQL Server
4、sys.dm_db_index_usage_stats - 搜索，扫描，查找每个索引
5、sys.dm_io_virtual_file_stats - 数据库和日志文件的IO统计信息
6、sys.dm_tran_active_transactions - SQL Server实例的事务状态
7、sys.dm_exec_sql_text - 返回TSQL代码
8、sys.dm_exec_query_plan - 返回查询计划
9、sys.dm_os_wait_stats - 返回信息SQL正在等待的资源
10、sys.dm_os_performance_counters - 返回与SQL Server相关的性能监视器计数器

--一些核心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 --查看实例锁情况

【1】一些常用的实践

【1.1】查看当前实例运行进程

-- 核心DMV、函数、系统SP：
/*
　　所有进程请求：sys.dm_exec_requests
　　所有进程与连接：sys.sysprocesses
　　系统函数，查看sql：sys.[dm_exec_sql_text] --根据sql_handle
　　dbcc命令，查看进程sql：dbcc inputbuffer(spid/session_id)
　　系统函数，查看执行计划：sys.dm_exec_query_plan --根据plan_handle
　　查看短期内执行计划状态：dm_exec_query_stats
　　查看数据库信息：sys.databases

*/

select db_name(r.database_id) db_name,status,start_time,command,percent_complete,wait_type,text as parent_query,
substring(s.text,r.statement_start_offset/2,
   (case when r.statement_end_offset = -1
   then len(convert(nvarchar(max), s.text)) * 2
   else r.statement_end_offset end - r.statement_start_offset)/2)
   as query_text,
session_id,blocking_session_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) s    

-- 相关查询

--详细版
SELECT TOP 10
[cpu_time],
[session_id],
[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.[dm_exec_requests] AS der 
CROSS APPLY 
sys.[dm_exec_sql_text](der.[sql_handle]) AS dest 
 
ORDER BY [cpu_time] DESC

--相关查阅
SELECT  spid,
            start_time,
            [Database] = DB_NAME(sp.dbid) ,
            command,
            [User] = nt_username ,
            [Status] = er.status ,
            [Wait] = wait_type ,
            [Individual Query] = SUBSTRING(qt.text,
                                           er.statement_start_offset / 2,
                                           ( CASE WHEN er.statement_end_offset = -1
                                                  THEN LEN(CONVERT(NVARCHAR(MAX), qt.text))
                                                       * 2
                                                  ELSE er.statement_end_offset
                                             END - er.statement_start_offset )
                                           / 2) ,
            [Parent Query] = qt.text ,
            Program = program_name ,
            hostname ,
            nt_domain ,
[Spid] = session_id ,
blocking_session_id
FROM sys.dm_exec_requests er INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt 
WHERE session_id > 50 -- Ignore system spids. 
AND session_id NOT IN ( @@SPID ) -- Ignore this current statement. ORDER BY 1

--查看所有SQL正在执行的进度详情
SELECT
r.session_id ,
DB_NAME(qt.[dbid]) AS [DatabaseName] ,
r.start_time,
r.[status],
r.blocking_session_id,
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) AS [statement] ,
r.wait_type,
r.wait_time,
r.wait_resource,
r.cpu_time ,
r.total_elapsed_time / 60000 AS[elapsed_minutes],
r.reads ,
r.writes ,
r.logical_reads,
s.host_name,s.program_name
FROM sys.dm_exec_requests AS r
join sys.dm_exec_sessions s on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY 1

 

 

---SQL Server查询正在执行的SQL语句及执行计划
select ds.session_id,dr.start_time,db_name(dr.database_id),dr.blocking_session_id,ds.host_name,
ds.program_name,ds.host_process_id,ds.login_name,dr.status,
dr.command,dr.wait_type,dr.wait_time,dr.open_transaction_count,
dr.percent_complete,dr.estimated_completion_time,dr.row_count,
SUBSTRING(st.text, (dr.statement_start_offset/2)+1, 
((CASE dr.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE dr.statement_end_offset
END - dr.statement_start_offset)/2) + 1) AS statement_text,
st.text as full_text,
qp.query_plan
from sys.dm_exec_sessions ds,sys.dm_exec_requests dr--,sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(dr.sql_handle) AS st 
CROSS APPLY sys.dm_exec_query_plan(dr.plan_handle) as qp
where ds.session_id>50
and dr.session_id<>@@spid
and ds.session_id=dr.session_id
--and dr.sql_handle=qs.sql_handle
and dr.database_id>4

-- 2008R2以下版本 想要看图形界面，直接复制内容，重命名为.sqlplan

 

--查看阻塞与被阻塞语句

SELECT R.session_id AS BlockedSessionID , 
S.session_id AS BlockingSessionID , 
Q1.text AS BlockedSession_TSQL , 
Q2.text AS BlockingSession_TSQL , 
C1.most_recent_sql_handle AS BlockedSession_SQLHandle , 
C2.most_recent_sql_handle AS BlockingSession_SQLHandle , 
S.original_login_name AS BlockingSession_LoginName , 
S.program_name AS BlockingSession_ApplicationName , 
S.host_name AS BlockingSession_HostName 
FROM sys.dm_exec_requests AS R 
INNER JOIN sys.dm_exec_sessions AS S ON R.blocking_session_id = S.session_id 
INNER JOIN sys.dm_exec_connections AS C1 ON R.session_id = C1.most_recent_session_id 
INNER JOIN sys.dm_exec_connections AS C2 ON S.session_id = C2.most_recent_session_id 
CROSS APPLY sys.dm_exec_sql_text(C1.most_recent_sql_handle) AS Q1 
CROSS APPLY sys.dm_exec_sql_text(C2.most_recent_sql_handle) AS Q2

查看当前实例正在运行的SQL进程与阻塞情况

【1.2】查看当前实例锁信息

-- 核心DMV：
/* 
　　参考联机丛书：https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017
　　查看锁信息DMV：sys.dm_tran_locks
　　锁视图：sp_lock
*/

SELECT l.resource_type, l.resource_associated_entity_id
 ,OBJECT_NAME(sp.OBJECT_ID) AS ObjectName
 ,l.request_status, l.request_mode,request_session_id
 ,l.resource_description
 FROM sys.dm_tran_locks l
 LEFT JOIN sys.partitions sp
  ON sp.hobt_id = l.resource_associated_entity_id
 WHERE l.resource_database_id = DB_ID()

【1.3】查看当前实例阻塞信息

/* 
　　等待任务：sys.dm_os_waiting_tasks
　　锁拥有者信息：t1.lock_owner_address
　　系统分区信息：sys.partitions
*/


SELECT
 t1.resource_type
 ,db_name(t1.resource_database_id) as db_name
 ,t1.resource_associated_entity_id
 ,OBJECT_NAME(sp.OBJECT_ID) AS ObjectName
 ,t1.request_mode
 ,t1.request_session_id
 ,t2.blocking_session_id
 FROM sys.dm_tran_locks as t1
 JOIN sys.dm_os_waiting_tasks as t2
   ON t1.lock_owner_address = t2.resource_address
 LEFT JOIN sys.partitions sp
   ON sp.hobt_id = t1.resource_associated_entity_id

【1.4】查看索引使用率

索引相关优化参考另一篇博文：https://www.cnblogs.com/gered/p/11044571.html

/*
　　查看数据库信息：sys.databases
　　查看索引使用率：sys.dm_db_index_usage_stats
　　查看所有索引：sys.indexes
　　查看数据库所有对象：sys.all_objects , sysobjects
*/

SELECT
  sd.name AS DatabaseName
 ,so.name AS ObjectName
 ,si.name AS IndexName
 ,diu.user_seeks
 ,diu.user_scans
 ,diu.user_lookups
 ,diu.user_updates
 ,diu.last_user_seek
 ,diu.last_user_scan
 ,diu.last_user_lookup
 ,diu.last_user_update
 ,GETDATE() AS StatusDate
 FROM sys.dm_db_index_usage_stats  diu
 JOIN sys.indexes si
   ON diu.object_id = si.object_id
  AND diu.index_id = si.index_id
 JOIN sys.all_objects so
   ON so.object_id = si.object_id
 JOIN sys.databases sd
   ON sd.database_id = diu.database_id
 WHERE is_ms_shipped <> 1
   AND diu.database_id = DB_ID()

【1.5】没有使用的索引

--------------------------------------------------------------------------
 --This will store the indexes which are not used.
 
/*
IF OBJECT_ID('dbo.NotUsedIndexes') IS NULL
 CREATE TABLE dbo.NotUsedIndexes
 (
  IndexName sysname NULL
 ,ObjectName sysname NOT NULL
 ,StatusDate datetime NOT NULL
 ,DatabaseName sysname NOT NULL
 )
 
 ----Below query will give you index which are NOT used per table in a database.
 INSERT dbo.NotUsedIndexes
 (
  IndexName
 ,ObjectName
 ,StatusDate
 ,DatabaseName
 )
*/
 SELECT
  si.name AS IndexName
 ,so.name AS ObjectName
 ,GETDATE() AS  StatusDate
 ,DB_NAME()
 FROM sys.indexes si
 JOIN sys.all_objects so
   ON so.object_id = si.object_id
 WHERE si.index_id NOT IN (SELECT index_id
                           FROM sys.dm_db_index_usage_stats diu
                           WHERE si.object_id = diu.object_id
                             AND si.index_id = diu.index_id
                           )
   AND so.is_ms_shipped <> 1

  -- select * from NotUsedIndexes

【1.6】查看等待内存授权的数据（2005不可用，2012及以上）

SELECT
es.session_id AS SPID
,es.login_name
,es.host_name
,es.program_name, es.status AS Session_Status
,mg.requested_memory_kb
,DATEDIFF(mi, mg.request_time
, GETDATE()) AS [WaitingSince-InMins]
FROM sys.dm_exec_query_memory_grants mg
JOIN sys.dm_exec_sessions es
ON es.session_id = mg.session_id
WHERE mg.grant_time IS NULL
ORDER BY mg.request_time

【1.7】查看实例连接信息

详细参考：如何在SqlServer中获取前端连接的IP地址，计算机名等信息

/*

　　连接执行会话信息：Sys.dm_exec_Sessions

　　每个连接的详细信息：Sys.dm_exec_connections

*/　　

SELECT login_name
, count(session_id) as session_count
FROM sys.dm_exec_sessions
GROUP BY login_name

【1.8】查看文件组信息

-- Find the total size of each Filegroup
 select data_space_id, (sum(size)*8)/1000 as total_size_MB
 into #filegroups
 from sys.database_files
 group by data_space_id
 order by data_space_id
 
 -- FInd how much we have allocated in each FG
 select ds.name, au.data_space_id
 , (sum(au.total_pages) * 8)/1000 as Allocated_MB
 , (sum(au.used_pages) * 8)/1000 as used_MB
 , (sum(au.data_pages) * 8)/1000 as Data_MB
 , ((sum(au.total_pages) -  sum(au.used_pages) ) * 8 )/1000 as Free_MB
 into #Allocations
 from sys.allocation_units as au inner join sys.data_spaces as ds
     on au.data_space_id = ds.data_space_id
 group by ds.name, au.data_space_id
 order by au.data_space_id
 -- Bring it all together
 select f.data_space_id
 , a.name
 , f.total_size_MB
 , a.allocated_MB
 , f.total_size_MB - a.allocated_MB as free_in_fg_MB
 , a.used_MB
 , a.data_MB
 , a.Free_MB
 from #filegroups as f inner join #allocations as a
 on f.data_space_id = a.data_space_id
 order by f.data_space_id
 
 drop table #allocations
 drop table #filegroups

【1.9】查看每个数据库的内存缓存

--查看每个数据库缓存大小
SELECT  COUNT(*) * 8 / 1024 AS 'Cached Size (MB)' ,
        CASE database_id
          WHEN 32767 THEN 'ResourceDb'
          ELSE DB_NAME(database_id)
        END AS 'Database'
FROM    sys.dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ,
        database_id
ORDER BY 'Cached Size (MB)' DESC

【1.10】CPU、重编译

https://www.cnblogs.com/gered/p/9961370.html

同类参考文件（必看）

sql server使用DMV排查系统异常

核心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       --查看实例锁情况