SQL Server 2000 オフィシャル マニュアル : 第 36 章 ‐ 一般的なパフォーマンスの問題の解決

  • [アーティクル]

第 36 章 ‐ 一般的なパフォーマンスの問題の解決

「Microsoft SQL Server 2000 オフィシャルマニュアル 下」 (発行 : 日経 BP 社) より抜粋

本書では、特定のパフォーマンスの問題の発見と解決に役立つ、各種のツールと調整できるオプションを紹介してきました。たとえば、前の章では、SQL ステートメントとストアドプロシージャの問題を特定する方法と、それらのステートメントとストアドプロシージャをチューニングして最適なパフォーマンスを得る方法を説明しました。この章は、さまざまな種類のパフォーマンスの問題の解決に必要な情報をすばやく得られるようにすることを目的としています。ほかの章で取り上げたパフォーマンス関連のトピックのいくつかを復習したり、特定のパフォーマンスの問題について説明している章を参照先として示したり、パフォーマンスの監視とシステムのチューニングに関する追加の情報を紹介します。

はじめに、「ボトルネック」の定義を簡単に復習します。次に、Windows 2000 のシステムモニタ (Windows NT ではパフォーマンスモニタ) と Enterprise Manager を使用して、パフォーマンスの問題が存在するかどうかを調べる方法を説明します。次に、アプリケーションレベル、SQL Server レベル、オペレーティングシステムレベル、ハードウェアレベルなど、さまざまなレベルで発生する多数の一般的なパフォーマンスの問題の解決方法を説明します。説明の際に、第 6 章で述べたシステムのキャパシティ計画のためのガイドラインを、必要に応じてもう一度紹介します。これらのガイドラインは、システムを分析して、パフォーマンスを改善するために追加のハードウェアが必要かどうかを判断する際に役立つからです。最後に、以前の章で説明したものも含め、パフォーマンスに影響を与える SQL Server の設定オプションについて紹介します。これらのオプションを変更することによって、システムの動作を調整することができます。

この章を読み終えたときには、パフォーマンスボトルネックを発見して、その原因を調べることができるようになっているはずです。パフォーマンスの問題は常に解決できるわけではありませんが、時間とリソースさえあれば、そのほとんどが解決可能です。

目次

36.1 ボトルネックとは
36.2 問題の発見
36.3 一般的なパフォーマンスボトルネック
36.4 SQL Server の構成設定
36.5 まとめ

36.1 ボトルネックとは

「ボトルネック」という用語は、ソフトウェアおよびハードウェアのパフォーマンスの問題に関する話題でよく使用され、コンポーネントまたはコンポーネントのセットが原因で発生するパフォーマンスの制限を指します。たとえば、キャパシティが不十分な I/O サブシステムは深刻なボトルネックの原因となり、システム全体の速度を低下させることがあります (この章の「36.3.3 I/O サブシステム」で、この状況について詳しく説明します)。

システム内で使われているコンポーネントはいずれも、ボトルネックの原因となる可能性を持っています。ボトルネックは、1 台のディスクドライブなど、単一のコンポーネントによって発生することもあれば、I/O サブシステムのようにコンポーネントのセットによって発生することも、またはさまざまなコンポーネントの組み合わせによって発生することもあります。たとえば、I/O サブシステムによって発生したボトルネックが検出されたので、システム上で発生する I/O 操作に対処できるようにディスクを増設するか (ハードウェア製品)、非効率的なクエリを最適化することによってI/O 操作回数を削減する (ソフトウェア製品)、またはその両方を行って、その問題を解決したとします。I/O 問題が解決したと思ったら、今度は CPU 関連のボトルネックが発生して、CPU の速度を上げるか、CPU の数を増やさなければならなくなるような場合もあります。

36.2 問題の発見

システムに問題があるかどうかを調べるには、まず、システムのパフォーマンスについて、いくつかの一般的な観察を行います。たとえば、ユーザーがデータベースクエリを実行したり変更を行うときに、予想よりも応答時間が長いことがないかどうかを調べます。これは、パフォーマンスの問題 (ボトルネック) の一般的な徴候です。たとえば、あるクエリを実行すると、システム上で実行中のほかのすべての操作が通常より遅くなることに気が付くかもしれません。そのような場合は、問題の原因となっているクエリに最適化の余地がないかどうか検討するか、システムにアクセスしているユーザーが少ないときに実行して検証することができます。

問題が存在するかどうかを調べるもう1つの方法は、システムを定期的に監視することです。このためには、Windows 2000 のシステムモニタや Enterprise Manager など、いくつかのツールを使用することができます。この節では、この 2 つのツールを使用してシステムの健全性を調べる方法を説明します。また、アクティブな SQL Server プロセスを監視するための sp_who システムストアドプロシージャも紹介します。

注意 :
SQL プロファイラと SQL クエリアナライザを使用して SQL ステートメントに関連するパフォーマンスの問題を検出する方法については、第 35 章を参照してください。

36.2.1 システムモニタ

システムモニタでは、Windows 2000 のパフォーマンスカウンタだけでなく SQL Server のパフォーマンスカウンタも監視することができます。これらのカウンタは、CPU 使用率や SQL Server のキャッシュヒット率など、システムの活動状況を判断するうえで役立つシステムの特性を監視します (特定のパフォーマンスカウンタについては、この章全体を通じて説明します)。リアルタイムで監視したり、データをファイルに記録して、後で見たりすることができます。

