SQL Serverのメモリ最適化テーブルが引き起こすメモリプレッシャーアラートの対処法

Microsoft SQL Serverはメモリ管理に非常に優れたデータベースエンジンですが、環境によってはメモリプレッシャーのアラートが発生し、データベースエンジンが必要なメモリを確保できず、エラーにつながる場合があります。

はじめに

本記事では、SQL Server® 2019(Enterprise Edition)上のメモリ最適化テーブル(In-Memory OLTP)に起因するメモリプレッシャーによって発生する特異な障害シナリオの解決方法を解説します。ここで紹介する手順は、SQL Server 2014以降のバージョンでも同様に適用できます。

画面には次のようなエラーメッセージが表示されることがあります。

Message: MSSQL on Windows: Stolen Server Memory is too high
Source: XXXXX\MSSQLSERVER Path: Not Present Alert
description: SQL instance "MSSQLSERVER" Stolen Server Memory on
computer "XXXXXXX.XXX.com" is too high.

Message: SQL Server Alert System: 'Severity 17' occurred on \\XXXXXXX
DESCRIPTION:   There is insufficient system memory in resource pool 'internal'
to run this query.

Message: Disallowing page allocations for database 'InMemoryDB' due to
insufficient memory in the resource pool 'default'. See
'https://go.microsoft.com/fwlink/?LinkId=510837' for more information.

Message: XTP failed page allocation due to memory pressure: FAIL_PAGE_ALLOCATION 32

解決策

以下の手順に従って、トラブルシューティングと問題の解決を行います。

ステップ1:バッファプールのメモリ消費量を確認する

まず、SQLバッファプールにおけるメモリ消費状況を確認します。

上記の画像のとおり、問題となっているデータベースInMemoryDBが消費しているのは、バッファプール全体のわずか0.017%です。

ステップ2:OSメモリ クークを確認する

続いて、次のT-SQLコマンドでOSメモリ クーク(memory clerks)を確認します。

select * from sys.dm_os_memory_clerks order by pages_kb desc

結果を見ると、上位の主要なメモリ消費元の合計は、最大サーバーメモリ全体の約80%を占めていました。

また、メモリ最適化テーブルのサイズも2GB未満であり、前述の画像ではDB_ID_6という名前で確認できます。そのため、本来であればサーバーにメモリプレッシャーが発生しているはずはありません。

ステップ3:リソースプールへのデータベースのバインド

エラーログに記載されていたOOM(Out of Memory)関連のリンク(https://go.microsoft.com/fwlink/?LinkId=510837)を確認した結果、メモリ最適化テーブルを含むデータベースをリソースプールにバインドする必要があることがわかりました。このバインドは、メモリ最適化テーブルを持つデータベースにとってのベストプラクティスです。以下の手順で、リソースガバナーにリソースプールを作成し、データベースをバインドします。

ベストプラクティスでは、1つ以上のメモリ最適化テーブルによってSQL Serverのリソースが占有される事態を防ぐとともに、逆に他のメモリ使用者がメモリ最適化テーブルに必要なメモリを奪ってしまうこと防ぐことが推奨されています。そのため、メモリ最適化テーブルを持つデータベースのメモリ消費を管理するために、専用のリソースプールを作成してください。

データベースをリソースプールに追加する際は、以下の点に注意してください。

• 1つのデータベースをバインドできるリソースプールは1つだけです。
• 複数のデータベースを同じプールにバインドすることは可能です。
• SQL Serverでは、メモリ最適化テーブルを持たないデータベースもリソースプールにバインドできますが、効果はありません。
• データベースをリソースプールにバインドした後でも、そのデータベース内にメモリ最適化テーブルを作成できます。

リソースプールへのバインド手順

1. メモリ割り当てを指定してリソースプールを作成します。

   USE [master]
   GO
   
   CREATE RESOURCE POOL [Admin_Pool] WITH(min_cpu_percent=0, 
      max_cpu_percent=100, 
      min_memory_percent=15, 
      max_memory_percent=15, 
      cap_cpu_percent=100, 
      AFFINITY SCHEDULER = AUTO,
      min_iops_per_volume=0,
      max_iops_per_volume=0)
   GO
   

   注:メモリ不足(OOM)状態を回避するため、min_memory_percentとmax_memory_percentは必ず同じ値に設定してください。

   今回のケースでは、メモリ最適化テーブルが非常に小規模であったため、サーバー総メモリの15%をリソースプールに割り当てました。ご自身の環境に応じたメモリ割合については、参考リンクを活用して計算することを忘れないでください。

2. リソースプールを確認し、データベースをバインドします。

   EXEC sp_xtp_bind_db_resource_pool 'InMemoryDB', 'Admin_Pool'  
   GO
   

3. sys.databasesカタログビューでバインドを確認します。

   SELECT d.database_id, d.name, d.resource_pool_id  
   FROM sys.databases d
   GO
   

   

4. バインドを有効化するため、データベースを再起動します。

   ALTER DATABASE DB_Name SET OFFLINE  
   GO  
   ALTER DATABASE DB_Name SET ONLINE  
   GO  
   

注:Always On可用性グループを構成している場合は、両方のノードで同じ手順を実行し、手順4(データベースの再起動)の代わりに、セカンダリインスタンスへのデータベースフェイルオーバーを実行してください。

まとめ

本ケースでは、メモリ最適化テーブルを含むデータベースをリソースプールに追加したところ、メモリプレッシャーに関連するすべてのアラートが収まりました。この問題について数週間にわたりSQL Serverのエラーログを監視し続けましたが、メモリプレッシャーの痕跡は一切見られませんでした。これらの手順により、最小限のダウンタイムでデータベースエンジンレベルのメモリプレッシャーを解消することができました。

コメントや質問がある場合は、フィードバックタブをご利用ください。私たちとの対話を始めることもできます。