リソースガバナーのリソースプールの構成と状態情報を出力する：SQL Server動的管理ビューレファレンス（150）

「Microsoft SQL Server」が稼働するデータベースシステムを運用する管理者に向け、「動的管理ビュー」の活用を軸にしたトラブル対策のためのノウハウを紹介していきます。今回は、リソースガバナーのリソースプールの構成と状態情報の出力について解説します。

[伊東敏章，＠IT]

SQL Server動的管理ビュー一覧

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で使用可能な動的管理ビューについて、動作概要や出力内容などを紹介していきます。今回は動的管理ビュー「sys.dm_resource_governor_resource_pools」における、リソースガバナーのリソースプールの構成と状態情報の出力について解説します。対応バージョンは、SQL Server（サポートされている全てのバージョン）、「Azure SQL Database」「Azure SQL Managed Instance」「Azure Synapse Analytics」「Analytics Platform System」（PDW）です。

概要

　SQL Serverでは、リソースガバナーの機能を使用してワークロードが使用するシステムリソースの消費を管理することができます。

　リソースガバナーでは、アプリケーションからの要求をワークロードグループに割り当てることで、要求が使用できるCPU時間やメモリ量に対しての制限を設定したり、ワークロードグループが属するリソースプールに、使用できるCPU時間やメモリ量の割合、I/O要求のスループットの制限を設定できます。

　「sys.dm_resource_governor_resource_pools」動的管理ビューでは、リソースプールの構成や現在の状態、統計に関する情報を出力することが可能です。

• sys.dm_resource_governor_resource_pools（Transact-sql）

出力内容

列名	データ型	説明
pool_id	int	リソースプールのID
name	sysname	共有リソースの名前
statistics_start_time	datetime	このプールの統計がリセットされた時刻
total_cpu_usage_ms	bigint	リソースガバナー統計がリセットされた後の累積CPU使用時間（ミリ秒単位）
cache_memory_kb	bigint	現在の合計キャッシュメモリ使用量（KB単位）
compile_memory_kb	bigint	コンパイルと最適化に現在使用されているメモリ量（stolen）の合計（KB単位） 大半はコンパイルと最適化に使用されるが、他のメモリユーザーに使用されている可能性もある
used_memgrant_kb	bigint	メモリ許可で現在使用されているメモリ量（stolen）の合計（KB単位）
total_memgrant_count	bigint	このリソースプール内のメモリ許可の累積数
total_memgrant_timeout_count	bigint	このリソースプールにおけるメモリ許可のタイムアウトの累積数
active_memgrant_count	int	現在のメモリ許可の数
active_memgrant_kb	bigint	現在のメモリ許可の合計（KB単位）
memgrant_waiter_count	int	メモリ許可を現在保留中のクエリの数
max_memory_kb	bigint	リソースプールが持つことができるメモリの最大量（KB単位） 現在の設定とサーバの状態に基づく
used_memory_kb	bigint	リソースプールに使用されるメモリの量（KB単位）
target_memory_kb	bigint	リソースプールが達成しようとしている目標メモリ量（KB単位） 現在の設定とサーバの状態に基づく
out_of_memory_count	bigint	リソースガバナーまたは統計がリセットされた後にプールで失敗したメモリ割り当ての数
min_cpu_percent	int	CPUの競合がある場合に、リソースプール内の全ての要求に対して保証される平均CPU帯域幅の現在の構成
max_cpu_percent	int	CPUの競合がある場合に、リソースプールの全ての要求で許容される最大平均CPU帯域幅の現在の構成
min_memory_percent	int	メモリの競合がある場合に、リソースプール内の全ての要求に対して保証されるメモリの量の現在の構成
max_memory_percent	int	このリソースプール内の要求で使用できる合計サーバメモリの割合の現在の構成
cap_cpu_percent	int	※SQL Server 2012（11.x）以降のみ リソースプール内の全ての要求が受信するCPU帯域幅のハードキャップ。CPU帯域幅の最大レベルを指定されたレベルに制限する 1〜100の範囲の値
min_iops_per_volume	int	※SQL Server 2014（12.x）以降のみ このプールのディスクボリューム設定ごとの1秒当たりの最小I/O（IOPS） リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
max_iops_per_volume	int	※SQL Server 2014（12.x）以降のみ このプールのディスクボリューム設定ごとの1秒当たりの最大I/O数（IOPS） リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
read_io_queued_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナーがリセットされた後にエンキューされた読み取りI/Oの合計 リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
read_io_issued_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に発行された読み取りI/Oの合計 リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
read_io_completed_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に完了した読み取りI/Oの合計
read_io_throttled_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に調整された読み取りI/Oの合計 リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
read_bytes_total	bigint	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に読み取られたバイト数の合計
read_io_stall_total_ms	bigint	※SQL Server 2014（12.x）以降のみ 読み取りI/Oの到着から完了までの合計時間（ミリ秒単位）
read_io_stall_queued_ms	bigint	※SQL Server 2014（12.x）以降のみ 読み取りI/Oの到着から発行までの合計時間（ミリ秒単位） リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
write_io_queued_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後にエンキューされた書き込みI/Oの合計 リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
write_io_issued_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に発行された書き込みI/Oの合計 リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
write_io_completed_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に完了した書き込みI/Oの合計
write_io_throttled_total	int	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に調整された書き込みI/Oの合計
write_bytes_total	bigint	※SQL Server 2014（12.x）以降のみ リソースガバナー統計がリセットされた後に書き込まれたバイト数の合計
write_io_stall_total_ms	bigint	※SQL Server 2014（12.x）以降のみ 書き込みI/Oの到着から完了までの合計時間（ミリ秒単位）
write_io_stall_queued_ms	bigint	※SQL Server 2014（12.x）以降のみ 書き込みI/Oの到着から発行までの合計時間（ミリ秒単位） リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
io_issue_violations_total	int	※SQL Server 2014（12.x）以降のみ I/O発行違反の合計。I/Oの問題の発生率が予約されたレートを下回った回数 リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
io_issue_delay_total_ms	bigint	※SQL Server 2014（12.x）以降のみ スケジュールされた発行からI/Oの実際の発行までの合計時間（ミリ秒単位） リソースプールのMIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEの設定が「0」の場合はNULL
pdw_node_id	int	※Azure Synapse Analytics、Parallel Data Warehouseのみ このディストリビューションが配置されているノードの識別子