システムモニタを使用してシステムを監視するには、次の手順に従ってください。

  1. [スタート] - [プログラム] - [管理ツール] - [パフォーマンス] をクリックする。パフォーマンスコンソールが表示されるので、 [システムモニタ] をクリックする。

  2. 現在のカウンタデータを折れ線グラフ、レポート、ヒストグラムで表示するか、またはログファイルから以前に記録したデータを呼び出して表示するかを、ツールバー上の適切なボタンをクリックして指定する。図 36-1 は、グラフ表示のシステムモニタを示している。ログファイルのデータを表示する場合は、開くファイルを選択するためのダイアログボックスが表示される。

    Cc748748.sqlom3601s(ja-jp,TechNet.10).gif

    図 36-1: システムモニタ

  3. システムモニタにカウンタを追加するには、ツールバーの [追加] ボタンをクリックする。 [カウンタの追加] ダイアログボックスが表示される (図 36-2)。ローカルシステムのカウンタを表示するには、 [ローカルコンピュータのカウンタを使う] をクリックする。リモートコンピュータのカウンタを表示するには、 [次のコンピュータからカウンタを選ぶ] をクリックして、その下のボックスからリモートコンピュータの名前を選択する。

    sqlom3602

    図 36-2: [カウンタの追加] ダイアログボックス

  4. [パフォーマンスオブジェクト] ボックスからパフォーマンスオブジェクトを選択する。これらのオブジェクトは、システムコンポーネントを表す。選択したオブジェクトのカウンタが、ダイアログボックスの左下のボックスに表示される。選択したオブジェクトのすべてのカウンタを観察したい場合は、 [すべてのカウンタ] をクリックする。特定のカウンタだけを監視したい場合は、 [一覧からカウンタを選ぶ] をクリックして、一覧からカウンタを選択する。1 つのカウンタに複数のインスタンスが存在する場合もある。これらのインスタンスは、ダイアログボックスの右下のボックスに表示される。特定のインスタンスを監視するには、 [一覧からインスタンスを選ぶ] をクリックする。すべてのインスタンスを監視するには、 [すべてのインスタンス] をクリックする。

  5. [追加] をクリックする。選択したカウンタが追加され、右ペインに表示される (複数のインスタンスを選択した場合は、インスタンスごとにカウンタが追加される)。その後、さらにカウンタを追加することができる。必要なカウンタを追加し終わったら、 [閉じる] をクリックする。これで、カウンタによって提供されるパフォーマンスデータを見ることができる。図 36-3は、System\Context Switches/sec、SQLServer : Memory Manager\Total Server Memory (KB)、およびProcessor\% Processor Time という 3 つのカウンタから返された結果をグラフ表示で示している。

    Cc748748.sqlom3603s(ja-jp,TechNet.10).gif

    図 36-3: 動作中のシステムモニタ

パフォーマンスデータをログファイルに保存するには、次の手順に従ってください。

  1. パフォーマンスコンソールの左ペインで、 [パフォーマンスログと警告] を展開する。 [カウンタログ] を右クリックして、ショートカットメニューから [新しいログの設定] をクリックする。 [新しいログの設定] ダイアログボックスが表示される。図 36-4 に示すように、ここでログの名前を入力する。入力し終わったら、 [OK] をクリックする。

    sqlom3604

    図 36-4: [新しいログの設定] ダイアログボックス

    図 36-4 [新しいログの設定] ダイアログボックス

  2. 新しいログの名前が付いたダイアログボックスが表示される。 [全般] タブで、 [追加] をクリックする。表示される [カウンタの選択] ダイアログボックスで、先ほど述べたシステムモニタを使用してシステムを監視する方法の説明の手順 3～5 に従って、記録したいカウンタを選択する。 [全般] タブでは、パフォーマンスデータのサンプリング頻度を指定することもできる。

  3. [ログファイル] タブをクリックして、ログファイルの追加のプロパティを設定する。図 36-5 は、ファイル名の終わりに日付が追加された設定を示している。このファイルは、バイナリファイルとして作成される。

    sqlom3605

    図 36-5: 新しいログのダイアログボックスの [ログファイル] タブ

  4. [スケジュール] タブをクリックする。このタブでは、ログの開始時刻と停止時刻を指定する。また、現在のログが終了したときに新しいログを開始したり、任意のコマンドを実行するように指定したりすることもできる。

  5. [OK] をクリックすると、ダイアログボックスが閉じ、ログファイル情報が保存される。すぐにログを開始するように指定した場合は、 [OK] をクリックした時点で開始される。図 36-6 に示すように、ログファイルのエントリが右ペインに表示される。

    Cc748748.sqlom3606s(ja-jp,TechNet.10).gif

    図 36-6: パフォーマンスログと警告 (新しいカウンタログのエントリが表示されている)

システムモニタを定期的に使用して、システムの状態をチェックしてください。1 日または 1 週間に一度、監視を行うことによって、システムの状態を把握することができ、異常があればすぐにわかるようになります。また、後で確認できるように、パフォーマンスデータをログファイルに保存することをお勧めします。ログファイルにデータを記録しておくと、システムに変更が加えられる前に記録したパフォーマンスデータと変更後に記録したデータを比較して、変更が適切なものであったかどうかを判断する際に役立ちます。また、ログを使用することによって、日単位でのユーザーとシステムの活動の傾向を知ることができます。たとえば、月末の数日間は、ほかの時期よりもユーザー活動がはるかに高くなるかもしれません。こうした情報を知っていれば、システムがそのようなピーク時の負荷を処理できるように対策を講じることもできるようになります。

36.2.2 Enterprise Manager

