• [アーティクル]

SQL に関する Q&A一時テーブル、64 ビット処理、ミラーリングなど

Nancy Michell

一時テーブル

Q 競合の問題を回避するためには、一時データベース (tempdb) の使用が常に適切とは限らないと聞きました。それは本当ですか。多数のテーブルに散在しているデータを取得したり、さらには変更したりする必要があるため、一時データベースを使用してきました。ビューを使用してこれを行うのは容易ではありません。

A 確かに、tempdb を頻繁に使用すると競合が発生する場合があります。ですが通常、競合というのは、サポート技術情報の記事 (support.microsoft.com/kb/328551) で説明されているように、まれな状況でのみ発生する問題です。たとえば、数百の SPID (サーバー プロセス識別子) で多数の一時テーブルを一度に作成および削除する場合などです。

実際、一時テーブルの使用は、十分許容可能な手段になり得ます (ちなみに、一時テーブルには tempdb に含まれるものもそうでないものもあります)。重要なのは、一時テーブルからデータを取り出す際のパフォーマンスの向上が、最初のテーブル作成にかかるオーバーヘッドというデメリットを上回るかどうかという点です。

この方法を退ける前に、一時テーブルのサイズ、寿命、使用頻度、そして特に、一時テーブルのアクティブ インスタンス数について考える必要があります。tempdb の競合はハードウェアに関連している場合もあるので、サーバーの構成についても考慮する必要があります。

短期間しか存在しない、小さいテーブルが多数必要な場合は、テーブル変数を使用することもできます。このような場合には、一時テーブルよりもテーブル変数を使用する方がメリットがあります (テーブル変数と一時テーブルの違いに関する詳細については、次の質問の回答、および「[INF] よく寄せられる質問 - SQL Server 2000 - table 変数」を参照してください)。

今回の質問の場合、データベースにクエリを実行する際に毎回、一時テーブルを作成しなければならないとしたら、その状況を受け入れるのではなく、元のデータ モデルの見直しが必要な可能性もあります。

Q テーブル変数とは何ですか。また、どのような働きをするものですか。本当に一時テーブルの代わりに使用できるのですか。

A 一時テーブルとテーブル変数で提供される基本機能は同じです。実際、tempdb データベース内ではどちらも一時テーブルとして実現されています。ただし、ほんの少数の行を挿入する場合には、テーブル変数を使用した方がパフォーマンスが向上します。これは、テーブル変数が統計情報もインデックスも保持しないためです。そのため、オーバーヘッドが少なくなります。テーブルの容量が、データベース エンジンがデータ キャッシュに常駐させる、サイズ 8 KB のデータベース ページ、数ページ分を超えない場合は、テーブル変数の使用をお勧めします。

SQL Server™ Books Online によると、

  • テーブル変数は、ローカル変数のように機能します。この変数には適切に定義されたスコープがあります。スコープは、それが宣言されている関数、ストアド プロシージャ、またはバッチです。テーブル変数は、そのスコープの中で、通常のテーブルのように使用できます。
  • テーブル変数は、それが定義されている関数、ストアド プロシージャ、またはバッチの終了時に自動的に削除されます。
  • ストアド プロシージャでテーブル変数を使用すると、ストアド プロシージャの再コンパイルの回数が、一時テーブルを使用した場合よりも少なくなります。
  • テーブル変数を使用すると、ロックおよびログで必要なリソースが少なくて済みます。
  • 統計は保持されませんが、場合によってはパフォーマンスが向上することがあります。

ミラーリング

Q SQL Server 2005 のミラーリングを使用して可用性の向上を図ろうと考えています。自動フェールオーバーをサポートするために、システムを高可用性モードで実行しますが、リソースを最大限に活用するため、ミラー サーバーでは他のアプリケーションも実行する予定です。ミラー サーバーは、プライマリ データベースのミラーである運用データベースと、スタンドアロン データベースであるステージング データベースという 2 つのデータベースをサポートします。また、2 種類のクライアントもサポートします。運用データベースにアクセスするためにミラーリング フェールオーバーを使用して構成されたクライアントと、ステージング データベースに直接接続するクライアントです。

さらに、ミラー サーバーでは SQL Server Integration Services (SSIS) パッケージもいくつか実行します。SSIS パッケージは両方のデータベースにアクセスし、ローカルのステージング データベースからプライマリ サーバー上で実行されている運用データベースにデータを転送します。フェールオーバー時には、SSIS パッケージはローカルのステージング データベースからローカルの運用データベースにデータを転送します (フェールオーバー)。以上の構成に関して、認識しておくべき問題は何かありますか。

A 一般的には、このような構成はお勧めしません。フェールオーバー後に新しいプリンシパルに過度の負荷がかかるためです。

機能的には、ミラーリングの観点から見ると問題はないように思えますが、この構成で進めていく場合は徹底的なテストが必要です。テストは、最大負荷の場合、標準構成の場合、およびフェールオーバー後について行う必要があります。最大負荷は現在の最大負荷ではなく、将来予想される最大負荷にします。将来のビジネス ニーズを考慮に入れておかないと、負荷が増大して容量を超えた場合にこのソリューションは機能しなくなります。

