!!!Microsoft SQL Server 動的管理ビュー
{{category SQL Server,nolink}}動的管理ビュー(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 物理ストレージ容量
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|https://docs.microsoft.com/ja-jp/sql/azure-data-studio/download] の [Server Report|https://github.com/Microsoft/azuredatastudio/tree/master/samples/serverReports] という拡張機能で使用されている方法。

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