Enterprise Manager は、日常的な管理作業を自動化するためだけでなく、SQL Server のプロセスとロックを監視するために使用することもできます (ロックについては、第 19 章を参照してください)。たとえば、ロックを使用しているプロセスとロックされているオブジェクトに関するデータを集めることができます。この場合のオブジェクトとは、テーブル、データベース、または一時テーブルです。この情報を表示するには、次の手順に従ってください。

  1. Enterprise Manager を開き、 [Microsoft SQL Servers] を展開し、サーバーグループを展開して、サーバーを展開し、 [管理] フォルダを展開して、 [現在の利用状況] を展開する (図 36-7)。 [現在の利用状況] には、 [プロセス情報] 、 [ロック/プロセス ID]、および [ロック/オブジェクト] の 3 つがある。

    Cc748748.sqlom3607s(ja-jp,TechNet.10).gif

    図 36-7: Enterprise Manager で [現在の利用状況] を展開したようす

  2. [プロセス情報] をクリックすると、現在 SQL Server に接続しているユーザーの名前、それらのユーザーのプロセス ID、ユーザープロセスの状態 (実行中、実行可能、休止中、またはバックグラウンドなど)、それぞれのユーザーが接続しているデータベース、それぞれのユーザーが実行しているコマンドとアプリケーション、待機時間 (リソースが使用可能になるのを待つ時間)、それぞれのプロセスの CPU、物理 I/O、およびメモリ使用量、それぞれのプロセスのブロック状態 (ほかのプロセスをブロックしているか、ほかのプロセスによってブロックされているか) などが表示される。すべての情報を見るためには、右側へスクロールしなければならない。図 36-8 は、この情報の一部を示している。

    Cc748748.sqlom3608s(ja-jp,TechNet.10).gif

    図 36-8: Enterprise Manager の [プロセス情報] に表示された情報

  3. [ロック/プロセス ID] をクリックすると、現在アクティブなプロセスのシステムプロセス識別番号 (SPID) の一覧が右ペインに表示される (図 36-9)。右ペインの SPID をダブルクリックすると、 [プロセスの詳細] ダイアログボックスが表示される (図 36-10)。このダイアログボックスには、選択したプロセスが最後に実行した T-SQL コマンドバッチが表示される。

    Cc748748.sqlom3609s(ja-jp,TechNet.10).gif

    図 36-9: [ロック/プロセス ID] ペインに表示された SPID

    sqlom3610

    図 36-10: [プロセスの詳細] ダイアログボック

    Cc748748.sqlom3611s(ja-jp,TechNet.10).gif

    図 36-11: 展開した [ロック/プロセス ID]

  4. [ロック/プロセス ID] を展開すると、左ペインに現在アクティブな SPID が表示される (図 36-11)。

  5. 左ペインの SPID をクリックすると、そのプロセスのロック情報が右ペインに表示される (図 36-11)。この情報には、ロックの種類、ロックモード、ロックの状態、およびロック所有者が含まれる。ロックの種類は、次の中の 1 つである。

    • RID - 行ロック

    • KEY - インデックス内の行ロック

    • PAG - データページまたはインデックスページロック

    • EXT - エクステントロック

    • TAB - テーブルロック (ロックされたテーブルのすべてのデータおよびインデックスデータを含む)

    • DB - データベースロック

      ロックモードは、次の中の 1 つである。

    • S - 共有ロック

    • X - 排他ロック

    • U - 更新ロック

    • BU - 一括更新ロック

    • IS - インテント共有

    • IX - インテント排他

    • SIX - インテント排他付き共有

    • Sch-S - クエリのコンパイル用のスキーマ修正ロック

    • Sch-M - DDL操作用のスキーマ安定度ロック

      ロックの状態は次の中の 1 つである。

    • GRANT - 選択したプロセスに対してロックが許可されたことを意味する。

    • WAIT - プロセスが別のプロセスによってブロックされ、ロックの解除を待っていることを意味する。

    • CNVT - ロックが別の種類のロックに変換されていることを意味する。

  6. [ロック/オブジェクト] を展開すると、ロックされているオブジェクトの一覧が表示される (図 36-12)。ロック対象となるオブジェクトとしては、テーブル、一時テーブル、データベースなどがある。

    Cc748748.sqlom3612s(ja-jp,TechNet.10).gif

    図 36-12: 展開された [ロック/オブジェクト]

  7. ロックされているデータベースまたはテーブルの名前をクリックすると、そのロック情報が右ペインに表示される (図 36-13)。表示される情報は、 [ロック/プロセス ID] の SPID をクリックしたときに表示されるのと同じ内容を別の視点から見たものである。

    Cc748748.sqlom3613s(ja-jp,TechNet.10).gif

    図 36-13: オブジェクトのロック情報

36.2.3 sp_whoシステムストアドプロシージャ

クエリアナライザまたは OSQL プロンプトで次のクエリを実行することによって、アクティブなプロセスに関する情報を表示することができます

sp_who active
GO

クエリアナライザでこのクエリを実行した結果の例を図 36-14 に示します。プロセスがブロックされている場合、 [blk] 列に、ブロックしているプロセスの SPID が表示されます。

Cc748748.sqlom3614s(ja-jp,TechNet.10).gif

図 36-14: sp_who active ステートメントの実行結果

ユーザーからトランザクションの処理が遅いという苦情が来たときには、このクエリを実行すればブロックを発見することができます。ブロックされているプロセスの大部分は 1 つのプロセスによってブロックされていることが多いので、そのプロセスがわかれば、ロックを長時間保持している理由を調べることができます。

定期的に、ロックを長時間保持しているプロセスがないかどうか、また、ほかのプロセスが排他ロックまたはテーブルロックを保持しているために頻繁にブロックされている (待機状態になる) プロセスがないかどうか監視することをお勧めします。しかし、通常、応答時間が長いという苦情が出るのは、ブロックが問題となっている場合です。ブロックがあまりにも頻繁に発生したり、長時間に及んだりする場合は、ブロックの原因となっている排他ロックまたはテーブルロックを保持しているプロセスを調べて、それらのプロセスが実行している SQL ステートメントを監視するとよいでしょう。そして、可能であれば、それらのステートメントを最適化して、ロックが早く解除されるようにするか、排他テーブルロックを使用しないようにします。一般に、プロセスがロックを取得するのを待たなければならないときには、プロセスが終了するまでに時間がかかります。したがって、ロックによる競合を削減することによって、トランザクションの応答時間を改善することができます。

