MSSQL/DMV

Microsoft SQL Server 動的管理ビュー

動的管理ビュー(Dynamic Management View) のメモ

動的管理ビュー（SQL Server） | DB & SQL 技術ブログ
- https://www.dbsheetclient.jp/blog/?p=1879

不足していると思われるインデックス

---- 不足していると思われるインデックス
-- この結果からインデックスを作成する場合は、
-- まず最初に、[equality_columns（等値述語に使用できる列）]を指定し、
-- その次に、[inequality_columns（不等値述語に使用できる列）]を指定し、
-- INCLUDE句に[included_columns（包括列として必要な列）]を指定する
SELECT
  gs.avg_user_impact AS [予測されるクエリパフォーマンス改善率]
, gs.avg_total_user_cost AS [削減できたクエリの平均コスト]
, gs.last_user_seek AS [最後にシークした時間]
, id.statement AS [テーブル名]
, id.equality_columns AS [等値述語に使用できる列]
, id.inequality_columns AS [不等値述語に使用できる列]
, id.included_columns AS [包括列として必要な列]
, gs.unique_compiles AS [コンパイルおよび再コンパイルの数]
, gs.user_seeks AS [クエリによって発生したシーク数]
FROM sys.dm_db_missing_index_group_stats AS gs
JOIN sys.dm_db_missing_index_groups AS ig
ON gs.group_handle = ig.index_group_handle
JOIN sys.dm_db_missing_index_details AS id
ON ig.index_handle = id.index_handle
-- WHERE id.[database_id]=DB_ID('db名')
-- コメントをはずすと指定したデータベースでの抽出となる
--Order By gs.avg_user_impact DESC;
order by id.statement, gs.avg_user_impact DESC;

インデックス使用状況

---- インデックス使用状況
-- updates は、INS/UPD/DEL操作によって更新された回数
-- lookups は、検索後にペーステーブルの参照数(Key Lookup, RID Lookup)
-- これが高い場合は、インデックスへの列追加（INCLUDE）を検討し、I/O負荷を軽減を検討。
-- need VIEW SERVER STATE 権限
SELECT
  SCHEMA_NAME(o.schema_id) AS [スキーマ名]
, o.name AS [テーブル名]
, i.name AS [インデックス名]
, s.user_seeks    AS [シーク数]
, s.user_scans    AS [スキャン数]
, s.user_lookups  AS [参照数]
, s.user_updates  AS [更新数]
, s.last_user_seek   AS [シーク時刻]
, s.last_user_scan   AS [スキャン時刻]
, s.last_user_lookup AS [参照時刻]
, s.last_user_update AS [更新時刻]
FROM sys.objects o 
JOIN sys.indexes i 
ON i.object_id = o.object_id 
JOIN sys.dm_db_index_usage_stats s 
ON s.index_id = i.index_id AND s.object_id = i.object_id 
WHERE o.type = 'U' AND s.database_id = DB_ID()
--AND o.name = 'テーブル名'
--AND i.name = 'インデックス名'
ORDER BY 
s.user_seeks+s.user_scans desc -- seek+scan回数降順
--s.user_seeks desc -- seek回数降順
--s.user_scans desc -- scan回数降順

最もCPUを多く使っているクエリー

-- 最もCPUを多く使っているクエリー
SELECT TOP 20
  total_worker_time/execution_count AS avg_cpu_cost
, execution_count
, SUBSTRING(st.text, (qs.statement_start_offset/2)+1,  
    ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)
  AS query_text
, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY avg_cpu_cost DESC;

最も多くのI/Oを発生させているクエリ

-- 最も多くのI/Oを発生させているクエリ
SELECT TOP 20
  (qs.total_logical_reads/qs.execution_count) AS avg_logical_reads
, (qs.total_logical_writes/qs.execution_count) AS avg_logical_writes
, (qs.total_physical_reads/qs.execution_count) AS avg_phys_reads
, execution_count
, SUBSTRING(st.text, (qs.statement_start_offset/2)+1,  
    ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
      ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1)
  AS query_text
, query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY (total_logical_reads + total_logical_writes) DESC;

SQL Server テーブルごとの行数とサイズ

-- SQL Server テーブルごとの行数とサイズ
DECLARE @TAB TABLE (
  [name] SYSNAME,[rows] BIGINT,[reserved] VARCHAR(25),
  [data] VARCHAR(25),[index] VARCHAR(25),[unused] VARCHAR(25));
INSERT INTO @TAB
EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'";
SELECT [name],[rows]
,CAST(REPLACE([reserved],' KB','') AS INT) [reserved (KB)]
,CAST(REPLACE([data],' KB','') AS INT) [data (KB)]
,CAST(REPLACE([index],' KB','') AS INT) [index (KB)]
,CAST(REPLACE([unused],' KB','') AS INT) [unused (KB)]
FROM @TAB WHERE [name]<>'sysdiagrams' ORDER BY [name]
GO

テーブルの概算データ件数・データサイズの取得

-- テーブルの概算データ件数・データサイズの取得
SELECT DB_NAME() AS DbName, SCHEMA_NAME(obj.schema_id) AS SchemaName, obj.name AS TableName, dps.row_count
--
, dps.reserved_page_count, dps.reserved_page_count * 8.0 AS reserved_page_kb
, dps.used_page_count, dps.used_page_count * 8.0 AS used_page_kb
--
, dps.in_row_reserved_page_count, dps.in_row_reserved_page_count * 8.0 AS row_reserved_page_kb
, dps.in_row_used_page_count, dps.in_row_used_page_count * 8.0 AS row_used_page_kb
, dps.in_row_data_page_count, dps.in_row_data_page_count * 8.0 AS row_data_page_kb
, dps.lob_reserved_page_count, dps.lob_reserved_page_count * 8.0 AS lob_reserved_page_kb
, dps.lob_used_page_count, dps.lob_used_page_count * 8.0 AS lob_used_page_kb
FROM sys.dm_db_partition_stats dps JOIN sys.objects obj 
ON(obj.object_id = dps.object_id AND SCHEMA_NAME(obj.schema_id) NOT IN('sys','cdc'))
WHERE dps.index_id IN(0, 1)
ORDER BY obj.name
OPTION (RECOMPILE)

Microsoft SQL Server ストレージ容量

SQL Server 物理ストレージ空き容量

各固定ドライブの空き容量をMB単位で返すストアドプロシージャ。空き容量しか取得できないため、割合などは計算できない。※ドキュメントに記載のないストアド

EXEC master.dbo.xp_fixeddrives;

SQL Server 2008 R2 以降で、指定されたデータベースとファイルが格納されたボリュームの情報を返す sys.dm_os_volume_stats 。VIEW SERVER STATE 権限が必要。また、sys.master_files を参照する場合 VIEW ANY DEFINITION もしくは CREATE DATABASE, ALTER ANY DATABASE 権限が必要。

-- SQL Server 物理ストレージ容量
-- need VIEW SERVER STATE 権限
SELECT DISTINCT @@SERVERNAME AS [SERVER NAME] 
, vs.volume_mount_point AS [Mount Point] 
, vs.file_system_type AS [File System] 
, vs.total_bytes / 1024 / 1024 AS [Total (MiB)]
, vs.available_bytes / 1024 / 1024 AS [Available (MiB)]
FROM sys.master_files AS mf 
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs 
ORDER BY vs.volume_mount_point;

-- 現在のデータベース のみの場合
FROM sys.database_files AS mf 
CROSS APPLY sys.dm_os_volume_stats(DB_ID(), mf.file_id) AS vs

sys.dm_os_volume_stats (Transact-SQL) - SQL Server | Microsoft Learn
- https://learn.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-os-volume-stats-transact-sql

SQL Server データベース単位の容量

VIEW SERVER STATE 権限が必要。EXEC sp_spaceused + ログサイズ相当の内容

-- SQL Server データベース単位の容量
-- need VIEW SERVER STATE 権限
CREATE TABLE #database_usage ( 
	database_id		smallint, 
	database_name	nvarchar(128), 
	total_bytes		bigint, 
	used_bytes		bigint, 
	reserved_bytes	bigint, 
	data_bytes		bigint, 
	index_bytes		bigint, 
	total_log_bytes	bigint, 
	used_log_bytes	bigint, 
	PRIMARY KEY (database_id) 
);

EXEC sp_MSforeachdb 'USE ?; 
INSERT INTO #database_usage 
SELECT DB_ID(), DB_NAME() 
, F.total_bytes AS total_bytes 
, F.allocated_bytes AS used_bytes 
, CAST(R.total_pages AS bigint) * 8192 AS reserved_bytes 
, CAST(R.data_pages  AS bigint) * 8192 AS data_bytes 
, CAST(used_pages  - data_pages AS bigint) * 8192 AS index_bytes 
, L.total_log_size_in_bytes AS total_log_bytes 
, L.used_log_space_in_bytes AS used_log_bytes 
FROM ( 
	SELECT SUM(a.total_pages) AS total_pages, SUM(a.used_pages) AS used_pages, SUM(CASE 
	WHEN it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) THEN 0 
	WHEN a.type <> 1 and p.index_id < 2 THEN a.used_pages 
	WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END) AS data_pages 
	FROM sys.partitions p 
	JOIN sys.allocation_units a ON p.partition_id = a.container_id 
	LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 
) R, ( 
	SELECT database_id 
	, SUM(CAST(total_page_count AS BIGINT)) * 8192 AS total_bytes 
	, SUM(CAST(allocated_extent_page_count AS BIGINT)) * 8192 AS allocated_bytes 
	, SUM(CAST(unallocated_extent_page_count AS BIGINT)) * 8192 AS unallocated_bytes 
	FROM sys.dm_db_file_space_usage GROUP BY database_id 
) F, sys.dm_db_log_space_usage AS L
';

--SELECT @@SERVERNAME AS SERVERNAME, * FROM #database_usage ORDER BY database_id;
SELECT @@SERVERNAME AS [SERVER NAME] 
, database_id AS [DB ID], database_name AS [DB NAME]
, total_bytes / 1024 / 1024 AS [Total (MiB)]
, used_bytes / 1024 / 1024 AS [Used (MiB)]
, reserved_bytes / 1024 / 1024 AS [ROWS Reserved (MiB)]
, data_bytes / 1024 / 1024 AS [ROWS data (MiB)]
, index_bytes / 1024 / 1024 AS [ROWS index (MiB)]
, total_log_bytes / 1024 / 1024 AS [LOG Total (MiB)]
, used_log_bytes / 1024 / 1024 AS [LOG Used (MiB)]
FROM #database_usage 
WHERE database_name not in ('master','msdb','model','tempdb')
ORDER BY database_id;

DROP TABLE #database_usage;

SQL Server データベースファイルの容量

-- SQL Server データベース ファイルの容量
CREATE TABLE #database_usage ( 
	db_id		smallint, 
	db_name		nvarchar(128), 
	file_id		smallint, 
	file_name	nvarchar(128), 
	type		smallint, 
	type_desc	nvarchar(64), 
	state		smallint, 
	state_desc	nvarchar(64), 
	max_bytes	bigint, 
	total_bytes	bigint, 
	used_bytes	bigint, 
	growth		int, 
	is_percent_growth	bit, 
	PRIMARY KEY (db_id, file_id) 
);

EXEC sp_MSforeachdb 'USE ?; 
INSERT INTO #database_usage 
SELECT DB_ID() AS [db_id], DB_NAME() AS [db_name] 
, file_id, name AS [file_name], type, type_desc, state, state_desc 
, CASE WHEN max_size<0 THEN -1 ELSE CAST(max_size AS BIGINT) * 8192 END AS [max_bytes] 
, CAST(size AS BIGINT) * 8192 AS [total_bytes] 
, CAST(FILEPROPERTY(name,''SpaceUsed'') AS BIGINT) * 8192 AS [used_bytes] 
, growth, is_percent_growth 
FROM sys.database_files
';

--SELECT @@SERVERNAME AS SERVERNAME, * FROM #database_usage ORDER BY db_id, type, file_id;
SELECT @@SERVERNAME AS [SERVER NAME] 
, db_id AS [DB ID], db_name AS [DB NAME]
, file_id AS [file id], file_name AS [file name]
, /*type AS [type],*/ type_desc AS [Types]
, /*state AS [state],*/ state_desc AS [State]
, total_bytes / 1024 / 1024 AS [Total (MiB)]
, used_bytes / 1024 / 1024 AS [Used (MiB)]
, CASE WHEN is_percent_growth = 1 THEN CAST(growth AS varchar)+' %' 
  ELSE CAST(growth * 8 / 1024 AS varchar)+' MB' END AS [自動拡張]
, CASE WHEN max_bytes<0 THEN N'無制限' 
  WHEN max_bytes=2199023255552 THEN N'2 TiB'
  ELSE CAST(max_bytes / 1024 / 1024 AS varchar)+' MB' END [最大サイズ]
FROM #database_usage 
WHERE db_name not in ('master','msdb','model','tempdb') 
ORDER BY db_id, type, file_id;

DROP TABLE #database_usage;

Microsoft SQL Server CPU使用率

SQL Server CPU使用率

リングバッファーの情報を参照し、過去数時間(1分間隔?)の CPU の使用率を取得する。また、sys.dm_os_sys_info を参照するため VIEW SERVER STATE 権限が必要。

Azure Data Studio の Server Report という拡張機能で使用されている方法。

-- https://github.com/microsoft/azuredatastudio/blob/master/samples/serverReports/src/sql/cpumetric.sql

-- source: https://sqlserverperformance.wordpress.com/2009/07/30/how-to-get-sql-server-cpu-utilization-from-a-query/
-- Use for a demo/sample purpose only. This query is not built-in to any product.

-- need VIEW SERVER STATE 権限
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info); 

SELECT Top(30) 'CPU%' as [label]
, DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
, SQLProcessUtilization AS [SQL Server Process CPU Utilization]
, SystemIdle AS [System Idle Process]
, 100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization]
FROM ( 
  SELECT [timestamp], 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] 
  FROM (
    SELECT [timestamp], convert(xml, record) AS [record] 
    FROM sys.dm_os_ring_buffers 
    WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
    AND record LIKE '%<SystemHealth>%'
  ) AS x 
) AS y 
--ORDER BY record_id DESC;
ORDER BY [Event Time] DESC;

最終更新時間：2026年04月23日 16時57分56秒　指摘や意見などあればSandBoxのBBSへ。

INDEX