テストによって、ネットワークやディスクのボトルネック、および CPU のボトルネックまで発見される可能性が大いにあります。ボトルネックは、アプリケーションの応答時間の長さ、許容し難いスループット、タイムアウト エラー、ミラー サーバーの再実行キューが非常に高くなることなどの形で現れます (再実行キューが非常に高いと、フェールオーバーの時間が実際の再実行速度に応じて予想外に長くなる場合があります)。

また、フェールオーバー中にパッケージを実行していると、障害のあるプリンシパル サーバーとの接続が切断されます。フェールオーバーが完了して新しいプリンシパル サーバー上のデータベースが使用できるようになると、パッケージは新しいプリンシパル サーバーに再接続できるようになります。これはもちろん、パッケージに接続再試行メカニズムが組み込まれていて、接続エラーを適切に処理することを前提としています。適切に処理するとは、たとえば、"切断された" 状態から回復できることなどです。

SQL Server と 64 ビット OS

Q Windows Server® オペレーティング システムの 64 ビット バージョンを実行している場合、32 ビットの SQL Server 2000 ではなく SQL Server 2000 Enterprise Edition (64 ビット) を使用する必要があるというのは本当ですか。

A SQL Server 2000 に関しては、サポートされているネイティブの 64 ビット アーキテクチャは Intel の IA64 のみです。そのため、SQL Server 2000 について 64 ビットのサポートという話が出たら、それは IA64 を意味します。64 ビットの IA64 でネイティブにサポートされる SQL Server 2000 は Enterprise Edition だけです。Standard Edition の SQL Server 2000 は、IA64 では動作しません。ネイティブな 64 ビット アプリケーションとしても、また、IA64 の Windows® on Windows (WOW。32 ビット アプリケーションを実行できる OS のサブシステム) を使用しても動作しません。

AMD x64 コンピュータでは、SQL Server 2000 に関して 2 つのオプションが用意されています。1 つは、32 ビット オペレーティング システムを実行する方法です。この場合、SQL Server 2000 のどのエディション、どの Service Pack (SP) でも実行することができます。もう 1 つは、64 ビット オペレーティング システムと SQL Server 2000 (どのエディションでも可) SP4 を WOW で実行する方法です。この環境では、SQL Server 2000 はネイティブ 64 ビット アプリケーションではありません。この場合の SQL Server 2000 は WOW の下で 32 ビット モードで動作しているので、64 ビット環境が存在することさえ知らないのです。

話が複雑になりますが、IA64 WOW と x64 WOW は まったく異なる 2 つのサブシステムです。IA64 では、WOW は制限された仮想アドレス空間と実際のコンピュータの命令の両方をエミュレートする必要があります。IA64 コンピュータの命令は、x86 とはまったく異なります。まったく異なるコンピュータです。SQL Server は、IA64 WOW の下でのコンポーネント (SQL Server 2000 または SQL Server 2005) の実行をまったくサポートしません。

x64 WOW の下では、話が別です。x86 と x64 のコンピュータ アーキテクチャはよく似ています。命令セットは同じ (またはほぼ同じ) なので、エミュレートする必要があるのは 32 ビット仮想アドレス空間だけであり、これについてはハードウェアが支援します。そのため、SQL Server コンポーネントには x64 WOW をサポートするものが多数あります。SQL Server 2000 SP4 や SQL Server 2005 (全エディション) などがその例です。

SQL Server 2005 へのアップグレード

Q SQL Server 2000 から SQL Server 2005 にアップグレードしようと思っています。SQL コードの変更以外に、アプリケーションをどのように変更する必要がありますか。たとえば、Microsoft® .NET Framework クライアントを使用して SQL Server 2005 に接続する場合、デスクトップ クライアントは Windows XP SP2 にアップグレードする必要がありますか。

Windows XP SP1、Visual Basic® 6.0、および .NET を使用するアプリケーションも動作させています。SQL Server Native Client、.NET Framework 2.0、Windows XP SP2 は必要ですか。つまり、アップグレードするにあたって、全般的な最低限のクライアント要件は何ですか。また、SQL Server 2005 のミラーリングを採用することにした場合、クライアント要件は変わりますか。

A ミラーリングを最大限に活用するためには、SQL Native Client (OleDb 用または ODBC 用) か ADO.NET 2.0 の SqlClient を使用する必要があります。また、プリンシパルとミラーの両方を参照するように接続文字列を変更する必要があります。

しかし、クライアントをミラー対応にするために必要なのは SQL Native Client か .NET Framework 2.0 だけです。古いクライアントでは、ミラー サーバーに接続することはできますが、プリンシパル サーバーがダウンしている場合に自動的にミラー サーバーへの接続を試行することはありません。

クライアントを変更しなくても、クライアントとサーバーの間に BIG-IP スイッチを追加して、フェールオーバー時に手動でクライアントの接続先をプリンシパル サーバーからミラー サーバーに変更することができます。また、アプリケーションのコードを変更して、プリンシパル サーバーへの接続障害が発生したときにミラー サーバーへの接続を試行するようにすることもできます。