詳細情報 :
ロック情報の詳細については、Books Online の「ロック情報の表示」トピックを参照してください。

36.3 一般的なパフォーマンスボトルネック

システムモニタ、Enterprise Manager、クエリアナライザ、プロファイラなどのパフォーマンス監視ツールの使い方をひととおり説明したので、パフォーマンスボトルネックに取り組む準備ができました。この節では、一般的なパフォーマンスボトルネックとそれらを解決するさまざまな方法を紹介します。これらのボトルネックの多くは密接に関連しているので、1 つのボトルネックの陰に別のボトルネックが隠れていることもあります。また、ハードウェアが原因のボトルネックとソフトウェアが原因のボトルネックの両方を調べる必要があります。パフォーマンスの問題の多くは、いくつかのボトルネックの組み合わせが原因となっているからです。ボトルネックの原因となるハードウェアコンポーネントとしては、CPU、メモリ、および I/O サブシステムがあります。ボトルネックの原因となるソフトウェアとしては、SQL Server アプリケーションと SQL ステートメントがあります。これらの種類の問題のそれぞれについて、次の節から詳しく説明していきます。

36.3.1 CPU

よくあるパフォーマンスの問題の 1 つに、CPU パワーの不足があります。システムの CPU パワーは、システム内の CPU の数、種類、および速度によって決まります。システムの CPU パワーが不十分だと、ユーザーを満足させる速度でトランザクションを処理することはできません。システムモニタを使用して CPU 使用率を調べるには、Processor オブジェクトの % Processor Time カウンタを監視します (マルチプロセッサシステムの場合は、すべての CPU インスタンスを選択します)。CPU 使用率が 75 % 以上の状態が長時間続く場合は (第 6 章で述べたガイドラインに従うと、75 % というのは許容範囲の上限値です)、CPU ボトルネックが発生している可能性があります。CPU 使用率が 60 % 前後で推移している場合でも、より高速な CPU に変更するか、CPU の数を増やした方がよいかもしれません。

CPU をアップグレードするかどうかを決める前に、システムのほかの特性も調査してください。たとえば、SQL ステートメントが非効率的であれば、システムが処理しなければならない操作が必要以上に多くなるので、ステートメントを最適化することによって CPU 使用率を下げることができるかもしれません。別の例として、SQL Server のデータキャッシュのキャッシュヒット率が 90 % 未満の場合を考えてみましょう。この場合には、データキャッシュのメモリサイズを増加させる必要があるかもしれません (max server memory オプションの値を増やすか、システムに物理メモリを追加する)。これにより、より多くのデータがキャッシュできるようになるので、物理 I/O 操作が減ります。したがって、I/O 要求の処理に費やされる処理時間が少なくなるので、CPU 使用率が低下します。シングルプロセッサシステムを使用している場合は特に、単純に CPU の数を増やすだけでシステムのパフォーマンスを改善できることもあります。

ただし、すべてのアプリケーションがマルチプロセッサシステムに対応しているわけではありません。SQL Server はマルチプロセッサシステムに対応していますが、すべての SQL ステートメントがマルチプロセッサシステムの恩恵を受けられるわけではありません。プロセスは一度に 1 つの CPU で実行されるので、1 つの SQL ステートメントを実行するだけなら 1 つの CPU で十分です。マルチプロセッサシステムがパフォーマンスを高めることができるのは、SQL Server システムが同時に複数の SQL ステートメントを実行する場合です。複数の CPU がそれぞれ異なるステートメントを処理することができるので、複数のステートメントを同時に実行することができます。

より多くの CPU パワーが必要だと判断した場合には、CPU を追加するか、既存の CPU をより高速な CPU に交換します。たとえば、システムに 2 個の CPU があり、4 個まで搭載できる場合には、同じ種類の CPU を 2 個追加するか、2 個または 4 個のより高速な CPU に交換することができます。システムに既に最大数の CPU が搭載されているときに、CPU パワーを高める必要がある場合には、より高速な CPU と交換してください。たとえば、200 MHz の CPU が 4 個搭載されているとします。これらの CPU は、たとえば 4 個の 500 MHz の CPU と交換できます。高速な CPU ほど、短時間で処理を完了することができます。

36.3.2 メモリ

SQL Server で使用可能なメモリの量は、SQL Server のパフォーマンスにとって最も重要な要素の 1 つです。メモリと I/O サブシステムの関係も、重要な要素です。たとえば、I/O 集中型のシステムでは、SQL Server がデータのキャッシングに使用できるメモリが多いほど、実行しなければならない物理 I/O 操作が少なくて済みます。これは、データがディスクからではなくデータキャッシュから取り出されるからです。SQL Server は、システムのパフォーマンスを高めるために、複雑なキャッシングシステムを使用しています。できるだけメモリ内のデータへのアクセスを増やして、パフォーマンスの低下につながるディスクアクセスをできるだけ減らすようにします。メモリ内でアクセスできるデータが多いほど、システムのパフォーマンスは向上します。メモリアクセスは、物理ディスクアクセスよりはるかに高速です。

場合によっては、メモリの不足がディスクボトルネックとして現れることがあります。システムがキャッシュを効率的に使用できないために、物理ディスクI/Oが増えるからです。SQL Server が使用しているシステムメモリの量を調べるには、システムモニタを使用して、SQLServer : Memory Manager オブジェクトの Total Server Memory (KB) カウンタを監視します。SQL Server が使用しているメモリ量が予想より少ない場合は、この章の「36.4 SQL Server の構成設定」で説明するメモリ関連の設定オプションを調整する必要があるかもしれません。

