都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

SQL Server のチューニングについてまとめてみる - その12 - ( SQL Server のメモリ関係で必要なカウンタを抽出してみる )

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

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

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

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

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

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

カウンタ名 説明
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%'

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

カウンタ名 説明
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 に対応します。

カウンタ名 説明
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 に対応します。

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

※それぞれのプランを出すにはもう少し工夫が必要ですね。(と、手を抜いてみる。)

おわりになにか言ってみる

 パフォーマンスカウンタのまとめとかやってるときいつも思うんですが、パフォーマンスカウンタ同士の関連図ほしいですよね。※A7マシンをシャットダウン忘れて、起動しっぱなしにしていたため、2,000円くらいもってかれました....