動作例

　リソースガバナーは既定の構成では無効のため、リソースガバナーを有効化する前に「sys.dm_resource_governor_resource_pools」動的管理ビューを出力してみます（図1）。

図1　リソースガバナーを有効化する前に「sys.dm_resource_governor_resource_pools」動的管理ビューを出力したところ

　既定で構成されている「internal」と「default」リソースプールの情報が出力されました。

　次にリソースガバナーを構成し、リソースガバナーを有効化した後に「sys.dm_resource_governor_resource_pools」動的管理ビューを出力しました（図2）。

図2　リソースガバナーを有効化して「sys.dm_resource_governor_resource_pools」動的管理ビューを出力したところ

　リソースガバナーには2つのリソースプールを追加して、作成した2つのリソースプールにはI/O調整を有効化するため、MIN_IOPS_PER_VOLUMEとMAX_IOPS_PER_VOLUMEオプションを構成しました。

　出力内容から作成した2つのリソースプールについて、MIN_CPU_PERCENTやMIN_IOPS_PER_VOLUMEオプションなどに構成した値を確認することができます。

　次に、実際に多数のクエリを実行しながら「sys.dm_resource_governor_resource_pools」動的管理ビューを出力しました（図3）。

図3　多数のクエリを実行しながら「sys.dm_resource_governor_resource_pools」動的管理ビューを出力したところ

　実行したクエリは全て、追加したワークロードグループに割り当てられ、一方のリソースプールに割り当てられる構成となっています。

　出力内容の「active_memgrant_count」列からメモリ許可が要求されていることや、「read_io_throttled_total」列から読み取り操作でI/O調整が実施されていることなどを確認できました。

※本Tipsは、「Windows Server 2019」上に「SQL Server 2019」をインストールした環境を想定して解説しています。

筆者紹介

椎名 武史（しいな たけし）

BIPROGY株式会社（ビプロジー）所属。Microsoft MVP for Data Platform（2017〜）。入社以来 SQL Serverの評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。SQL Serverのトラブル対応で社長賞の表彰を受けた経験も持つ。休日は学生時代の仲間と市民駅伝に参加し、銭湯で汗を流してから飲み会へと流れる。

伊東 敏章（いとう としあき）

BIPROGY株式会社（ビプロジー）所属。入社以来SQL Server一筋で評価／設計／構築／教育などに携わりながらも、主にサポート業務に従事。社内のプログラミングコンテストで4回の優勝経験も持つ。趣味は輪行で週末は自転車を持っての旅行。目標は色々な日本百選を制覇すること。