SQL Server のキャッシュメモリのサイズが十分かどうかを調べるには、システムモニタを使用して、SQLServer:Buffer Manager オブジェクトの Buffer Cache Hit Ratio カウンタを監視します。一般的な目安として、SQL Server のキャッシュヒット率は、可能な限り 90 % 以上に保つべきです。ヒット率が 90 % 未満の場合は、キャッシュメモリのサイズを増やしてください。使用しているアプリケーションの種類によっては、キャッシュヒット率が 90 % に達しない場合もあります。データページがほとんど再利用されず、システムがキャッシュ内の古いデータページを絶えずフラッシュして新しいデータを格納している場合に、このような状況になります。

注意 :
SQL Server 2000 は、使用可能なシステムメモリとメモリオプションの設定に基づいて、バッファキャッシュに動的にメモリを割り当てます。印刷プロセスなど、外部プロセスの種類によっては、それらのプロセスで使用するために、SQL Server 用に割り当てられていたメモリの大部分が解放されることがあります。入念にシステムメモリを監視して、可能であれば、SQL Server を専用システムに隔離してください。メモリオプションの詳細については、第30章とこの章の「36.4 SQL Server の構成設定」を参照してください。

36.3.3 I/O サブシステム

I/O サブシステムで発生するボトルネックは、データベースシステムで最も一般的なハードウェア関連の問題です。I/O サブシステムの不適切な構成は、非効率的な SQL ステートメントに次いで、パフォーマンスの問題における 2 番目に多い原因です。さいわいにも、I/O サブシステムにおけるパフォーマンスの問題は、解決が最も容易な問題でもあります。多くの場合、ディスクドライブの増設によって、パフォーマンスボトルネックは完全に解消されます。

I/O サブシステム問題の根本的な原因は、ディスクドライブが実行できる I/O 操作の数が限られているということにあります。たとえば、あるドライブは 1 秒間に 85 回のランダム I/O 操作しか処理できないかもしれません。ディスクドライブが過負荷の場合、それらのディスクドライブへの I/O 操作はキューに入れられ、SQL Server は長い I/O 遅延を経験することになります。このような長い I/O 遅延は、ロックの保持時間を長引かせたり、スレッドがリソースを待ってアイドル状態でいる時間を長くします。最終的には、システム全体のパフォーマンスが低下し、ユーザーがトランザクションの所要時間が長すぎると苦情を言うことになります。

ほとんどの場合、I/O サブシステムのパフォーマンスの問題は、I/O サブシステムのサイジングが適切に行われていないことが原因で発生します。サイジングについては第 5 章と第 6 章で詳しく述べましたが、ここで簡単に復習しておきましょう。

サイジングが重要なのは、既に述べたように、1 台のディスクドライブが1秒間に実行できる I/O 操作回数が限られているからです。トランザクションログの場合のように、ほとんどがシーケンシャルな I/O 操作を行うときには、ディスクドライブは過負荷になることなく、1 秒間に 150 回以上の I/O 操作を実行できるかもしれません (既に述べたように、過負荷になると長い遅延が発生します)。一方、データファイルの場合のようにランダム I/O 操作を行うときには、1 秒間に 85 回の I/O 操作しか実行できないかもしれません。I/O に対する要求が高いほど、長い遅延が発生します。

ディスクドライブが過負荷になっていないかどうかを調べるには、PhysicalDisk および LogicalDisk オブジェクトによって提供されるカウンタを監視します。これらのカウンタ (いくつかは後ほど紹介します) は、1 秒間の読み取りおよび書き込み回数など、ディスク上で行われた物理および論理ディスク I/O 活動に関するデータを収集します。PhysicalDisk オブジェクトのカウンタは、オペレーティングシステムをインストールすると自動的に有効になりますが、LogicalDisk オブジェクトは自動的には有効になりません。そこで、これらオブジェクトの有効/無効を切り替える方法を紹介することにします。また、これらのカウンタは、統計を収集するときに CPU 時間などのシステムリソースを使用するので、システム I/O を監視したいときだけ有効にすべきです。これらのカウンタを有効または無効にするには、Windows NT/2000 の diskperf コマンドを使用します。

これらのオブジェクトが有効かどうかを確認するには、コマンドプロンプトで次のコマンドを実行します。

diskperf

PhysicalDisk オブジェクトだけが有効な場合 (既定の設定) は、「物理ディスクパフォーマンスカウンタ (このシステム上) は、現在、起動時に開始するように設定されています」というメッセージが表示されます。両方のオブジェクトが無効な場合は、「このシステム上の論理と物理ディスクパフォーマンスカウンタは現在、両方とも、開始しないように設定されています」というメッセージが表示されます。

両方のオブジェクトを有効にするには、コマンドプロンプトで次のコマンドを入力します。

diskperf -Y

両方のオブジェクトを無効にするには、次のコマンドを実行します。

diskperf -N

ディスクオブジェクトの変更を有効にするには、コンピュータを再起動する必要があります。diskperf のオプションを表示するには、次のコマンドを入力してください。

diskperf -?

特に重要なカウンタは、Disk Writes/sec、Disk Reads/sec、Avg. Disk Queue Length、Avg. Disk sec/Write、および Avg. Disk sec/Read です。これらのカウンタは、ディスクサブシステムを酷使しているかどうかを調べるうえで非常に役立ちます。PhysicalDisk オブジェクトと LogicalDisk オブジェクトの両方が、これらのカウンタを提供します。これらのカウンタが提供する情報の詳細を表示するには、システムモニタでカウンタを追加するときに、 [カウンタの追加] ダイアログボックスの [説明] をクリックします。

