• [アーティクル]

SQL に関する Q&Aメモリの構成、パフォーマンスのプロファイル、FILL FACTOR の設定など

Nancy Michell

メモリを構成する

ヒント : 簡略化されたプロファイル

パフォーマンス モニタを SQL Server 2005 の SQL Server Profiler と関連付けられることをご存知でしたか。

おそらく、SQL Server コンピュータのパフォーマンス モニタで、CPU 使用率の急上昇、過剰なメモリの消費、または全体的なパフォーマンスの低下に気付いて、このようなパフォーマンス異常の原因を考えたことがあるでしょう。SQL Server 2005 以前では、パフォーマンス モニタのログをキャプチャするには、まず、Profiler でトレースをキャプチャし、Enterprise Manager で sysprocesses を確認する必要がありました (もちろん、パフォーマンス モニタのログをキャプチャするには、パフォーマンス モニタを起動する必要がありました)。このような作業が必要であったにもかかわらず、パフォーマンス低下の原因を解明するには、ツール間でイベントを手動で調整する必要がありました。つまり、各ログを 1 つずつ確認する必要がありました。楽しい作業ではありませんが、パフォーマンスの問題の原因を特定するには必要な作業でした。

SQL Server 2005 では、依然としてトレースをキャプチャしてパフォーマンス モニタのログを検討する必要がありますが、Profiler でログを関連付けることができるようになりました。T-SQL ステートメントをスクロールすると、Profiler では、発生した事象が視覚的かつ自動的に表示されます。Profiler でパフォーマンス モニタのユーザー インターフェイスをクリックすると、そのタイムスタンプに関連するステートメントに移動します。この機能により、SQL Server 環境のトラブルシューティングにかかる時間を大幅に短縮できます。

パフォーマンス モニタのログを Profiler に関連付ける方法を説明します。

  1. パフォーマンス モニタを起動して、データベース サーバーの情報のキャプチャを開始します。
  2. [パフォーマンス ログと警告] ノードの下に新しいカウンタ ログを作成し、新しいログの名前を入力します。
  3. 新しいカウンタ (% Processor Time など) を追加します。[スケジュール] タブでは、手動または定期的にログ記録を開始するよう設定することもできます。
  4. [OK] をクリックします。手動オプションを選択した場合は、必ずログ記録を開始します。
  5. Profiler で SQL Server のトレースを設定します。これには、[ファイル] メニューの [新しいトレース] をクリックします。トレースには StartTime と EndTime を含めます。次に、トレースに名前を付け、ファイルに保存するよう設定します。最後に、サーバーを稼動してトランザクション処理のシミュレーションを行ってから、パフォーマンス モニタと Profiler の両方でデータのキャプチャを停止します。
  6. Profiler で、[ファイル] メニューの [パフォーマンス データのインポート] をクリックします。次に、パフォーマンス モニタのログを保存した場所を選び、[File] (ファイル) の [Open] (開く) をクリックして [Trace] (トレース) を選択します。Profiler のトレースを保存した場所を選択します。

この作業を完了すると、特定の SQL ステートメントが処理時間に及ぼした影響を簡単に把握できるようになったことを実感できるでしょう。

質問 - SQL Server™ コンピュータに最適なメモリ構成を検討しています。私の前任の管理者は、SQL Server 2000 の 2 ノード クラスタ上に 12GB の RAM を搭載した各コンピュータの Boot.ini で、/PAE は有効、/3GB は無効 (SQL Server で AWE を有効にしない) という設定をしていました。12 GB の RAM が利用できるので、Boot.ini の /3GB スイッチを削除して、AWE を有効にし、12 GB のうち約 10 GB を SQL Server に割り当てる必要があるでしょうか。コンピュータには SQL Server 以外に実行するものはなく、他のアプリケーションでメモリが必要になることはありません。

回答 - はい。Address Windowing Extensions (AWE) を有効にして、SQL Server の RAM の上限を事前に割り当てる必要があります。12 GB のメモリを搭載した SQL Server 専用のコンピュータであれば、10 GB は適切でしょう (事前割り当ては SQL Server 2000 でのみ有効で、SQL Server 2005 以降では、AWE の使用は固定的ではなくなり、その場で変更されることがあります)。/3GB スイッチと /PAE スイッチの両方を使うのか、/PAE スイッチだけで十分なのかという問題は、常に議論の的になってきました。実は /PAE スイッチを設定して AWE を有効にするだけでも十分ですが、次のような点を考慮して、両方のスイッチを利用することをお勧めします。