システム要件に含まれるのは、Windows インストーラ 3.0、Microsoft Windows XP SP1 以降、Microsoft Windows 2000 SP4 以降、または Microsoft Windows Server 2003 です。要件の詳細については、「SQL Native Client と ADO の併用」、「MDAC から SQL Native Client へのアプリケーションの更新」、および「SQL Native Client のシステム要件」を参照してください。

SQL Server 2000 のメモリ不足

Q Windows Server 2003 SP1 上で実行している運用 SQL Server 2000 Enterprise Edition SP4 で、おかしなメモリ不足例外が発生します。図 1 に、ログに表示されるエラーを示します。

Figure 1 メモリ不足エラー

2006-06-23 14:41:40.72 spid77    WARNING:  Failed to reserve contiguous memory of Size= 24641536.
2006-06-23 14:41:40.85 spid77    Buffer Distribution:  Stolen=4800 Free=1744 Procedures=39391
                                Inram=0 Dirty=90621 Kept=0
                                I/O=0, Latched=99, Other=3063345
2006-06-23 14:41:40.85 spid77    Buffer Counts:  Commited=3200000 Target=3200000 Hashed=3154065
                                InternalReservation=587 ExternalReservation=0 Min Free=1024 Visible= 173320
2006-06-23 14:41:40.85 spid77    Procedure Cache:  TotalProcs=9555 TotalPages=39391 InUsePages=1031
2006-06-23 14:41:40.85 spid77    Dynamic Memory Manager:  Stolen=44191 OS Reserved=7648 
                                OS Committed=7619
                                OS In Use=7603
                                Query Plan=39088 Optimizer=0
                                General=4183
                                Utilities=140 Connection=7651
2006-06-23 14:41:40.85 spid77    Global Memory Objects:  Resource=1617 Locks=313 
                                SQLCache=1836 Replication=204
                                LockBytes=2 ServerGlobal=42
                                Xact=63
2006-06-23 14:41:40.85 spid77    Query Memory Manager:  Grants=0 Waiting=0 Maximum=94533 Available=94533
2006-06-23 14:41:40.88 spid77    Error: 17803, Severity: 20, State: 12
2006-06-23 14:41:40.88 spid77    Insufficient memory available..

サーバーには RAM が十分あります (32GB)。おかしなことに、この問題はランダムに発生するようです。ストアド プロシージャを 20 回実行した場合、このエラーが発生するのは、おそらくそのうちの 1、2 回です。

サーバーのパフォーマンス カウンタを確認すると、使用可能なメモリはたくさんあります。レイジー ライタがディスクにページングする速度が遅すぎるのでしょうか。なぜ、ストアド プロシージャを実行するときにプロセスはそれほど大量のメモリを必要とするのですか。SELECT ステートメントの数や一時テーブルの使用が原因ですか。

A この場合、コンピュータの物理メモリの容量は関係ありません。boot.ini の設定に応じて、アプリケーションには 2 GB から 3 GB の仮想アドレス空間が用意されます。仮想アドレス空間は 32 ビット システムの重要な特長です。既定では、バッファ プールは仮想アドレス空間のうち 384 MB を除いた量をすべて使用します。384 MB は、スレッド スタックや、コンポーネントからの割り当てのうち大きすぎてバッファ プールでは処理できないもの、またはバッファ プールからの割り当て方法が不明なもの (サードパーティの拡張ストアド プロシージャ、リンク サーバー、COM コンポーネントなど) のために確保されます。

表示される警告は、約 23 MB の割り当てに失敗したことを示しています。この規模の割り当てが成功するかどうかは、384 MB ブロック内の他の割り当ての数や配置によって決まります。

sp_OACreate やリンク サーバーで COM コンポーネントを使用しているかどうかを調べて、システムに読み込まれるサードパーティの拡張ストアド プロシージャを削除することも検討できます。

SQL ストアド プロシージャを実行する際にプロセスがこれほど大量のメモリを必要とするのは、ストアド プロシージャでの FOR XML 句の使用が原因である可能性が大いにあります (この記事にはストアド プロシージャを転載しませんでした)。ただし、詳細については、Process\sqlservr\Virtual Bytes を参照してください。どれだけの仮想アドレス空間がまだ使用可能か確認することができます。また、VMStat というツール (Jeffrey Richter の著書『Programming Applications for Microsoft Windows』(英語) に付属している CD-ROM に含まれます) を使用すると、仮想アドレス ブロックの最大サイズを調べることができます。レイジー ライタはこの割り当てに関与しません。この割り当てに使用されるメモリ領域はバッファ プール内のものではありません。

**技術的知識を提供してくれたマイクロソフトの次の IT Pro に感謝します。**Ramon Arjona、Stephen Borg、Sandu Chirica、Robert Djabarov、Guillaume Fourrat、Osamu Hirayama、Alejandro Mihanovich、Maxwell Myrick、Uttam Parui、Shashi Ramaka、Gavin Sharpe、Vijay Sirohi、Jimmie Thompson、Madhusudhanan Vadlamaani、Jian Wang、Dave Wickert。

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