これらのカウンタの使用例を見てみましょう。システムに I/O ボトルネックが発生していると仮定します。この場合、PhysicalDisk オブジェクトの Avg. Disk sec/Transfer、Avg. Disk sec/Read、および Avg.Disk sec/Write カウンタを監視してください。これらのカウンタでは、ディスクの遅延 (ディスクが読み取りまたは書き込みを実行するための所要時間) を調べることができます。遅延の増大は、ディスクドライブまたはディスクアレイが酷使されている兆候です。一般的な目安として、これらのカウンタの正常値は 1～15 ミリ秒 (0.001～0.015 秒) ですが、ピーク時の遅延が 20 ミリ秒 (0.020 秒) 程度であれば、心配する必要はありません。値が 20 ミリ秒を超える場合は、I/O サブシステムのパフォーマンスの問題が確かに存在しています。

Disk Writes/sec および Disk Reads/sec カウンタも調査してください。これらのカウンタを監視した結果、ディスクは毎秒 20 回の書き込みと 20 回の読み取り、合計 40 回の I/O 操作を実行しており、このディスクのキャパシティは毎秒 85 回の I/O 操作であると仮定します。I/O 操作の回数が許容範囲内に収まっているにもかかわらず、ディスクの遅延が長い場合は、ディスクドライブに欠陥がある可能性があります。今度は、ディスクが毎秒 100 回の I/O 操作を行っていて、遅延が 20 ミリ秒以上であると仮定しましょう。この場合、パフォーマンスを改善するには、ドライブを追加する必要があります。

RAID アレイを使用している場合に、システムが実行している I/O 操作回数を調べるには、システムモニタで調べた 1 秒間の I/O 操作回数をアレイ内のドライブ数で割り、RAID レベルに応じたオーバーヘッド係数を掛けます。次の表は、RAID を使用しているときに読み取りおよび書き込みのために生成される物理 I/O 操作の回数を示しています。

表 36-1 1 回の読み取りおよび書き込みのために実行される RAID レベル別の物理 I/O 回数

RAID レベル

読み取り

書き込み

0

1

1

1 および 10

1

2

5

1

4

一般に、I/O サブシステムのボトルネックを解決する最良の手段は、単にディスクドライブを追加することです。しかし、I/O ボトルネックには、低いキャッシュヒット率や必要以上に I/O 操作を実行するトランザクションなど、ほかの原因も考えられるので、それらも忘れずにチェックしてください (既に述べたように、ほとんどの場合、90 % 未満のキャッシュヒット率というのは低すぎます)。I/O ボトルネックを発見したら、第 6 章の説明を読み返して、システムに必要なディスクドライブの数を調べてください。

36.3.4 欠陥品

ときには、ハードウェアの欠陥品によるパフォーマンスの問題を経験するかもしれません。欠陥品が完全に壊れているのではなく、単に品質が悪いだけの場合、問題の解決は困難です。これらの問題とその解決策は多種多様な形をとり、非常に複雑でもあるので、本書で詳細に説明することはできません。代わりに、欠陥品による問題を識別するための基本的なヒントをいくつか示します。

  • ディスクドライブおよびアレイの比較
    システムモニタで統計を見るときに、同種のコンポーネントを比較する。たとえば、2 つのディスクの I/O 操作回数がほぼ同じなのに、異なる遅延を示している場合は、遅い方のドライブに問題がある可能性がある。

  • ライトのチェック
    ネットワークハブには、通常、衝突 (コリジョン) を示すライトが付いている。特定のネットワークセグメントの衝突が異常に多い場合は、欠陥品 (おそらくネットワークカードかネットワークケーブル) がある可能性がある。

  • システムを知り尽くす
    システムの観察に費やす時間が長いほど、システムの異変に早く気付くようになる。システムが正常な能力を発揮していないことが、すぐにわかるようになる。

  • システムモニタの使用
    システムモニタは、システムの動作を定期的に監視するための優れた手段である。

  • ログのチェック
    SQL Server とイベントビューアのシステムおよびアプリケーションログを定期的にチェックする習慣を付けるべきである。これらのログを毎日チェックして、手に負えなくなる前に問題を発見するようにする。この習慣は、いくら強調しても強調しすぎということはないほど重要である。

36.3.5 アプリケーション

