SQL Server のチューニングについてまとめてみる - その12 - ( SQL Server のメモリ関係で必要なカウンタを抽出してみる ) - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

今回やることを説明してみる

　新年最初の記事はパフォーマンスカウンタです。(ちょっと地味ですね。)パフォーマンス計測で必要な SQL Server のパフォーマンスカウンタ ( sys.dm_os_performance_counters ) をまとめていきたいと思います。今回はメモリー関連です。

使用する環境を説明してみる

　久しぶりの Microsoft Azure です。特に意味ないですが、A7 マシン上の SQL Server 2014 を使用したいと思います。

メモリー関連のカウンタをまとめてみる

　メモリー関連のパフォーマンスカウンタは、かなりの数がありますので、その中でも大事なものをリストアップしたいと思います。(独断と偏見と知識不足がありますが...)

SQL Server: Buffer Manager オブジェクト

カウンタ名	説明
Buffer cache hit ratio	ディスクから読み取ることなく、バッファキャッシュから取得した割合です。バッファキャッシュのヒット率を計算するために、sys.dm_os_performance_counters の動的管理ビューを参照する必要があります。計算するには、この表の下に記述してあるクエリを実行する必要があります。※Buffer cache hit ratio base と合わせて使用する必要があります。
Buffer cache hit ratio base	ディスクから読み取ることなく、バッファキャッシュから取得した割合です。バッファキャッシュのヒット率を計算するために、sys.dm_os_performance_counters の動的管理ビューを参照する必要があります。計算するには、この表の下に記述してあるクエリを実行する必要があります。※Buffer cache hit ratio と合わせて使用する必要があります。
Checkpoint pages/sec	チェックポイントにより、またはすべてのダーティページをフラッシュする必要があるその他の操作により、ディスクにフラッシュされた 1 秒あたりのページ数を示します。
Database pages	データベースのバッファプール内のページ数です。
Free list stalls/sec	フリーリスト内のフリーページを待機する必要があった要求数です。
Lazy writes/sec	チェックポイント以外に、メモリが足りない場合に Lazy Writer のダーティページの書き込みが発生します。Checkpoint pages および Page writes のカウンタと合わせて監視を行う必要があります。
Page life expectancy	ページがバッファキャッシュ上に存在していた平均時間です。5～6分を目安にしたほうがよいです。この値が短い場合は、テーブルスキャンやバッチクエリなどを実行した可能性があります。
Page writes/sec	物理なページ書き込みの1秒あたりの回数です。
Target pages	理想的ページ数です。SQL Server 2008の頃は、Total Pages と比較していたような気がするのですが、2012では Total Pages が存在しません。

※バッファキャッシュヒット率算出クエリ

SELECT [バッファキャッシュヒット率] = ( opc.cntr_value * 1.0 / bcr.cntr_value ) * 100.0
FROM sys.dm_os_performance_counters opc
JOIN (SELECT cntr_value,
             object_name 
      FROM sys.dm_os_performance_counters  
      WHERE counter_name = 'Buffer cache hit ratio base'
      AND object_name LIKE '%Buffer Manager%') bcr 
ON  opc.OBJECT_NAME = bcr.OBJECT_NAME
WHERE opc.counter_name = 'Buffer cache hit ratio'
AND opc.OBJECT_NAME LIKE '%Buffer Manager%'

※バッファプール拡張関連のパフォーマンスカウンタは意図的に含めていません。(なぜならば、まだ自分で使う予定がないからです。(というか、試したこと無いのでわかりません。すいません...))

SQL Server、Memory Node

カウンタ名	説明
Database Node Memory (KB)	NUMA ノードでデータベースページに現在使用しているメモリ量です。
Free Node Memory (KB)	NUMA ノードの未使用のメモリ量です。
Foreign Node Memory (KB)	別NUMA ノードのメモリを使用している量です。この値が大きい場合、レスポンスが悪化する可能性があります。
Stolen Node Memory (KB)	NUMA ノードでデータベースページ以外に使用しているメモリ量です。
Target Node Memory (KB)	NUMA ノードの理想的なメモリ量です。(理想的がどの程度なのかはわかりません。)
Total Node Memory (KB)	サーバーがNUMAノードでコミットしているメモリの総容量を示します。

※Total Node Memory (KB)の計算方法は下記の通りです。

Total Node Memory (KB) = Database Node Memory (KB) + Free Node Memory (KB) + Stolen Node Memory (KB)

※SQL Server Management Studio から NUMA ノードの状態を確認することができます。
f:id:koogucc11:20150101132919p:plain
※下記のクエリを実行することで、NUMA Node 単位の Memory Node の状態を確認することができます。

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Memory Node%'

