SQL Server 之 DMV（Dynamic Management View）
转自：https://leongfeng.github.io/2017/03/28/sqlserver-dmv-list/
SQL Server数据库内置的 动态管理视图DMV和 函数DMF，通过监视服务器实例的运行状况、诊断故障以及优化性能的服务器状态信息，可用于进行故障诊断、性能调优和状态监控等。DMV 和 DMF 保存在 sys_schema 中，以dm_xx开头， 有服务器和数据库范围两种类型，因此，要查询这些范围内的 DMV&DMF**， 用户需要有 VIEW SERVER/DATABASE STATE 权限。
本文将列出一些可能需要使用的常用场景，如果需要查看所有的细节，请参考文档。
附件是一个关于索引和执行计划相关的DMV：DMV_in_SqlServer.sql
1 有关阻塞、内存相关的DMV
dm_exec_requests
这个SQL DMV包括该查询和查询计划有关的详细信息，还包括请求状态和关于查询执行时间的信息。
通过下面语句可能查看正在阻塞的会话连接：
SELECT session_id, blocking_session_id, status, wait_time, wait_type, wait_resource, transaction_id, lock_timeout FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;
查看没关闭事务的空闲Session：
SELECT es.session_id, es.login_name, es.host_name, est.text, cn.last_read, cn.last_write, es.program_name FROM sys.dm_exec_sessions AS es INNER JOIN sys.dm_tran_session_transactions AS st ON es.session_id = st.session_id INNER JOIN sys.dm_exec_connections AS cn ON es.session_id = cn.session_id CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) AS est LEFT OUTER JOIN sys.dm_exec_requests AS er ON st.session_id = er.session_id AND er.session_id IS NULL;
dm_exec_connections
返回关于 SQL Server 连接的信息以及每个连接的详细信息。
下面的查询语句可以查询连接到SQL Server上每个会话最近执行的SQL文本:
SELECT ec.session_id, ec.connect_time, ec.client_net_address, ec.last_read, ec.last_write, ec.auth_scheme, qt.text
FROM sys.dm_exec_connections AS ec
CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) AS qt;
查看被阻塞的语句和它们的等待时间：
SELECT Waits.wait_duration_ms / 1000 AS WaitInSeconds
, Blocking.session_id AS BlockingSessionId
, DB_NAME(Blocked.database_id) AS DatabaseName
, Sess.login_name AS BlockingUser
, Sess.host_name AS BlockingLocation
, BlockingSQL.text AS BlockingSQL
, Blocked.session_id AS BlockedSessionId
, BlockedSess.login_name AS BlockedUser
, BlockedSess.host_name AS BlockedLocation
, BlockedSQL.text AS BlockedSQL
, SUBSTRING(BlockedSQL.text, (BlockedReq.statement_start_offset/2)+1,
((CASE WHEN BlockedReq.statement_end_offset = -1
THEN LEN(CONVERT( NVARCHAR(MAX), BlockedSQL.text))*2
ELSE BlockedReq.statement_end_offset
END-BlockedReq.statement_start_offset)/2)+1) AS [Blocked Individual Query]
, Waits.wait_type
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_exec_sessions Sess ON Blocking.session_id = sess.session_id
INNER JOIN sys.dm_tran_session_transactions st ON Blocking.session_id = st.session_id
LEFT OUTER JOIN sys.dm_exec_requests er ON st.session_id = er.session_id AND er.session_id IS NULL
INNER JOIN sys.dm_os_waiting_tasks AS Waits ON Blocked.session_id = Waits.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS BlockingSQL
INNER JOIN sys.dm_exec_requests AS BlockedReq ON Waits.session_id = BlockedReq.session_id
INNER JOIN sys.dm_exec_sessions AS BlockedSess ON Waits.session_id = BlockedSess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
--WHERE Waits.wait_duration_ms > 30000 -阻塞等待时间超过30秒
ORDER BY WaitInSeconds;
dm_os_wait_stats
返回执行的线程所遇到的所有等待的相关信息。 可以使用此聚合视图来诊断 SQL Server 以及特定查询和批处理的性能问题。
你还可以使用这个DMV计算信号等待，通常信号等待超过 20% 都是CPU压力的表现：
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS SignalWaitsPercent FROM sys.dm_os_wait_stats;
dm_os_sys_info
获取SQL Server服务器的基本硬件信息：
SELECT cpu_count / hyperthread_ratio AS PhysicalCPU, cpu_count AS LogicalCPU, hyperthread_ratio AS HyperThreadRatio , physical_memory_kb / 1024 AS PhysicalMemoryInMB, virtual_memory_kb / 1024 AS VirtualMemoryInMB, affinity_type_desc, sqlserver_start_time FROM sys.dm_os_sys_info;
[dm_os_sys_memory]
返回服务器的内存信息
SELECT [total_physical_memory_kb] / 1024 AS [PhysicalMemoryInMB] ,[available_page_file_kb] / 1024 AS [AvailablePhysicalMemoryInMB] ,[total_page_file_kb] / 1024 AS [TotalPageFileInMB] ,[available_page_file_kb] / 1024 AS [AvailablePageFileMB] ,[kernel_paged_pool_kb] / 1024 AS [KernelPagedPoolMB] ,[kernel_nonpaged_pool_kb] / 1024 AS [KernelNonpagedPoolMB] ,[system_memory_state_desc] AS [SystemMemoryStateDesc] FROM sys.dm_os_sys_memory
dm_io_virtual_file_stats
查看某个查询用了多少TempDB空间：
DECLARE @read BIGINT, @write BIGINT; SELECT @read = SUM(num_of_bytes_read), @write = SUM(num_of_bytes_written) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS'; --这里放入需要测量的语句 SELECT tempdb_read_MB = ( SUM(num_of_bytes_read) - @read ) / 1024.0 / 1024.0 , tempdb_write_MB = ( SUM(num_of_bytes_written) - @write ) / 1024. / 1024. , internal_use_MB = (SELECT internal_objects_alloc_page_count / 128.0 FROM sys.dm_db_task_space_usage WHERE session_id = @@SPID) FROM tempdb.sys.database_files AS DBF JOIN sys.dm_io_virtual_file_stats(2, NULL) AS FS ON FS.file_id = DBF.file_id WHERE DBF.type_desc = 'ROWS';
数据库级别等待的IO：
SELECT DB_NAME(database_id) AS DatabaseName
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20, 2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20, 2))) AS [IO read (MB)]
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20, 2))) AS [IO written (MB)]
, SUM(CAST((num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0 AS DECIMAL(20, 2))) AS [TotalIO (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
ORDER BY [IO stall (secs)] DESC;
按文件查看IO情况：
SELECT DB_NAME(database_id) AS [DatabaseName]
, file_id
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20, 2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20, 2))) AS [IO read (MB)]
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20, 2))) AS [IO written (MB)]
, SUM(CAST((num_of_bytes_read + num_of_bytes_written) / 1024.0 / 1024.0 AS DECIMAL(20, 2))) AS [TotalIO (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id, file_id
ORDER BY [IO stall (secs)] DESC;
dm_os_buffer_descriptors
返回有关 SQL Server 缓冲池中当前所有数据页的信息。
buffer中缓存每个数据库所占的buffer：
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 OBJECT_NAME(p.object_id) AS TableName
, (COUNT(*) * 8) / 1024 AS [Buffer size(MB)]
, ISNULL(i.name, '-- HEAP --') AS ObjectName
, COUNT(*) AS NumberOf8KPages
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
INNER JOIN sys.indexes AS i ON p.index_id = i.index_id AND p.object_id = i.object_id ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.object_id > 100
GROUP BY p.object_id, i.name
ORDER BY NumberOf8KPages DESC;
索引相关的DMV
dm_db_index_usage_stats
返回 SQL Server 中不同类型索引操作的计数以及上次执行每种操作的时间。
查询表的最后修改时间：
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update, * FROM sys.dm_db_index_usage_stats WHERE OBJECT_ID = OBJECT_ID(tableName);
查看那些被大量更新，却很少被使用的索引：
SELECT DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
INTO #TempUnusedIndexes
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1 = 2;
EXEC sp_MSForEachDB
'USE [?];
INSERT INTO #TempUnusedIndexes
SELECT TOP 20
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND s.user_seeks = 0
AND s.user_scans = 0
AND s.user_lookups = 0
AND i.name IS NOT NULL
ORDER BY s.user_updates DESC';
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC;
DROP TABLE #TempUnusedIndexes;
最高维护代价的索引：
SELECT DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_updates) AS [update usage]
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval usage]
, (s.user_updates) - (s.user_seeks + s.user_scans + s.user_lookups) AS [Maintenance cost]
, s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
, s.last_user_seek
, s.last_user_scan
, s.last_user_lookup
INTO #TempMaintenanceCost
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1 = 2;
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempMaintenanceCost
SELECT TOP 20
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_updates ) AS [update usage]
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval usage]
, (s.user_updates) - (s.user_seeks + user_scans + s.user_lookups) AS [Maintenance cost]
, s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
, s.last_user_seek
, s.last_user_scan
, s.last_user_lookup
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
AND (s.user_seeks + s.user_scans + s.user_lookups) > 0
ORDER BY [Maintenance cost] DESC'
SELECT top 20 * FROM #TempMaintenanceCost ORDER BY [Maintenance cost] DESC;
DROP TABLE #TempMaintenanceCost;
使用频繁的索引：
SELECT DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.object_id) AS TableName
, i.name AS IndexName
, s.user_seeks + s.user_scans + s.user_lookups AS Usage
, s.user_updates
, i.fill_factor
INTO #TempUsage
FROM sys.dm_db_index_usage_stats AS s
INNER JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id
INNER JOIN sys.objects AS o ON i.object_id = O.object_id
WHERE 1 = 2;
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempUsage
SELECT TOP 20
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]
, s.user_updates
, i.fill_factor
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Usage] DESC'
SELECT TOP 20 * FROM #TempUsage ORDER BY [Usage] DESC
DROP TABLE #TempUsage
碎片最多的索引:
SELECT DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent, 2) AS [Fragmentation %]
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1 = 2;
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempFragmentation
SELECT TOP 20
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
ORDER BY [Fragmentation %] DESC'
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC;
DROP TABLE #TempFragmentation;
自上次SQL Server重启后，找出完全没有使用的索引：
-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT DB_NAME() AS DatbaseName
, SCHEMA_NAME(O.Schema_ID) AS SchemaName
, OBJECT_NAME(I.object_id) AS TableName
, I.name AS IndexName
INTO #TempNeverUsedIndexes
FROM sys.indexes I
INNER JOIN sys.objects O ON I.object_id = O.object_id
WHERE 1 = 2;
EXEC sp_MSForEachDB 'USE [?];
INSERT INTO #TempNeverUsedIndexes
SELECT DB_NAME() AS DatbaseName
, SCHEMA_NAME(O.Schema_ID) AS SchemaName
, OBJECT_NAME(I.object_id) AS TableName
, I.NAME AS IndexName
FROM sys.indexes I
INNER JOIN sys.objects O ON I.object_id = O.object_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id AND I.index_id = S.index_id AND DATABASE_ID = DB_ID()
WHERE OBJECTPROPERTY(O.object_id, '' IsMsShipped '') = 0 AND I.name IS NOT NULL AND S.object_id IS NULL;'
SELECT * FROM #TempNeverUsedIndexes ORDER BY DatbaseName, SchemaName, TableName, IndexName;
DROP TABLE #TempNeverUsedIndexes;
参考：
利用SQL DMV管理SQL Server数据库
如何查看某个查询用了多少TempDB空间
有关锁和内存使用的DMV
有关索引的DMV
有关查询和执行计划的DMV
DMV in SQL Server
浙公网安备 33010602011771号