本シリーズの最後の投稿として、D ドライブに配置したバッファプール拡張について触れておきたいと思います。
D シリーズを使用した際に、高速な D ドライブをバッファプール拡張 (BPE) として利用するための方法については、
Using SSDs in Azure VMs to store SQL Server TempDB and Buffer Pool Extensions
Mission Critical Performance with SQL Server 2014: (07) Buffer Pool Extension and Resource Governor for IO
Slow performance due to SQL Server 2014 Buffer Pool Extension (BPE) and serial queries (MAXDOP 1)
が参考になります。
バッファプール拡張ですが、Channel 9 の以下のスライドがわかりやすいです。
従来のメモリ上に載っておりデータは「Hot Pages」として利用され、即時にアクセスできる状態となっています。
バッファプール拡張に載っているデータは「Warm Pages」の状態となっており、高速なディスクにバッファプール拡張が設定されていれば、すぐに「Hot Pages」として移すことができる状態となっています。
Warm Pages についてはメモリ上に載っていませんので、Warm Pages → Hot Pages に移行するための読み込みの分は処理のオーバーヘッドがありますが、データベースが格納されているディスクより高速なディスクに確保されていれば、データファイルを格納しているディスクより短時間で読み込むことができます。
よくある説明としては上記のようになっているかと思いますが、BPE を使用する際には、BPE に対しての I/O 単位を十分に理解しておく必要があります。
まずは、以下の SQL でバッファプール拡張を有効にします。
ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION ON (FILENAME = 'D:\SSDCACHE\EXTENSION.BPE', SIZE = 100 GB)
この状態で Max Server Memory を 1GB に設定して 1.5GB のテーブルのデータを検索してみます。
Hot Pages → Warm Pages （BPE) への書き込みについては最大で 256KB で I/O が行われるようで、書き込みについてはこのブロックサイズまでの書き込みは考慮しておく必要があります。
読み込みについては、8KB 単位で行われます。つまりページ単位で BPE から読み込みが行われていることになります。
先行読み取り (Readahead) については、最大で 256KB で行われていますので、読み込みの速度によっては BPE の 8KB 読み込みより、ディスクから 256KB 読み込みをしたほうが早いケースがあります。
# 動作を見ていると BPE からの読み込み、この辺を考慮して、どちらから読んだほうがよいかを考慮していそうですが。
BPE を使用して効果が出るのは、
- 「OLTP における 8KB 単位のランダムリード」
が適しているかと思います。
BI 系の
- 「大量のデータ読み込み (大量のシーケンシャルリード) が発生する」
するようなワークロードには適していない (設定をすることで逆にレスポンスが悪くなる可能性がある) と私は考えています。
BPE による読み込みが発生した場合、待ち事象としては「EC」 (Extension Cache ですかね) が発生するようで、この待ち事象を確認することで、読み込みの状況を確認することができます。
この辺は PAGEIOLATCH と EC の比率を見て、BPE の効果を判断するというような使い方もできるのではないでしょうか。
次に BPE の情報を確認してみたいと思います。BEP の使用状況はパフォーマンスモニター / 拡張イベント から確認することができますが、本投稿では情報が少ない、sys.dm_os_buffer_descriptors での確認について触れてみたいとおもます。
SQL Server 2014 の sys.dm_os_buffer_descriptors は以前のバージョンから拡張が行われており、「is_in_bpool_extension」 が追加されています。
この DMV の情報を使用することで、以下のような情報を取得することができます。
is_in_bpool_extension が 「1」の列が BPE が使用されている列になりますが、ここで 1 点注意することがあります。
BPE が 1 のデータがすべて BPE 上に確保されているのではなく、
- is_in_bpool_extension = 1 かつ page_type = NULL
のものが BPE 上に格納されている Warm Pages となるようです。
is_in_bpool_extension が 1 でも page_type が NULL 以外のものについてはメモリ上に Hot Pages としてロードされているようです。
# is_in_bpool_extension が 1 で page_type が NULL 以外のものは BPE から読み込まれた Hot Pages のデータとなるかと
DBCC PAGE などで page_type が NULL になっているページをピンポイントで読み込むと NULL から page_type が付与されることが確認できます。
# DBCC PAGE で読み込まれたデータは通常のデータアクセス同様メモリ上にキャッシュされますので、通常のアクセスと変わりはないはずです。
それでは上記の状態からバッファキャッシュをクリアせずにもう一度検索をしてみます。
実行前と比較して、in_bpool_extension が 1 のデータの傾向が変わっていることが確認できます。
また、NULL の領域も増加しています。
BPE ですが、大量のアクセスをさせた場合、バッファプールからすべてのデータを BPE に退避するというような動作はしていないようで、負荷状況によって BPE に載るデータサイズは変わってくるようです。
# BPE に Warm Pages として格納するオーバーヘッドと、Cold Pages としてキャッシュアウトさせるコストを比較しているのかと思いますが。
このことから、BPE を使用する場合には、
- D ドライブの高速なディスクを確実にバッファプールの L2 キャッシュとして使用することは難しい
- BPE は 8KB I/O による読み込みのため、I/O パターンによってはディスクから読み込んだほうが高速
- バッファプールから BPE の書き込みは最大で 256KB のブロックサイズで行われているため、このブロックサイズの書き込み速度を考慮
- 大量のデータを読み込むワークロードではなく、ランダムアクセスされるワークロードに適している
- 設定することによるオーバーヘッドを考慮する
ということが重要となってくるかと。