結局のところ、/3GB スイッチを使用するかどうかは、状況によって異なります。仮想アドレス空間の最初の 2 ～ 3 GB にあるメモリの MemToLeave 領域が不足していないかどうか。スイッチを使用した場合、オペレーション システムのメモリが不足していないかどうか (詳細については、support.microsoft.com/kb/316739 を参照してください)。クラスタを使用している場合、/3GB スイッチを一方のノードに設定し、もう一方のノードには設定しないという構成にすることができます。このような構成にすると、/3GB スイッチのテストで問題が発生した場合は、インスタンスをすぐにもう一方のノードにフェールオーバーすることができます。16 GB 以上の RAM を搭載している場合、/3GB スイッチはサポートされないことに注意してください。

/3GB スイッチを使用すると、仮想アドレス空間 (VAS) が 50% 増加するので、データ キャッシュだけでなく、VAS にメモリ負荷をかけるアプリケーションにも大きな効果があります。さいわい、IA64 と x64 の両方の 64 ビット サーバーでは、この誤解されている要因を無視します。OS のメモリ不足に関する問題は、SQL Server 専用のコンピュータの場合は当てはまりません。OS に 2 GB を割り当てるのは少々行き過ぎです。サーバーを SQL Server 専用サーバーとして使用していて、標準的な最小限の OS のサービスだけを実行している場合、サーバーに約 1.3 GB の空きメモリがあるので、SQL Server で利用できるメモリの量を 1 GB 増加できます。まず 10 GB から始めて、パフォーマンス モニタで使用可能なメモリを長期間監視して、使用されていないメモリの量を確認し、適切な値に調整します。SQL Server 2000 の AWE は SQL Server 2005 の AWE ほど動的ではないので、過剰なコミットを行うと、スワップが発生することに注意してください。/3GB スイッチを使用するかどうかを判断する鍵は、個々の環境で /3GB スイッチを使用してテストを行うことです。

レプリケーションのインスタンス名

質問 - レプリケートするインスタンスを指定する際に、SQL Server 2005 のレプリケーションでサーバーの IP アドレスを使用することはできますか。「[HOWTO] 信頼されていないドメイン間またはインターネット経由で SQL Server を実行しているコンピュータ間のレプリケート方法」(support.microsoft.com/kb/321822) によると、SQL Server 2000 では、このような操作を行うとエラーが発生するそうですが、SQL Server 2005 でも同様でしょうか。

回答 - レプリケーションに参加するサーバーのインスタンスを指定する場合は、SQL Server に登録されているインスタンス名を指定する必要があります。たとえば、コマンドラインで、レプリケーション ストアド プロシージャやレプリケーション エージェントの接続設定に対して、Publisher または Subscriber パラメータを指定する場合は、SQL Server インスタンス名を使用する必要があります。SQL Server インスタンスのネットワーク名が SQL Server に登録されているインスタンス名と異なる場合、エージェントのレプリケーション接続は失敗します。

インスタンスのネットワーク名と SQL Server のインスタンス名が異なる場合は、SQL Server のインスタンス名を有効なネットワーク名として追加することを検討してください。代替ネットワーク名を設定する 1 つの方法として、その名前をローカル ホスト ファイルに追加することができます。ローカル ホスト ファイルは、既定では、WINDOWS\system32\drivers\etc または WINNT\system32\drivers\etc にあります。たとえば、コンピュータ名が comp1、そのコンピュータの IP アドレスが 10.193.17.129、インスタンス名が inst1/instname の場合は、ホスト ファイルに次のエントリを追加します。

10.193.17.129 inst1

SQL Server Integration Services

質問 - SQL Server 2005 のアクティブ/アクティブ クラスタ (2 台のサーバーで 64 ビット Enterprise Edition を実行) のインストール中で、SQL Server 2005 のインスタンスは全部で 4 つになる予定です。すべてのインスタンスに SQL Server Integration Services (SSIS) が必要です。SSIS のクラスタ化とメンテナンス プランへの影響に関して、注意する点はありますか。

回答 - SSIS サービスをクラスタ化することはできますが、クラスタ化する必要はありません。しかも、クラスタ化により、委任がサポートされない (msdn2.microsoft.com/aa337083 参照)、複数インスタンスがサポートされない (1 つのノードで同時に実行できるインスタンスは 1 つだけ) など、さまざまな問題が発生することがあります。

以前は、メンテナンス プラン ウィザードを実行するには、SSIS をインストールする必要がありました (ただし、実行する必要はなく、単にインストールする必要がありました)。しかし、SQL Server 2005 SP1 では、この要件はなくなりました。SSIS を実行していない場合、メンテナンス プランは SQL Server エージェントで実行できます。

