バッファー プール スキャンをトリガーする操作は、大容量メモリ コンピューターで実行速度が低下する可能性があります

この記事では、SQL Server バッファー プールのスキャンが大きなメモリ コンピューターで完了するまでに長い時間がかかる可能性がある方法について説明します。

適用対象: SQL Server
元の KB 番号: 4566579

現象

Microsoft の特定の操作SQL Server、バッファー プール (データベース ページをメモリに格納するキャッシュ) のスキャンをトリガーします。 RAM が大量 (1 TB 以上) のシステムでは、バッファー プールのスキャンに時間がかかる場合があります。 これにより、スキャンをトリガーした操作の速度が低下します。

バッファー プール スキャンの原因となる操作

バッファー プール スキャンをトリガーする可能性がある操作を次に示します。

• データベースの起動
• データベースのシャットダウンまたは再起動
• AG フェールオーバー
• データベースの削除 (ドロップ)
• データベースからのファイルの削除
• データベースの完全バックアップまたは差分バックアップ
• データベースの復元
• トランザクション ログの復元
• オンライン復元
• DBCC CHECKDB または DBCC CHECKTABLE 操作

エラー ログは、スキャンに長い時間がかかったことを示しています

SQL Server 2016 SP3、SQL Server 2017 CU23、SQL Server 2019 CU9 以降、バッファー プール スキャンに長い時間 (10 秒以上) かかったことを示すエラー メッセージが SQL Server エラー ログに追加されました。

バッファー プール スキャンには 14 秒かかりました。データベース ID 7、コマンド 'BACKUP DATABASE'、操作 'FlushCache'、スキャンされたバッファー 115、反復処理されたバッファーの合計204640239、待機時間 0 ミリ秒。 詳細については、'https://go.microsoft.com/fwlink/?linkid=2132602' を参照してください。

長いスキャンを診断するための拡張イベント

また、2016 SP3 SQL Server同じビルド以降、SQL Server 2017 CU23 と SQL Server 2019 CU9 では、長いバッファー プール スキャンを識別するために、buffer_pool_scan_complete Extended イベントが導入されました。

スキャンに 1 秒を超える時間がかかる場合、イベントが有効になると、XEvent が次のように記録されます。

name	database_id	elapsed_time_ms	コマンド	操作	scanned_buffers	total_iterated_buffers
buffer_pool_scan_complete	7	1308	BACKUP DATABASE	FlushCache	243	19932814

注:

しきい値は XEvent のサイズが小さいので、より細かい粒度で情報をキャプチャできます。

回避策

2022 年SQL Server以前は、この問題を解決する方法はありませんでした。 バッファー プールからバッファー (DBCC DROPCLEANBUFFERS) クリーン を削除するとパフォーマンスが大幅に低下する可能性があるため、バッファー プールをクリアする操作は実行しないことをお勧めします。 メモリからデータベース ページを削除すると、後続のクエリ実行でディスク上のデータベース ファイルからデータが再読み取りされます。 ディスク I/O を介してデータにアクセスするこのプロセスでは、クエリの速度が低下します。

SQL Server 2022 では、バッファー プール スキャンは複数のコアを利用して並列化されるため、この問題は軽減されます。 800 万バッファー (64 GB) ごとに 1 つのタスクがあり、800 万未満のバッファーがある場合でもシリアル スキャンが使用されます。 詳細については、「バッファー プールの並列スキャン」をwatch。

詳細

大きなバッファー プールで発生する可能性がある問題の詳細については、「SQL Server: 大きな RAM と DB のチェックポイント処理」を参照してください。