f:id:koogucc11:20150101134138p:plain
※instance = 000 が NUMANode0、instance = 001 が NUMANode1 に対応します。

SQL Server: Buffer Node

カウンタ名	説明
Database pages	NUMA ノード上のバッファプール内のページ数です。
Page life expectancy	MSDN を参照すると、最少時間となっています。Buffer Manager:Page life expectancy と同様に平均時間ではないのでしょうか？
Local node page lookups/sec	NUMAノード内からの参照要求の数です。
Remote node page lookups/sec	別NUMAノードへの参照要求の数です。この数が多い場合、レスポンス悪化の要因となります。

※下記のクエリを実行することで、NUMA Node 単位の Buffer Node の状態を確認することができます。

SELECT *
FROM sys.dm_os_performance_counters
WHERE object_name LIKE '%Buffer Node%'

f:id:koogucc11:20150101134316p:plain
※instance = 000 が NUMANode0、instance = 001 が NUMANode1 に対応します。

SQL Server の Access Methods オブジェクト

カウンタ名	説明
Full Scans/sec	1 秒あたりのフルスキャン(テーブルスキャン、インデックススキャンなど)の数です。回数が多いと、メモリ圧迫の要因になります。
Workfiles Created/sec	ハッシュデータを格納するファイルの1秒あたりの生成数です。この数が多い場合は、Memory Manger:Granted Workspace Memory が増加していないか確認します。このカウンタはTempDBのアクセス数にも用いることが多いですが、メモリも使用するためメモリ関連の評価にも用います。
Worktables Created/sec	ソート領域などを格納するテーブルの1秒あたりの生成数です。この数が多い場合は、Memory Manger:Granted Workspace Memory が増加していないか確認します。このカウンタはTempDBのアクセス数にも用いることが多いですが、メモリも使用するためメモリ関連の評価にも用います。

SQL Server: Memory Manager オブジェクト

カウンタ名	説明
Connection Memory (KB)	コネクションのためにサーバーが使用しているメモリの合計サイズです。
Database Cache Memory (KB)	データベースのページをキャッシュするのに使用しているメモリの量です。
Free Memory (KB)	使用されていないコミット済みのメモリの量です。
Granted Workspace Memory (KB)	ハッシュ操作、並べかえ、一括操作のコピー、インデックス作成などの操作を実行しているプロセスに割り当てられているメモリの合計サイズです。
Lock Memory (KB)	ロックのために使用しているメモリの合計サイズです。
Log Pool Memory (KB)	AlwaysOnでプライマリの更新をセカンダリに連携するために使用されているメモリ量です。
Memory Grants Pending	ワークスペースメモリ許可を待機しているプロセスの数です。メモリが枯渇している場合に現われます。
Optimizer Memory (KB)	クエリの最適化のために使用しているメモリの合計サイズです。
Reserved Server Memory (KB)	予約しているメモリの量です。
Stolen Server Memory (KB)	ページ以外に使用しているメモリの量です。
Target Server Memory (KB)	使用できるメモリの最適な量です。最適な量ってなんだろう...
Total Server Memory (KB)	使用しているメモリのサイズです。

SQL Server の Plan Cache オブジェクト

カウンタ名	説明
Cache Hit Ratio	プランキャッシュのヒット率です。キャッシュのヒット率を計算するために、sys.dm_os_performance_counters の動的管理ビューを参照する必要があります。計算するには、この表の下に記述してあるクエリを実行する必要があります。※Cache Hit Ratio Base と合わせて使用する必要があります。
Cache Hit Ratio Base	キャッシュのヒット率を計算するために、sys.dm_os_performance_counters の動的管理ビューを参照する必要があります。計算するには、この表の下に記述してあるクエリを実行する必要があります。※Cache Hit Ratio と合わせて使用する必要があります。
Cache Object Counts	キャッシュ内にあるキャッシュオブジェクトの数です。
Cache Pages	キャッシュオブジェクトによって使用されるページの数です。

※プランキャッシュヒット率算出クエリ

SELECT [プランキャッシュヒット率] = ( opc.cntr_value * 1.0 / bcr.cntr_value ) * 100.0
FROM sys.dm_os_performance_counters opc
JOIN (SELECT counter_name,
             cntr_value,
             object_name 
      FROM sys.dm_os_performance_counters  
      WHERE counter_name = 'Cache Hit Ratio Base'
      AND object_name LIKE '%Plan Cache%' 
	  AND instance_name = '_Total') bcr 
ON  opc.OBJECT_NAME = bcr.OBJECT_NAME
WHERE opc.counter_name = 'Cache Hit Ratio'
AND opc.OBJECT_NAME LIKE '%Plan Cache%'
    AND opc.instance_name = '_Total'