常用的系统动态管理视图2

1.查询正在运行的SQL语句

Sys.dm_exec_connections、 sys.dm_exec_sessions 和 sys.dm_exec_requests 动态管理视图映射到sys.sysprocesses系统表。

Sys.dm_exec_connections：连接的信息

sys.dm_exec_sessions ：显示了有关所有活动用户连接和内部任务的会话信息

sys.dm_exec_requests ：正在执行的每个请求的信息

SELECT  
	er.session_Id AS [Spid] ,
	DB_NAME(er.database_id) AS [Database] ,
	ses.login_name AS [LoginName] ,   
	er.status AS [Status], 
	wait_type AS [Wait], 
	SUBSTRING(qt.text, er.statement_start_offset / 2,
				(CASE WHEN er.statement_end_offset = - 1 THEN LEN(qt.text)  * 2  ELSE er.statement_end_offset END  - er.statement_start_offset) / 2) AS  [Individual Query]  ,
	qt.text AS [Parent Query] , 
	ses.program_name  ,
	ses.host_name, 
	ses.nt_domain,
	er.start_time
FROM  sys.dm_exec_requests er 
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id
WHERE  er.session_Id > 50 
 AND er.session_Id NOT IN (@@SPID)

2.查询阻塞的语句

 --查看阻塞的根节点

 SELECT A.spid , c.text AS parent_query , 
 SUBSTRING( c.text , A.stmt_start/2 +1,(CASE WHEN a.stmt_end =-1 THEN LEN(c.text) *2 ELSE a.stmt_end END -a.stmt_start)/2+1) AS query
 FROM  sys.sysprocesses A 
 JOIN sys.sysprocesses b ON A.spid =b.blocked AND b.blocked >0 
 CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) C
 WHERE A.blocked =0 


--被阻塞的进程、等待类型
SELECT A.spid ,A.blocked , A.waittime ,B.wait_type ,C.text
FROM  sys.sysprocesses  A
JOIN   sys.dm_os_waiting_tasks  B  ON A.spid =B.session_id
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) C 
WHERE  A.blocked >0

 

--查看锁请求状态
 SP_lOCK

3. 查看死锁图，存在死锁后SQLsever会自行牺牲掉优先级低的会话，解除死锁

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph 
FROM ( SELECT XEvent.query('.') AS XEvent 
       FROM ( SELECT CAST(target_data AS XML) AS TargetData 
              FROM sys.dm_xe_session_targets st 
                   JOIN sys.dm_xe_sessions s 
                   ON s.address = st.event_session_address 
              WHERE s.name = 'system_health' 
                    AND st.target_name = 'ring_buffer' 
              ) AS Data 
              CROSS APPLY 
                 TargetData.nodes 
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent ) 
      ) AS src;

4.索引相关
--sys.dm_db_index_usage_stats 
--查询从未使用过的索引 ，也可查看索引使用情况，以便进行索引相关调整。

SELECT DB_NAME(a.database_id) AS DBname ,  b.name TabName,c.name  IndexName, e.name IndexCol  ,e.colid IndexPos 
FROM sys.dm_db_index_usage_stats a
JOIN sys.all_objects b ON A.object_id =B.object_id  
JOIN sys.indexes c ON b.object_id =c.object_id  AND c.index_id =a.index_id
JOIN sys.sysindexkeys d ON  c.index_id =d.indid  AND d.id =a.object_id
JOIN sys.syscolumns e ON d.colid =e.colid  AND e.id =B.object_id
WHERE a.user_seeks =0 AND a.user_scans =0 AND a.user_lookups =0 
ORDER BY a.database_id ,TabName ,e.colid ASC 

--sys.dm_db_index_operational_stats
--查询叶级数据更新数量 ，锁更新数量等情况
--可用于查看表的锁情况是否经常出现行锁、表锁，是否经常更新索引或数据等

--查看索引碎片化程度（内部碎片）

SELECT DB_NAME(ps.database_id) AS [Database Name]
    ,OBJECT_NAME(ps.[object_id]) AS [Object Name]
    ,i.[name] AS [Index Name]
    ,ps.index_id
    ,ps.index_type_desc
    ,ps.avg_fragmentation_in_percent
    ,ps.fragment_count
    ,ps.page_count
    ,i.fill_factor
    ,i.has_filter
    ,i.filter_definition
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK) ON ps.[object_id] = i.[object_id]
    AND ps.index_id = i.index_id
WHERE ps.database_id = DB_ID()
    AND ps.page_count > 2500
ORDER BY ps.avg_fragmentation_in_percent desc;

5.查询10个可能性能是最差的SQL语句

SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count"
    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC