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;

  最も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

Microsoft SQL Server ストレージ容量

 SQL Server 物理ストレージ容量

VIEW SERVER STATE 権限が必要。データファイルが置かれるボリュームのみが対象。

-- 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;

 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;