SQL Serverで物理メモリを増加させてもレスポンスを悪くしない方法方について

Question

こんにちは。アべです。

SQL Server 2012を使って性能を検証しているのですが、メモリを増やしていくと遅くなる処理が幾つか発生して困っています。（2/5本）

メモリを増やした場合の処理を見ていると3000万件や4000万件のデータをクラスタスキャンで全件読み取っていることを確認しました。

メモリが少ない場合は、対象データのみ(例えば100万件程度)を読み取って処理しているので５分程度で終わるのですが、メモリを倍に増やしていくと、16GBのメモリだと５分の処理が物理メモリを32GBに増やすと３５分になるので困っています。

実行プランの流れや使用しているインデックスは概ね変わらないのですが、読みとる対象の行をメモリが多い場合は豪快に全件読み取ることが多くなり遅くなっているようなので、なんとか回避したいのですが、DBサーバへ割り当てるメモリが増えても処理が遅くならないような回避策はあるのでしょうか？ロジック？設定？

バージョン
  Microsoft SQL Server 2012 - 11.0.5613.0 (X64)

Answer 1

アベさん

はじめまして。

下記URL記載のSQLサーバーが利用するメモリ量の調整は試されましたでしょうか？

物理メモリ増設時のmax server memory の設定値について

今回のケースは、SQLのオプティマイザがよろしくない方向に作動した結果のように見えますので

メモリ量の調整では解決しない可能性があります。

オプティマイザが適切な実行プランが生成できていないとして、、統計情報が古い（メモリ16の時のまま）場合もありますので、インデックス等の再構築をしてみて、結果を見てみるというのも手かと思います。

また、何らかの設定で回避できればベストかと思いますが、メモリ増設に伴って特定のクエリだけが遅くなり、

その他のクエリのパフォーマンスは想定通り上がる。

ということであれば、特定のクエリだけ、実行計画をオプティマイザに任すのではなく、USE PLANヒントを使って、実行計画を強制してやるのはいかがでしょうか？

プラン強制の使用によるクエリ プランの指定

※実行プランを保存したsqlplanファイルを、USE PLANヒントに指定すると楽です。

Answer 2

ご助言ありがとうございます。

max server memoryは設定しております。（この設定がないと、32GBや64GBなどある程度のメモリ量になるとスワップが起きて固まることがあるようです。）

実行プランの強制などを検討して対応を考えていきますが、実行クエリの種類が何百とあるので、もう少しエイヤで対応できればと考えておりました。たとえば、処理あたりの最大使用コア数は調整しますが、それと同様にメモリ量も調整ができると助かる。

Answer 3

Enterprise Editionなら、リソースガバナーが使えそうな気もします。

---

jzkey