パフォーマンスの問題の原因となるもう 1 つの一般的なシステムコンポーネントは、SQL Server アプリケーションです。この問題の根本的な原因は、アプリケーションコードまたはアプリケーションが実行している SQL ステートメントです。この節では、SQL Server アプリケーションに関連するパフォーマンスの問題を解決するためのヒントとガイドラインを示します。

  • 実行プランの最適化
    第 35 章で述べたように、クエリの処理に応じた最適な実行プランとデータアクセス方法を選ぶことが重要です。残念なことに、最適なプランを決めるための秘密の公式はありません。SQL Server は、クエリオプティマイザが計算した実行プランを最適なプランとして自動的に選択します。実行プランを最適化するために、クエリヒント、テーブルヒント、および結合ヒントなど、さまざまな種類のヒントをクエリで使用することができます。これらのヒントに精通するにつれて、クエリに最適なプランを自然に判断できるようになるかもしれません。通常、最適なプランを見つけるためには、さまざまなプランを試す必要があります。

  • インデックスの活用
    第 17 章と第 35 章で説明したように、パフォーマンスを高めるには、インデックスの正しい使用が不可欠です。インデックスを使用すれば 10～20 回の I/O 操作で目的のデータを見つけられるのに、テーブルスキャンを使用すると数千または数万回の I/O 操作が必要になる場合もあります。ただし、インデックスの使用には注意が必要です。INSERT、UPDATE、または DELETE ステートメントを使用してテーブル内のデータを変更するときには、そのデータに関連付けられたインデックスが、変更されたデータを反映するように自動的に更新されるため、テーブルの変更によって生じる I/O 操作に加えて、インデックスを更新するための I/O 操作が発生します。あまり多くのインデックスを作成しないように気をつけてください。インデックスが多すぎると、インデックスの更新に必要なオーバーヘッドによってデータ変更操作のパフォーマンスが低下します。

  • ストアドプロシージャの使用
    第 21 章で説明したように、ストアドプロシージャは、あらかじめパッケージ化され、コンパイルされた SQL ステートメントをサーバー上で実行するために使用します。クライアント上のアプリケーションから個々の SQL ステートメントを実行する代わりに、サーバー上のストアドプロシージャを呼び出すことによって、サーバー上の SQL ステートメントの再利用度が高まるだけでなくネットワークトラフィックが大幅に削減されて、パフォーマンスが向上します。ストアドプロシージャはサーバー上に格納されており、データの処理とフィルタリングをアプリケーションではなくストアドプロシージャ内に組み込めるので、クライアントとサーバーの間で送受信されるデータの量を削減することができます。

36.4 SQL Server の構成設定

SQL Server 2000 は、自動設定機能を備えていますが、特定の設定オプションを変更することによって、システムの動作とパフォーマンスを変えることができます。この節では、これらのオプションを設定する方法と、それらがシステムの動作にどのような影響を与えるのかを説明します。これらのオプションを変更する必要はほとんどありませんが、設定オプションがどんなものであり、何をするかを知っておくことで、設定を変更すべきかどうかを判断することができるようになります。これらのオプションを変更するには、Enterprise Manager または sp_configure システムストアドプロシージャを使用します。

Enterprise Manager を使用する場合は、設定したいサーバーの名前を右クリックして、ショートカットメニューから [プロパティ] をクリックし、 [SQL Server のプロパティ] ダイアログボックスを表示します。このダイアログボックスには9つのタブがあり、それぞれのタブに設定可能なオプションが含まれています。これらのタブとオプションについては、後ほど説明します。

sp_configure システムストアドプロシージャを使用してこれらのオプションを設定するときには、いくつかのオプションが拡張オプションであることに注意してください (どのオプションが拡張オプションであるかは、後ほど示します)。sp_configure システムストアドプロシージャを使用して拡張オプションを変更するには、show advanced options オプションを 1 (有効) に設定する必要があります。このオプションは、既定では 0 (無効) に設定されています (Enterprise Manager を使用して拡張オプションを設定するときには、このオプションの設定を気にする必要はありません)。show advanced options オプションを有効にするには、次のステートメントを使用します。

sp_configure 'show advanced options', 1 GO

一般に、sp_configure システムストアドプロシージャを使用してオプションを設定する場合には、次の構文を使用します。

sp_configure '<option name>', <value>

36.4.1 affinity mask オプション

affinity mask オプションは、マルチプロセッサ環境で SQL Server スレッドを実行する CPU を指定するために使用します。既定値の 0 は、Windows 2000 のスケジューリングアルゴリズムによってスレッドのプロセッサアフィニティ (プロセッサとスレッドの関連付け) が決まることを示します。0 以外の値は、SQL Server が実行できる CPU を定義するビットマスクとして解釈されます。10 進値の 1 (2 進値では 00000001) は CPU1 だけを使用できることを示し、2 (00000010) は CPU2 だけを使用できることを示し、3 (00000011) は CPU1 か CPU2 を使用できることを示します。以下も同様です。

このオプションは拡張オプションなので、sp_configure システムストアドシージャを使用して設定するときには、show advanced options オプションを1に設定しておく必要があります。affinity mask オプションは、Enterprise Manager を使用して設定することもできます。Enterprise Manager の場合は、 [SQL Server のプロパティ] ダイアログボックスの [プロセッサ] タブをクリックして、 [プロセッサ制御] で、SQL Server が使用するそれぞれの CPU のチェックボックスを選択します。変更を保存するには [OK] をクリックします。また、この変更を有効にするには、SQL Server を再起動する必要があります。

専用の SQL Server システムでは、SQL Server がすべての CPU を使用できるように affinity mask オプションを設定してください。SQL Server 専用ではないシステム (CPU 時間を必要とするほかのプロセスを含んでいるシステム) では、SQL Server が 1 つの CPU を除いたすべての CPU を使用するように affinity mask オプションを設定してみてください。

36.4.2 lightweight pooling オプション

lightweight pooling オプションは、SQL Server が、スレッドの代わりにより軽量なファイバ (スレッドのサブコンポーネント) を使用するように設定します。ファイバを使用すると、 (Windows NT または Windows 2000 オペレーティングシステムのスケジューラではなく) SQL Server がスケジューリングを管理できるので、コンテキストスイッチを削減することができます。マルチプロセッサシステムでアプリケーションを実行していて、コンテキストスイッチが多く発生する場合は、lightweight pooling オプションを 1 (有効) に設定して、コンテキストスイッチが削減されたかどうか確認してみてください。既定値は0であり、ファイバは無効です。

lightweight pooling オプションは拡張オプションなので、sp_configure システムストアドプロシージャを使用して設定するときには、show advanced options オプションを1に設定しておく必要があります。また、Enterprise Manager を使用して設定することもできます。 [SQL Server のプロパティ] ダイアログボックスの [プロセッサ] タブをクリックして、 [プロセッサ制御] の [Windows NT ファイバーを使用] チェックボックスをオンにすると、このオプションが有効になります。このチェックボックスをオフにすると無効になります。 [OK] をクリックし、SQL Server を再起動すると、変更が有効になります。

