SQL Serverは一般的にチューニング不要のデータベースと認識されている。しかし基幹系業務システムへの導入が進むにつれて、パフォーマンス・チューニングのニーズは急速に高まってきた。そこで本記事では、日本におけるSQL Serverコンサルタントの第一人者、熊澤幸生氏にSQL Serverチューニングのノウハウを語っていただくことにした。インタビュアーはSQL Serverへの造詣が深いITジャーナリスト、工藤淳氏が担当する。（編集部）
前回の「誰も知らないメモリ・チューニングの極意を教えよう」では、実践で使えるノウハウとして、メモリ・チューニングに関連するパラメータについて解説しました。今回も前回に引き続いて、SQL Serverのメモリ・チューニングを掘り下げていきます。この記事を読み終えたら、SQL Serverに必要な物理メモリ量を算出できるようになっているでしょう。
メモリのチューニングは、必要なメモリの合計を知り
それを満たす物理メモリを設計することが基本
適切な物理メモリ量はどうやって計算するのか
SQL Serverが使用するバッファプール内のメモリ総量を設定するのは、前回に解説した「max server memory」という「sp_configure」の中のパラメータで、既定値はSQL Server 2000／2005ともに2,147,483,647Mbytesです。既定値で運用すると、SQL Serverは定期的に使用可能な物理メモリの空き容量を確認し、メモリを動的に使用するようになります。マニュアルなどではメモリの動的使用を推奨していますが、ミッションクリティカルなシステムでは、実際の運用で使用するOSおよびSQL Serverのメモリ総量を正確に算出し、それに見合った物理メモリを搭載して「max server memory」を手動で設定すべきだというのが、私の持論です。
では、必要なメモリの総量を知るにはどうしたらよいのでしょうか。図1を見てみましょう。
この図1に示されている以下の4つの領域が、物理的に必要なメモリのすべてです。
- メモリプール
- memory to reserve
- SQL Serverのワーキングセット
- Windows Serverのワーキングセット
例えばWindows Server 2003とSQL Server 2000のシステムの場合、私の経験からすると大体600〜800Mbytesのワーキングセットが必要となるようです。この2つのワーキングセットはユーザーが設定することのできない領域です。
メモリプールは、実際の運用の中で実測すべき領域です。これには第1回で紹介したようなパフォーマンス監視ツールなどを利用して、SQL Serverがどれだけのメモリを使っているかを調べればよいのです。これを「max server memory」で指定します。
残っている「memory to reserve」はどのように決まるかというと、ここには2つの領域があります。
- ワーカースレッドスタック
- 特殊なメモリ予約領域（ここだけをmemory to reserveとも呼ぶこともある）
32ビットマシンでは、ワーカースレッド（クライアント接続を処理するスレッドのこと。前回参照）は1つ当たり512Kbytes、Maxワーカースレッドの既定値は255ですから合計で128Mbytesになります。「特殊なメモリ予約領域」は既定値で128Mbytesです。この2つを合計した256Mbytesが「memory to reserve」で使用されるメモリ量の既定値です。この領域のチューニング方法は後述します。
つまりは、この各領域で必要としているメモリの合計以上の物理メモリを搭載していないと、不足した分のメモリを補うために、OSはハードディスクに仮想のメモリ領域を確保して随時データを読み書きにいく、いわゆる「ページング」が起こってパフォーマンスが低下することになるのです。
この記事を読んでいるエンジニアの皆さんが、実際にデータベース設計を行う場合に最も重要なポイントは、「メモリの見積もり」をいかに的確に行えるかということになります。通常は安全のためにマージンを取って、メモリ使用量を物理メモリの8掛けくらいで考えることが多いようですが、実際の運用では各パラメータが動的に変化しながら複雑なトランザクションミックスを形成するので、これらの各要素をいかにうまく割り当てながらチューニングを行うかが、エンジニアの腕の見せどころとなるわけです。
具体的な例として、前回に紹介したケースで掲載した図を再度お見せしましょう（図2）。
図2はメモリ不足でパフォーマンスの低下が起きたシステムのメモリ使用状況を、1時間半近くにわたってモニタしたものです。赤で反転した個所が、各オブジェクトの最大値です。これらの最大値を合計したものが、このシステムの最大メモリ使用量と考えられます。そして、この合計が「利用可能な物理メモリ以内」であれば、メモリ不足に起因するパフォーマンス低下は避けられるという見積もりが成立します。
なお、使用メモリの最大値を知るには、図2のように最もシステムに負荷の掛かる状況を長時間にわたってモニタすることが大切です。Windows OSにあるシステムモニタなどで見るだけでは、その瞬間は大丈夫に見えても、ある特定の時間帯に大きなピークがあった場合にメモリ不足を発見できないからです。このケースでは、入念なメモリ使用状況の分析を基にチューニングを行った結果、ユーザー数が従来の3倍くらいになってもメモリ不足に起因した「クライアントのタイムアウトを検知して、クラスタがフェイルオーバーする事象」は発生しなくなりました。（次ページへ続く）
Copyright © ITmedia, Inc. All Rights Reserved.