SSIS をクラスタ化するのではなく、SSIS をスタンドアロン サービスとして実行し、すべての実行中のインスタンスを指定するように MsDtsSrvr.ini.xml を編集することをお勧めします。このようにすると、サービスのクラスタ化に関する問題が発生することなく、あらゆるノードからパッケージを管理することが可能で、ほとんどのユーザーから期待されている高可用性を実現できます。

メンテナンス プランを作成できない場合の詳細については、サポート技術情報の記事 (support.microsoft.com/kb/909036) を参照してください。

奇妙な実行時間

質問 - SQL Server 2005 SP1 コンピュータの負荷テスト中に、SQL Server Profiler でストアド プロシージャ (SP) の実行時間に負の値が多く記録され、終了時刻から開始時刻を引いた値と一致しない SP の実行時間もありました。

回答 - SQL Server Profiler による SP の実行時間や他のパフォーマンスに関する時間のレポートは、多くの要素の影響を受けます。SQL Server 2005 では実行時間をミリ秒単位で測定します。測定単位を変更するテクノロジを利用している場合、レポート結果に矛盾が生じたり、計算の合わない実行時間が記録されたりすることがあります。

たとえば、他の電源設定、CPU ステップ実行、または AMD の Cool 'n Quiet テクノロジを使用すると、CPU の周波数が変更されるので、SQL Server Profiler で実行時間を計算した場合の値と一致しません。

この問題に関する現象、さまざまな原因、および解決策については、サポート技術情報の記事 (support.microsoft.com/kb/931279) を参照してください。

ヒント : FILL FACTOR を確認する

いっぱいまで水を入れたコップがあって、このコップにさらに水を注ごうとすると、何が起こるでしょうか。水があふれます。

SQL Server の場合も同じ現象が起こります。インデックスがいっぱいになったページに新しい行を追加すると、新しい行を挿入する領域を確保するために、SQL Server では半数の行が新しいページに移動されます。これをページ分割と呼びます。ページ分割では新しいレコード用の領域が確保されますが、この処理は、時間がかかり、多くのリソースを消費します。また、断片化を引き起こし、I/O 処理のパフォーマンスが低下することがあります。 それでは、ページ分割を回避するにはどうすればよいでしょうか。

このような状況を回避するには、FILL FACTOR の値を事前に決定する必要があります。インデックスが作成または再構築されると、FILL FACTOR の値によって各リーフ レベルのページのデータを格納する領域の割合が決まり、それ以外の領域は、今後のインデックスの増加に備えて予約されます。たとえば、FILL FACTOR の値を 60 にすると、基になるテーブルにデータが追加されるときにインデックスを拡張するための領域として、各リーフ レベルのページの 40% は空き領域として確保されます。

FILL FACTOR の既定値は常に 0 ですが、多くの場合は、これで問題ありません。基本的に、FILL FACTOR の値を 0 に設定すると、リーフ レベルのページは、ほぼ全領域がデータの格納に使用されますが、少なくともインデックスを 1 行追加するだけの領域は残されます (FILL FACTOR の値 0 と 100 は同じことです)。

CREATE INDEX ステートメントまたは ALTER INDEX ステートメントでは、FILL FACTOR の値をインデックスごとに設定できます。また、サーバー レベルで FILL FACTOR の値を直接構成して、新しく作成したインデックスで既定値が使用されるようにすることもできます。

次の例では、今後のインデックスの拡張に備えて 30% の空き領域を確保するように、サーバー レベルで FILL FACTOR の値を 70 に設定します。もちろん、このオプションは、運用環境で実装する前に、慎重にテストする必要があります。

USE Master; GO SP_Configure 'show advanced options',1; GO SP_Configure 'Fill Factor', 70; GO -- 変更を有効にするには、SQL Server エンジンを再起動する必要があります。

インデックス レベルごとに FILL FACTOR の値を設定する必要がある場合はどうでしょうか。次のようなテーブルを作成中で、Col_A という列に FILL FACTOR の値が 70 の一意のインデックスを作成する場合のコマンドは、次のようになります。

-- Item テーブルを作成します。USE Item_DB; GO CREATE TABLE ITEM (Col_A Varchar(100),Col_b Varchar(200)); GO;

-- Item テーブルの Col_A 列に FILL FACTOR の値が 70 の一意のインデックスを作成します。CREATE UNIQUE INDEX AK_Index ON Item (Col_A) WITH (FillFactor = 70); GO

各インデックスの FILL FACTOR の値を特定するにはどうすればよいでしょうか。その場合は、次のように sys.Indexes をクエリして、データベースのすべてのインデックスに設定されている FILL FACTOR の値を取得できます。

USE Item_DB; GO SELECT Fill_Factor FROM Sys.Indexes WHERE Object_id=object_id('item') AND name IS NOT NULL; GO

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.