36.4.3 max server memory オプション

SQL Server は、既定では動的にメモリを割り当てます。SQL Server がバッファプールに割り当てる最大メモリ量 (MB 単位) を指定するには、max server memory オプションを設定します。SQL Server がメモリを解放するときには少し時間がかかるので、定期的にメモリを必要とするアプリケーションがほかにある場合は、SQL Server がほかのアプリケーションのためにメモリの一部を解放するように max server memory オプションを設定します。既定値は 2,147,483,647 です。この設定の場合、SQL Server はシステムから可能な限りのメモリを取得し、ほかのアプリケーションがメモリを必要としているときには動的にメモリを解放して、ほかのアプリケーションがメモリを解放したときには動的にメモリを割り当てます。専用の SQL Server システムの場合は、この設定を使用することをお勧めします。この設定を変更する場合は、物理メモリの合計から Windows 2000 と SQL Server 以外のアプリケーションに必要なメモリを引いて、SQL Server に割り当てることができる最大メモリ量を計算してください。

このオプションは拡張オプションなので、sp_configure システムストアドプロシージャを使用して設定するためには、show advanced options オプションを1に設定しておく必要があります。Enterprise Manager を使用して設定する場合は、 [SQL Server のプロパティ] ダイアログボックスの [メモリ] タブをクリックして、 [SQL Server メモリの動的設定] をクリックし、 [最大] スライダを調節します。このオプションの変更は、SQL Server を再起動しなくても、直ちに有効になります。また、 [固定メモリサイズを使用] をクリックすると、固定のメモリ量を設定することができます。この場合、SQL Server はその量までメモリを割り当てて、その後はメモリを解放しません。

36.4.4 min server memory オプション

min server memory オプションは、SQL Server のバッファプールに割り当て可能な最小メモリ量 (MB 単位) を指定するために使用します。SQL Server がほかのアプリケーションのために確保するメモリ量が多くなりすぎるおそれがあるシステムでは、このオプションを設定するとよいでしょう。たとえば、サーバーがデータベースサービスだけなく、印刷とファイルサービスも提供する環境では、SQL Server がこれらのアプリケーションに譲渡するメモリが多くなりすぎるかもしれません。この場合、応答時間が遅くなります。

min server memory オプションの既定値は 0 であり、SQL Server は動的にメモリの割り当てと解放を行うことができます。これが推奨の設定ですが、サーバーが SQL Server 専用でない場合には、既定値から変更する必要があるかもしれません。

このオプションは拡張オプションなので、sp_configure システムストアドプロシージャを使用して設定するには、show advanced options オプションを1に設定しておく必要があります。このオプションは、Enterprise Manager を使用して設定することもできます。 [SQL Server のプロパティ] ダイアログボックスの [メモリ] タブをクリックして、 [SQL Serverメモリの動的設定] をクリックし、 [最小] スライダを調節します。このオプションの変更は、SQL Server を再起動しなくても、直ちに有効になります。

36.4.5 recovery interval オプション

recovery interval オプションを使用すると、障害発生時にシステムの復旧に必要な最大時間 (分単位) を定義することができます。SQL Server は、この設定と特別な内部アルゴリズムを使用して、自動チェックポイントの実行頻度を判断し、復旧の所要時間が指定された時間以上にならないようにします。SQL Server は、システム内で行われている作業量に応じて、チェックポイントの間隔を決めます。作業量が多いほど、チェックポイントは頻繁に実行されます。ディスクにフラッシュされていないデータ量が少ないほど、SQL Server がクラッシュから復旧するために必要な時間は短くなります。また、チェックポイントの間隔が長いほど、復旧にかかる時間も長くなります。

recovery interval オプションを大きくすると、チェックポイントの数が少なく (間隔が長く) なるので、パフォーマンスが向上します (チェックポイントはディスクに大量の書き込みを行うので、数秒間、ユーザートランザクションの処理が遅くなります)。ただし、SQL Server が復旧に要する時間も長くなります。既定値は0であり、SQL Server が自動的に間隔を設定します。この場合の復旧間隔は約 1 分です。recovery interval オプションの値を増やす設定は、管理者自身の責任において行ってください。5～15 (分) という値は不正なわけではありませんが、システムクラッシュの際には、データベースが復旧するまで 5～15 分待たなければなりません。一般に、recovery interval オプションの値は、チェックポイントによる大量の書き込みが行われる頻度を削減して、ユーザーが中断なしにトランザクションの I/O を実行できるようにしたい場合に増やします。

このオプションは拡張オプションなので、sp_configure システムストアドプロシージャを使用して設定するには、show advanced options オプションを 1 に設定する必要があります。Enterprise Manager を使用してこのオプションを設定するには、 [SQL Server のプロパティ] ダイアログボックスの [データベースの設定] タブをクリックして、 [復旧間隔] ボックスに値を入力します。このオプションの変更は、SQL Server を再起動しなくても、直ちに有効になります。

36.5 まとめ

この章では、データベース管理者が経験する可能性があるパフォーマンスの問題について解説しました。システムモニタと Enterprise Manager を使用してシステムを監視し、パフォーマンスボトルネックを発見する方法を説明しました。また、一般的なシステムパフォーマンスの問題を発見して解決する方法を説明しました。

本書では、SQL Server 2000 の管理の「方法」、「対象」、そして「理由」について説明してきました。これで読者の皆さんは、SQL Server システムの管理と設定を適切に行い、日常の管理作業を容易かつ効率的に実行できるようになったはずです。私たちが本書を楽しみながら執筆したように、読者の皆さんにも楽しみながら読んでいただけたら幸いです。