Microsoft SQL Server クエリプロセッサの内部およびアーキテクチャ

[アーティクル]
03/14/2008

Hal Berenson、Kalen Delaney

2000 年 1 月

要約 : この記事では、クライアントにおける Microsoft® SQL Server のクエリの処理、さまざまなクライアントと SQL Server の相互作用、およびクライアントの要求を処理するために SQL Server が実行する内容について説明します (26 ページ)。

はじめに

Microsoft SQL Server の内部およびアーキテクチャに関するトピックは膨大にあるため、この記事では、開発者に関係する分野に限定し、ほかの文書で完全に説明されていないトピックに焦点を合わせます。SQL Server のアーキテクチャの説明では、まず、クライアントにおける処理、さまざまなクライアントと SQL Server との相互作用、およびクライアントの要求を処理するために SQL Server が実行する内容について説明します。SQL Server のほかの機能を解説する書籍も出版されています。特に、Microsoft Press から出版されている『Inside SQL Server 7.0』(Ron Soukup、Kalen Delaney 共著) では、ストレージエンジンのメカニズムおよび動作について詳しく説明されていますが、クエリプロセッサに関する範囲が十分ではありません。この記事はそれらの説明を補足するものです。

ここに記載されている情報は、最適なアプリケーションを作成するための参考にしていただくことを目的としています。新しい理解によって、新しい見方でパフォーマンスに関する問題を考えることができます。

SQL Server はクライアント/サーバーシステムである

長い間、SQL Server はクライアント/サーバーシステムであると言われていますが、実際には、クライアント/サーバーシステムとして開発され、最初に市販されたリレーショナルデータベースシステムは Sybase DataServer であり、SQL Server はこれを基にして開発されました。これは何を意味するのでしょうか? これは単に SQL Server が 2 階層システムであることを意味するものではありません。伝統的に、2 階層システムは、クライアントアプリケーションがあるマシンで実行され、別のマシンに配置されたサーバーに要求を送信することを意味します。SQL Server の場合、クライアント/サーバーは、SQL Server の "構成要素" を意味します。クライアント API 部分は、サーバーコンポーネント自体と切り離され、プロセス構造内の離れた場所に置かれます。

伝統的な 2 階層モデルでは、クライアント部分は、大量のクライアントアプリケーションロジックおよびビジネスロジックと共にデスクトップマシン上に置かれ、データベースシステムに対して直接、要求を生成します。次に、クライアントは、要求に対する応答としてサーバーからデータを受け取ります。

同じモデルが 3 階層システムに適用されています。SQL Server は長い間 BEA の Tuxedo や Compaq の ACMSxp などのトランザクション処理モニタと共に使用されています。これは 20 ～ 30 年前の伝統的な 3 階層モデルを表します。3 階層モデルは、Microsoft の MTS や新しい COM+ 1.0 などのアプリケーションサーバーで表されているように、今日の Web ベースのアプリケーションの中心でもあります。SQL Server から見れば、3 階層ソリューションのクライアントは、中間層に置かれた一連のロジックです。中間層は、データベースシステムと直接対話します。実際のデスクトップ (thin クライアント) は、ほかのメカニズムを使用し、通常、データベースと直接対話するのではなく、中間層と直接対話します。この構造を図 1 に示します。

図 1 3 階層システムモデル

クライアントアーキテクチャ

アーキテクチャの面から考えれば、SQL Server Relational Server のコンポーネントは、クライアントが実行されている場所にそれほど関心を持ちません。つまり、SQL Server を実行しているのと同じコンピュータでアプリケーションを実行している場合も、SQL Server に関する限り、これは同じクライアント/サーバーモデルです。サーバーは、別個のマルチスレッドプロセスを実行し、クライアントがどこに配置されていても、クライアントからの要求を処理します。クライアントコード自体は、クライアントの内部で実行される別個の DLL であり、SQL Server との実際のインターフェイスは、クライアントとサーバーの間で使用される表形式のデータストリーム (TDS) プロトコルです。

"SQL Server のネイティブインターフェイスは何ですか?" といった質問を良く受けます。長い間、多くの開発者は DB-Library (Sybase が開発したクライアント API) が SQL Server のネイティブインターフェイスだと考えていたため、ODBC のようなインターフェイスに対して抵抗がありました。実際には、SQL Server Relational Server 自体はネイティブ API を持たず、SQL Server のインターフェイスは、クライアントとサーバーの間の通信ストリーム用プロトコルである TDS です。TDS は、クライアントからサーバーに送信される SQL ステートメントと、サーバーからクライアントに返送される結果セットをカプセル化します。TDS を直接処理する API は、すべて SQL Server の "ネイティブ" インターフェイスです。

図 2 で示されているクライアントのコンポーネントについて説明します。SQL Server 自体の境界を超えるため、この文書で説明しないクライアントアーキテクチャの要素もありますが、アプリケーションを作成する場合は、それらの要素について十分注意してください。これらの要素の中でで最もよく知られているのは、さまざまなオブジェクトモデルです。ASP または Microsoft Visual Basic® アプリケーションを作成する場合は、ADO を使用してデータベースシステムと対話します。ODBC や OLE-DB などの低レベルの API は直接呼び出しません。ADO は OLE-DB にマップし、RDO は ODBC にマップします。このため、このオブジェクトモデル (最も一般的に使用されるプログラミングモデルの一部) は、厳密には SQL Server のクライアントアーキテクチャの一部ではありません。さらに、SQL Server のインフラストラクチャの上のこのレベルで接続可能な追加のコンポーネントがあります。OLE-DB の Session Pooling Service Provider は、そういったコンポーネントの一例です。

図 2 クライアントアーキテクチャ

クライアントインターフェイス

SQL Server 7.0 に対してネイティブであると考えられる SQL Server のインターフェイスには、OLE-DB および ODBC の 2 つがあります。DB-Library インターフェイスも、TDS を使用するためネイティブですが、DB-Library は、サーバーでの変換が必要な古いバージョンの TDS を使用します。既存の DB-Library アプリケーションを SQL Server 7.0 で実行する場合は不利な条件が伴います。多くの新機能およびパフォーマンス拡張機能は、ODBC および OLE DB を通してのみ使用できます。SQL Server 7.0 の新しい機能をサポートするように DB-Library を更新すると、既存のアプリケーションに対する多くの互換性がなくなり、アプリケーションの変更が必要になります。ODBC は、DB-Library に代わる新しい SQL Server アプリケーション用の理想的な API として、5 年前に採用されています。このため、新しい互換性のないバージョンの DB-Library を導入することは、意味のないことです。

上の図 2 に示すように、これらのクライアント API には 3 つのコンポーネントがあります。最上部は、行セットやカーソルの表示など、API の仕様を実装します。TDS フォーマッタは、SQL ステートメントなどの実際の要求を受け取り、TDS メッセージとしてパッケージ化して、SQL Server に送信します。次に、結果を取得して、インターフェイスの実装に送信します。

また、すべてのプロバイダによって使用される共通のライブラリコードがいくつかあります。たとえば、BCP 機能は、ODBC と OLE-DB の両方で呼び出されるライブラリです。別の例には、DTC があります。また、ODBC 正規 SQL 構文は、すべてのプロバイダで共通のパラメータマーク付き CALL 構文です。

前に説明したように、DB-Library は依然として SQL Server 6.5 バージョンを使用しているといった制限を除き、TDS プロトコルは、すべての API に対して同一です。ODBC および OLE-DB は、SQL Server 7.0 との通信に SQL Server 7.0 バージョンを使用しますが、6.5 または 6.0 サーバーと通信する機能もあります。また、Net-Library というアブストラクション層があります。ここでは、クライアントとサーバーはネットワークアブストラクションインターフェイスを使用でき、IPX または TCP/IP に関して気にする必要がなくなります。基本的に Net-Librarｙのジョブは、ソフトウェアのほかの部分からネットワーク通信の低レベルの詳細を隠すことであるため、ここでは、Net-Librarｙの働きについて詳しく説明しません。

クライアントから見たサーバー

既に説明したように、クライアントが SQL Server と通信する際は、主として TDS メッセージが使用されます。TDS は単純なプロトコルです。SQL Server がメッセージを受信すると、これはイベントの発生として考えられます。最初に、クライアントは、通信に関するログインメッセージ (またはイベント) を送信し、成功または失敗の応答を受け取ります。SQL ステートメントを送信する場合、クライアントがパッケージ化して SQL Server に送信できる SQL 言語メッセージがあります。また、ストアドプロシージャ、システムプロシージャ、または (後のセクションで説明する) 擬似システムストアドプロシージャを起動する場合、クライアントは RPC メッセージを送信できます。これは SQL Server の RPC イベントに対応します。これらのうち最後の 2 つのプロシージャを起動する場合、サーバーはデータのトークンストリームで結果を返します。実際の TDS メッセージは、SQL Server コンポーネント間のプライベートな規約とみなされるため、Microsoft はこれに関して解説しません。

カタログストアドプロシージャは、クライアント/サーバーの相互作用のもう 1 つの主要部です。カタログストアドプロシージャは、ODBC の SQL Server 6.0 で初めて登場し、sp_tables や sp_columns などのプロシージャを含んでいました。ODBC および OLE-DB API は、データベースオブジェクトに関するメタデータを記述するための標準の方法を定義します。これらの標準はすべての RDBMS サーバーに対して使用可能であること、SQL Server の独自のシステムテーブルに合わせていないことを必要とします。クライアントがサーバーのシステムテーブルに複数のクエリを送信して、このメタデータの標準ビューをクライアント上に作成する代わりに、API に適切な形式で情報を返す、一連のシステムストアドプロシージャがサーバー上に作成されます。これにより、多くの重要なメタデータ要求を 1 往復で完了することができます。

ODBC 用に作成されたプロシージャは文書化されており、通常、ほかのメカニズムによって提供されないシステムテーブルからの情報を必要とする場合に役に立ちます。これにより、Transact-SQL プロシージャおよび DB-Library アプリケーションは、SQL Server のシステムテーブルに対する複雑なクエリを作成することなく、メタデータにアクセスできるようになり、アプリケーションは、Microsoft が今後システムテーブルに加える変更から隔離されます。

OLE DB は、ODBC のメタデータに類似してはいますが、ODBC とは異なる一連のスキーマ行セットです。新しいカタログストアドプロシージャのセットは、これらのスキーマ行セットを効果的に作成するために作成されました。ただし、ストアドプロシージャは以前の機能を複製するため、この新しいストアドプロシージャのセットは文書化されていません。メタデータを取得するための既存の方法がいくつかあるため、SQL Server チームは、プログラミングモデルに対して価値のないものを公開しないことにしました。

クライアントとサーバーとの相互作用に関する 3 つ目の視点があります。この視点は SQL Server 6.0 で初めて採用されましたが、情報が比較的公開されませんでした。これは、擬似システムストアドプロシージャの概念であり、SQL Server 7.0 では重要な役割を果たします。サーバー側カーソルが初めて SQL Server 6.0 用に開発されたとき、開発者たちはクライアントとサーバーの相互作用を管理する方法に関して選択できるようになりました。クライアントが追加の SQL ステートメントを指定することなく、データの行を次々に返すことができたため、行カーソルは使用可能な TDS メッセージのセットにうまく適合しませんでした。開発者が TDS プロトコルにメッセージを追加することはできましたが、非常に多くのコンポーネントを変更する必要がありました。また、SQL Server 6.0 の TDS のバージョンは、相互運用性を保証するため、Sybase のバージョンに近いものである必要があり、開発者は代わりとなるメカニズムを考え出しました。実際にはストアドプロシージャは SQL Server コードベースへのエントリポイントにすぎないのに、システムストアドプロシージャのように見える新しい (サーバー側カーソル) 機能を作成しました。これらのストアドプロシージャは、クライアントアプリケーションから標準の RPC TDS メッセージを使用して起動されます。これらは、ストアドプロシージャのようにクライアントで起動されますが、ほかのストアドプロシージャのように単純な SQL ステートメントで構成されないため、擬似システムストアドプロシージャと呼ばれます。これらの擬似システムストアドプロシージャのほとんどは非公開であり、文書化されていません。カーソルプロシージャの場合、すべての API はカーソル API モデルの "独自の" セットおよび独自のカーソル操作関数を公開します。このため、ストアドプロシージャ自体を文書化する理由はありません。Transact-SQL 言語でも、DECLARE、OPEN、FETCH などを使用してカーソルを公開する構文があるため、内部のみで使用される sp_cursor などの擬似システムストアドプロシージャを文書化する必要は、まったくありませんでした。

ODBC および OLE DB は、パラメータクエリおよび準備/実行モデルの概念を公開します。SQL Server 7.0 より前のリリースでは、これらの概念はクライアント API のコードによって実装されていました。SQL Server 7.0 では、Microsoft はこれらの概念に関する Relational Server のサポートを追加し、新しい擬似システムストアドプロシージャを通じてそのサポートを公開しました。これらの機能、およびサーバーがこれらの機能をサポートする方法については、この記事の後のセクションで説明します。sp_executesql プロシージャを通じたパラメータクエリのサポートは、特に直接 Transact-SQL および DB-Library を使用する場合に役に立ちます。また、このプロシージャは文書化されています。準備/実行用のプロシージャは、ODBC ドライバおよび OLE DB プロバイダのみで使用されます。

SQL Server と通信可能なすべてのクライアントは、TDS プロトコル、カタログストアドプロシージャ、および擬似システムストアドプロシージャといった 3 つの機能セットに基づいて構築されます。

サーバーアーキテクチャ

SQL Server (厳密には SQL Server Relational Server) は、リレーショナルエンジンおよびストレージエンジンといった 2 つの主要部で構成されているとされています。前に説明したように、ストレージエンジンについて詳細に説明した文書は数多くあるため、この記事ではリレーショナルエンジンの機能に限定して説明します。図 3 は、SQL Server のリレーショナルエンジン部分の主要概念を示しています。図中のコンポーネントは、サブシステムの 3 つのグループにまとめることができます。左側に示されているのは、クエリオプティマイザなど、クエリをコンパイルするためのコンパイラです。オプティマイザは、リレーショナルデータベースエンジンの最も神秘的な部分の 1 つですが、パフォーマンスの面では最も重要な部分でもあります。クエリオプティマイザの役割は、SQL で表記された非手続き的要求を取得して、一連のディスク I/O、フィルタ、および要求を効果的に満たすその他の手続き的ロジックに変換することです。右側に示されているのは、実行インフラストラクチャです。これは非常に小さな機能セットです。コンパイルコンポーネントがジョブを完了すると、ほんのわずかなサービスを投入することですぐに実行できるものが作成されます。

図 3 サーバーアーキテクチャ

図の中央に示されているのは、SQL マネージャと呼ばれるもので、SQL Server 内部のすべてのフローを制御します。RPC メッセージは、SQL マネージャによって処理されます。SQL Server 7.0 では、これはクライアントからの機能の呼び出しの主流となるものです。前に説明した擬似システムストアドプロシージャも、論理的に SQL マネージャの一部です。通常 TDS SQL 言語メッセージとして受信される SQL ステートメントは、コンパイル側から直接処理されます。これは、SQL Server 7.0 では前のバージョンほど一般的ではありませんが、依然としてよく使用されます。結果は、ODS を呼び出して TDS 結果メッセージを作成する実行エンジンのコンポーネントによって返されます。

その出力のほとんどは図の実行側から生成され、結果は実際には Expression Service から出力されます。Expression Services ライブラリは、データの変換、述語の評価 (フィルタ)、および算術計算を行うコンポーネントです。また、ODS 層を使用して出力結果から TDS メッセージを組み立てます。

リレーショナルエンジンに追加のサービスを提供するいくつかのコンポーネントについて簡単に説明します。これらのコンポーネントの 1 つは、CREATE TABLE、CREATE VIEW といった定義ステートメントなどのデータを処理するカタログサービスコンポーネントです。また、カタログサービスはシステムテーブルを処理して、実際には擬似テーブルであるそれらのテーブルを実体化します。カタログサービスコンポーネントは、主としてリレーショナルエンジンに配置されますが、実際にはそれらの 3 分の 1 はストレージエンジンの範囲内で動作するため、共有コンポーネントとして扱われます。

リレーショナルエンジンのもう 1 つのコンポーネントは、ファイバおよびスレッド用の SQL Server 独自の内部スケジューラである User Mode Scheduler (UMS) です。UMS には、サーバーの構成に応じてファイバまたはスレッドへの作業の割り当てをスケジュールし、SQL Server が SMP システム上のプロセッサの適切な負荷分散を可能にする非常に高度な内部メカニズムがあります。また、UMS は SQL Server が同時に多くのスレッドを実行してスラッシングを起こさないようにします。最後に、一般的によく知られているシステムプロシージャがあります。これらは論理的にはリレーショナルエンジンの一部ではありません。use sp_helptext を使用して、これらのプロシージャを定義する Transact-SQL コードを簡単に調べることができるため、これらのプロシージャはサーバーコードではありません。ただし、システムプロシージャは、システムテーブルなどの基本的なサーバー機能を、アプリケーションにより高く適切なレベルで公開することを目的としているため、サーバーの一部として扱われます。アプリケーション開発者が、インターフェイスとして使いやすく、高度なシステムプロシージャを使用した場合は、基本レベルのシステムテーブルがリリースのたびに変更されても、アプリケーションは引き続き機能します。

SQL を処理する際のクライアントとサーバー間の相互作用

クライアントアプリケーションが SQL Server と対話している際に、クライアント側で実行される内容について説明します。ODBC 呼び出しの例を以下に示します。

  SQLExecDirect(hstmt, "SELECT * FROM parts where partid = 7", SQL_NTS)

(この呼び出しにほとんど同等の呼び出しが OLE-DB にありますが、その処理はODBC 呼び出しと事実上同じであるため、ここでは説明しません。) この ODBC 呼び出しは、SQL ステートメントを取得して、実行のために SQL Server に送信します。

この特定のクエリでは、特定の part ID を持つ行に対して、parts テーブルからすべての列を選択します。これは、アドホック SQL の典型的な例です。SQL Server 7.0 より前のリリースでは、ストアドプロシージャと異なるアドホック SQL の特性の 1 つは、クエリオプティマイザによって生成されたプランがキャッシュされないことでした。クエリが受信されて、コンパイルされて実行されたら、プランは破棄されました。SQL Server 7.0 には、アドホッククエリ用のプランをキャッシュできるメカニズムがあります。このメカニズムについては後で説明します。

このステートメントが SQL Server に送信される前に、クライアントが確認しなければならないいくつかの項目があります。すべてのクライアントはいくつかのカーソルの概念を提供します。したがって、クライアントが内部で確認しなければならない項目の 1 つは、プログラマが要求する結果セットの種類またはカーソルです。最もすばやい種類は、マニュアルで "既定の結果セット" と呼ばれているものです。この種類のカーソルはファイアホースカーソルとも呼ばれ、まったくカーソルとして考えられないこともあります。SQL 要求がサーバーに送信されたら、サーバーは、クライアントへの結果の送信を開始し、クライアントがセット全体を消費するまで、結果の送信は停止されません。これは、クライアントでデータを汲み出している巨大な消防ホースのようなものです。

クライアントは、カーソルが既定の結果セットであることを確認したら、次にパラメータマーカーが存在するかどうかを確認します。ODBC でこの SQLExecDirect 呼び出し (および OLE-DB での同等の呼び出し) を使用する場合のオプションでは、7 のような特定の値を WHERE 句に入力する代わりに、次のように定数を疑問符に置き換えて、パラメータマーカーに渡すことができます。

  SQLExecDirect(hstmt, "SELECT * FROM parts where partid = ?", SQL_NTS)

パラメータの実際の値を別個に指定する必要があります。

クライアントは、この SQL ステートメントにパラメータマーカーが存在するかどうか、または SQL ステートメントが真のアドホックであり、非パラメータ SQL であるかどうかを確認する必要があります。それらは、クライアントがこのステートメントを使用して内部で実行する内容に影響を及ぼし、メッセージとして SQL Server に実際に送信される内容を決定します。疑問符が存在しない場合、クライアントが単にこの要求を SQL 言語 TDS メッセージとして送信することを希望していることがわかります。クライアントはファイアホースの終端に達して、結果を受け取ります。次に、クライアントは、アプリケーションのパラメータに基づいてアプリケーションに結果を返すことができます。クライアントの内部処理の選択は、ODBC または OLE DB API を通じて要求する内容に関して少しあいまいにすることができます。たとえば、アプリケーションプログラムは、既定の結果セットを直接要求しません。その代わり、読み取り専用および前方のみスクロールで、1 行ずつ提供するカーソルを ODBC で要求すると、クライアントの内部に関する限り、カーソルはファイアホースカーソル (既定の結果セット) に定義されます。

ファイアホースカーソルに関する重要な問題が 1 つあります。クライアントはすべての行を消費するまで、ほかの SQL ステートメントをサーバーに送信できません。結果セットに非常に多数の行が存在する可能性があるため、一部のアプリケーションでは、ファイアホースカーソルを使用すると正常に機能しない場合があります。前方のみカーソルは、特にこの問題を解決することを目的とした SQL Server 7.0 の新しい機能です。前方のみカーソルについては後で説明します。

SQL Server 7.0 より前のリリースでは、SQLExecDirect 呼び出しは、定数にパラメータマーカーが代入されているかどうかにかかわらず、ほとんど同じ方法で処理されました。パラメータマーカーを指定した場合、クライアントは実際には別の呼び出しで入力した値 (このセクションの最初の例の値 "7") を取得し、疑問符が存在する場所に接続しました。代入された値を持つ新しいステートメントは、アドホック SQL ステートメントとして送信されました。サーバーでパラメータ SQL を使用するメリットはありませんでした。

SQL Server 7.0 では、パラメータマーカーが SQLExecDirect で使用されている場合、SQL Server に送信される TDS メッセージは SQL 言語メッセージではありません。その代わり、sp_executesql プロシージャを使用してサーバーに送信されるため、TDS プロトコルに関する限り、TDS メッセージは RPC です。クライアントでは、結果は基本的に同じです。クライアントはデータのファイアホースを取り戻します。

このデータのファイアホースを取り戻す必要のない場合は、ブロックカーソルまたはスクロール可能カーソルを常に使用することができます。この場合、フローはかなり異なります。呼び出しは、SQL テキストに渡される sp_cursoropen エントリポイント (擬似ストアドプロシージャの 1 つ) に対して作成されます。sp_cursoropen はその SQL を操作して、スクロールできるようにロジックを追加します。また、結果を temp テーブルにリダイレクトする場合もあります。次に、カーソルへのハンドル付きの応答を返し、カーソルが現在開いていることを示します。さらに、プログラマの制御範囲外で、クライアントは sp_cursorfetch を呼び出し、1 つまたは複数の行を取得して、ユーザーのアプリケーションに返します。クライアントは sp_cursor を使用してカーソルの位置を変更したり、特定の統計を変更することもできます。カーソルの処理を終えると、クライアントは sp_cursorclose を呼び出します。

クライアントに 1 つの行を返すだけの簡単な例を説明します。既定の結果セットの場合、メッセージはクライアントとサーバー間を 1 往復します。SQL メッセージ (または sp_executesql) がサーバーに送信され、結果がクライアントに返されます。同じ行に対して (非ファイアホース) カーソルを使用する場合は、これまでの SQL Server と同様に従来の処理が行われます。オープン、フェッチ、クローズのためにそれぞれメッセージが 1 往復します。プロセスでは、既定の結果セットが使用するメッセージの 3 倍のメッセージが使用されます。SQL Server 7.0 には、同じカーソルインフラストラクチャを使用する "前方のみカーソル" と呼ばれるものがあります。このカーソルは、追加の SQL メッセージを送信する前にすべての結果行を処理することを必要としないため、ファイアホースのように動作しません。したがって、5 つの行を取得して、さらにデータが存在する場合は、サーバーに更新内容を送信することができます。

高速の前方のみカーソルは、通常のカーソルよりもサーバー上で高速に機能し、autofetch および autoclose といった 2 つの追加オプションを指定することができます。Autofetch は、オープンに対する応答メッセージの一部として最初の行セットを返します。Autoclose は、最後の行が読み込まれ後に自動的にカーソルを閉じます。このカーソルは前方のみおよび読み取り専用であるため、後方にスクロールすることはできません。SQL Server は、カーソルが閉じられたことを示す最後のデータセット付きのメッセージを渡します。高速の前方のみカーソルを使用する場合は、少数の行に対して同じ 1 往復のメッセージで通信を行うことができます。多数の行がある場合は、各行のブロックに対して追加のコストが必要です。高速の前方のみカーソルを使用する場合、カーソルの処理は既定の結果セットに非常に近くなります。

SQLExecDirect モデルのフローを図 4 に示します。

図 4 クライアント/サーバーの相互作用

準備/実行モデル

ODBCで SQLExecDirect を使用して起動される直接実行モデルのほかに、準備/実行モデルと呼ばれる別の実行モデルが ODBC および OLE-DB で公開されています。SQL を実行するための定義は、実際の SQL の実行とは別のステップとして行われます。ODBC での例を次に示します。

  SQLPrepare(hstmt, "SELECT * FROM parts where partid = ?", SQL_NTS)
SQLExecute(hstmt)

SQL Server 7.0 がリリースされる前は、準備/実行は SQL Server のネイティブモデルではありませんでした。7.0 には、ネイティブインターフェイスを提供する 2 つの擬似システムストアドプロシージャがあります。準備呼び出しのために、ここでもカーソルの種類を確認してから、sp_prepare または sp_cursorprepare を呼び出します。これらは SQL またはストアドプロシージャの処理のコンパイル部分を処理しますが、実際にプランを実行しません。その代わり、擬似システムストアドプロシージャは、プランへのハンドルを返します。これで、アプリケーションは異なるパラメータ値を渡して、再コンパイルする必要なく、繰り返し SQL を実行することができます。

SQL Server 6.5 にはネイティブインターフェイスがないため、準備および実行の 2 つのフェーズを列挙する必要がありました。これは 2 つの方法で発生させることができました。そのうちの 1 つのメソッドでは、準備フェーズは実際には発生しませんでした。SQL Server が結果の形式の説明をアプリケーションに返送することができるように、メタデータを返すための単なる部分的な実行が存在しました (このための設定オプションが存在します)。もう 1 つのメソッドでは、SQL Server は、各ユーザー専用の一時ストアドプロシージャを実際に作成しました。そのため、プランの共有は存在しませんでした。この 2 番目のメソッドでは、tempdb データベースに書き込むことができるため、ほとんどのアプリケーション開発者はオプションを無効にし、ODBC の構成ダイアログボックスのチェックボックスからこの 2 番目のメソッドを使用しました。

SQL Server 7.0 では、準備/実行メソッドは SQL Server のネイティブ機能です。SQL ステートメントは、準備が終了した後に実行されます。既定の結果セットの場合、アプリケーションプログラマは、準備操作から提供されるハンドルを使用して sp_execute を呼び出すだけで、ステートメントが実行されます。カーソルの場合、ほかのカーソル処理とまったく同様です。その上、カーソルが高速の前方のみスクロールの場合は autofetch および autoclose が可能なことなど、まったく同じ特性を持ちます。

準備/実行操作のフローを図 5 に示します。

図 5 準備/実行モデル

ストアドプロシージャを呼び出す

ストアドプロシージャは、通常、プロシージャを呼び出すための ODBC 正規 CALL 構文を使用した SQL ステートメントを SQL Server に送信することにより、ODBC および OLE-DB から起動されます。たとえば、次のようなステートメントを使用します。

  SQLExecDirect(hstm, "{call addorder(?)}", SQL_NTS)

既定の結果セットの場合、これは RPC メッセージの本来の目的であるため、単純なフローになります。クライアントは RPC メッセージをサーバーに送信して、プロシージャから返される結果を取得します。カーソルの場合は少し複雑です。クライアントは、ほかのカーソルの場合と同様に sp_cursoropen を呼び出します。Sp_cursoropen には、プロシージャに単一の SELECT ステートメントのみが含まれているかどうかを検出するためのいくつかのロジックが組み込まれています。プロシージャに単一の SELECT ステートメントのみが含まれている場合、カーソルは、その SELECT 上で開かれます。プロシージャ内の単一の SELECT ステートメンではない場合、クライアントは "このカーソルを開きましたが、ファイアホースで結果を返します。その結果をユーザーに公開できます。" といった内容のインジケータ付きのメッセージを受け取ります。

ストアドプロシージャ処理の実行のフローを図 6 に示します。

図 6 ストアドプロシージャを呼び出す

SQL マネージャ

SQL マネージャは、多くのサーバー処理おける原動力となるものです。まさにサーバーの中心に位置します。SQL マネージャは、ストアドプロシージャを実行するためのすべての要求を処理します。プロシージャキャッシュを管理し、擬似システムストアドプロシージャを保持して、アドホッククエリの自動パラメータ化に関係します。自動パラメータ化については後で簡単に説明します。SQL マネージャの処理を実際に見ることはできませんが、SQL マネージャの処理の一部を実行するいくつかの別のコンポーネントを参照することができます。ただし、SQL Server 7.0 では、これらのコンポーネントはシステムを通じて実際にクエリ処理を行う 1 つのコンポーネントに統合されています。

通常、SQL マネージャは、SQL Server に作業を要求する際に RPC メッセージを使用して起動されます。ただし、SQL 言語ステートメントが SQL メッセージを通じてサーバーに達し、エンジンのコンパイル側に進入する場合は、SQL マネージャも関係します。プロシージャまたはバッチに EXEC ステートメントがある場合、EXEC は実際に SQL マネージャを呼び出すため、SQL マネージャもエンジンのコンパイル側に関係することができます。SQL ステートメントが自動パラメータ化テンプレートを渡す場合は、クエリをパラメータ化するために SQL マネージャが呼び出されます。また、アドホッククエリをキャッシュにおく必要がある場合にも SQL マネージャが呼び出されます。

コンパイルおよび実行

ここでは、SQL Server 内のコンパイルと実行の一般的なフローについて説明します。コンパイルと実行は、SQL Server 内の 2 つの異なるフェーズであることに注意してください。SQL Server がクエリをコンパイルする場合とクエリを実行する場合の格差は、ほんのわずか (数マイクロ秒) ですが、数秒、数分、数時間、または数日になる可能性もあります。最適化を含むコンパイル中は、コンパイルの一部として使用できる知識の種類を識別する必要があります。コンパイル時に真であるものが実行時にすべて真であるとは限りません。アドホック SQL ステートメントを送信して、すぐに実行する場合でも、コンパイルと実行を 2 つの別個のアクティビティとして考える必要があります。

クエリを SQL Server で処理する準備が整ったら、SQL マネージャはキャッシュ内でクエリを検索します。クエリが見つからない場合、クエリをコンパイルする必要があります。コンパイルプロセスにはいくつかのステップがあります。最初に、解析と正規化が行われます。解析は、SQL ステートメントをコンピュータが容易に処理できるデータ構造に変換して、SQL ステートメントを実際に分析することです。解析には、構文が正しいかどうかの検証も含まれます。解析には、有効なテーブルおよび列名の確認などの作業は含まれません。これらの作業は正規化中に処理されます。正規化は、基本的に SQL 内で参照する内容をデータベースでの実際の特性に分解し、要求するセマンティクスが意味をなすかどうかを確認することを目的としています。たとえば、テーブルを実行することはセマンティクスに関して非論理的です。

次のステップは、Transact-SQL コードのコンパイルです。Transact-SQL と SQL 自体が混同されているため、Microsoft の開発者は、ユーザーが置き換えられるようにそれらを使用しています。ただし、実際には重要な違いがあります。SQL は、INSERT、UPDATE、DELETE、および SELECT といったすべての DML ステートメントです。SQL Server には、Transact-SQL および TSQL といったこれらの DML ステートメントをまとめる言語もあります。TSQL は、IF ステートメント、WHILE ステートメント、ローカル変数の宣言など、手続き的な構文を提供します。これらは、サーバー内でまったく別々に扱われます。TSQL の手続き的ロジックは、手続き的タスクの実行方法を認識したエンジンによってコンパイルされます。

SQL ステートメント自体は、伝統的なクエリオプティマイザによってコンパイルされます。オプティマイザは、セットベースの SQL ステートメントの非手続き的要求を、効率的に実行して必要な結果を返すことができるプロシージャに変換する必要があります。特に明記しない限り、これ以降のセクションでコンパイルについて説明する場合は、TSQL のコンパイルと SQL ステートメントの最適化の両方を意味します。

前のセクションで、コンパイルと実行はクエリ処理の 2 つの異なるフェーズであることを説明しましたが、オプティマイザが実行する内容の 1 つは、完全に安定した状態で最適化を行うことです。ステートメントが完全に安定した状態ではない場合、特定の条件に応じて SQL Server が再コンパイルすることは知られていますが、それは流量が一定しない状態でなければなりません。オプティマイザが、非常に飛躍的に、頻繁に変化する情報を使用する場合 (大量データの同時処理や大量のロックの保持など)、クエリは絶えず再コンパイルされる必要があり、コンパイルに時間がかかる傾向があります。たとえば、1/100 秒で実行される SQL ステートメントをコンパイルするのに、0.5 秒かかる場合もあります。SQL Server がステートメントを一度コンパイルして、各ステートメントに再びコンパイルコストを費やすことなく、何千回、何百万回とステートメントを実行できれば理想的です。

コンパイルフェーズの最終結果は、プロシージャキャッシュに置かれるクエリプランです。低コストのアドホック SQL プランは、実際にはキャッシュに入りませんが、ここでは比較的重要ではない部分です。再利用される見込みのないものでキャッシュが満たされないようにし、アドホック SQL (最大規模のクエリ) は、プランを再利用しない種類のクエリにする必要があります。コンパイルに関して既に低コストなステートメント (1/100 秒未満) の場合は、プランをキャッシュに格納して、再利用される見込みのないプランでキャッシュを散らかしても意味がありません。

プランがキャッシュに置かれると SQL マネージャはプランの実行に関するロジックに戻り、プランが変更されているかどうか、プランの再コンパイルが必要かどうかを確認します。コンパイルと実行との格差がマイクロ秒しかない場合でも、他のユーザーがデータ定義言語 (DDL) ステートメントを実行して、重大なテーブルにインデックスを追加している可能性があります。これは、ありそうもないことですが、ありえることでもあります。SQL Server は、これを明らかにする必要があります。格納されたプランを SQL Server が再コンパイルするいくつかの理由があります。インデックスの追加やドロップなどによるメタデータの変更が最もよくある理由です。サーバーは、使用されたプランがインデックスの現在の状態を反映していることを確認する必要があります。

再コンパイルのもう 1 つの理由は統計の変更です。SQL Server は、処理するデータに関する大量のヒストグラム情報を保持します。データの分布が大きく変化する場合は、最も効果的な実行のために異なるクエリプランが必要になる可能性があります。SQL Server は、データのテーブルへの挿入またはテーブルからの削除の頻度を記録します。変更の数が、テーブルのサイズに基づいて変化する特定のしきい値を超える場合、サーバーは新しい分布情報に基づいてプランを再コンパイルします。

コンパイルと実行のフローを図 7 に示します。

図 7 コンパイルと実行

実際のパラメータ値の変化によって、プランが再コンパイルされることはありません。また、使用可能なメモリやキャッシュに既に存在する必要なデータの量などの環境の変化によって、プランが再コンパイルされることもありません。

実行は単純です。また、"1 行を挿入する" または、一意インデックスを使ってテーブルから 1 つずつ選択するような非常に単純なクエリのみであれば、処理は非常に単純です。ただし、多くのクエリは、能率的に実行するために多くのメモリを必要とします。SQL Server 6.5 では、クエリが使用できるメモリを 0.5 MB または 1 MB に制限して、これを制御しました。クエリによって使用されるメモリを制御する "sort pages" という構成パラメータがありました。この名前は、基本的に大量のメモリを使用する場合に役に立つソート操作にすぎないことを意味します。どんなに大きなソートが必要になっても、SQL Server 6.5 では、特定の操作に対して 1 MB を超えるメモリを使用できないように制限されました。これは、1 GB または 2 GB のメモリが搭載されたマシンで作業をしているときに、無数の行を操作する膨大なソートの場合でも同じでした。このような複雑なクエリの場合、明らかに効果的ではありませんでした。このため、SQL Server の開発者は、個々のクエリが大量のメモリを使用できるようにする機能を SQL Server 7.0 に追加しました。

ここで別の問題が発生しました。クエリが大量のメモリを使用できるようにすると、そのメモリを必要とする多くのクエリの中でこのメモリの使用の裁定方法を決定する必要があります。SQL Server は次のようにこれを処理します。クエリプランが最適化されたら、オプティマイザはそのクエリのメモリの使用に関する 2 つの情報を決定します。最初に、オプティマイザは、効果的に実行するためにそのクエリが必要とする最低のメモリ量を選択し、その値をクエリプランと共に格納します。またオプティマイザは、クエリが利用できる最大のメモリ量も決定します。たとえば、ソートするテーブル全体が 100 MB の場合に、2 GB のメモリをソート用に提供しても役に立ちません。実際に必要なメモリは 100 MB です。そのため、クエリプランに格納される有効なメモリの最大値は 100 MB になります。

SQL Server がプランの実行を開始すると、プランが Memory Grant Scheduler と呼ばれるルーチンに渡されます。Grant Scheduler は、いくつかの興味深い処理を行います。まず、Grant Scheduler が調べているクエリに、ソートまたはハッシュ操作がプランの一部として含まれていない場合、SQL Server は、そのクエリが大量のメモリを使用しないことを認識します。この場合、Memory Grant Scheduler での待ち時間はまったくありません。プランはすぐに実行できます。このため、通常のトランザクション処理要求は、このメカニズムをまったく使用しません。Memory Grant Scheduler には、異なるサイズのハンドル要求に使用可能ないくつかのキューもあります。Memory Scheduler は、サイズの小さい要求を優先します。たとえば、20 行をソートして "上位 10 行を選択する" クエリの場合、Memory Grant Scheduler を通過する必要はありませんが、こういったクエリは非常に高速に解放され、スケジュールされます。サーバーは、そういった多くのクエリを並列処理 (同時処理) で実行する必要があります。

非常に大きなクエリがある場合は、一度に少しずつ実行して、クエリが必要なメモリを使用できるようにする必要があります。SQL Server は、4 の倍数で計算される値 (システムの CPU の数) を決定します。可能な場合、SQL Server はクエリに最小有効メモリサイズを提供して、その値の数のクエリを同時に実行します。メモリが残っている場合は、クエリのいくつかに最大有効メモリを提供することができます。SQL Server は、クエリが使用可能なメモリを最大にすると同時に、多くのクエリをシステムで実行しようとします。

最大有効メモリを使用する機能は、毎晩実行するバッチサイクルなどの操作で非常に役に立ちます。非常にサイズの大きなレポートを作成したり、インデックスの再構築を実行したりすることができます。これらの種類のクエリは、大量のメモリを使用する可能性があり、このメカニズムは必要性に動的に順応します。キューの待ち時間が少ない場合、Memory Grant Scheduler は最大要求メモリをクエリにかなり頻繁に提供します。日中に大きな処理を行う場合は、多数のクエリを同時に実行します。これらのクエリには最小のメモリが割り当てられるため、効果的に実行できますが、メモリはむしろ共有リソースです。

Scheduler が要求に対してメモリの割り当てを許可すると、プランは "開かれます"。これにより、実際の実行が開始されます。これ以降、プランは完全に実行されます。クエリが既定の結果セットモデルを使用している場合は、すべての行が生成され、すべての行がクライアントに返されるまでプランは実行されます。カーソルモデルを使用している場合は、処理が少し異なります。各クライアント要求は行の単一ブロックに向けられます。結果の各セットがクライアントに返されたら、SQL Server はクライアントからの次のセットの要求を待機する必要があります。待機中はプラン全体が "休止状態" になります。つまり、一部のロックが解除されてリソースが解放され、配置情報が保管隠されます。この情報により、次の行セットが要求されたときに、SQL Server が前の場所に戻り、実行を続けることができます。

プロシージャキャッシュ

SQL Server のプロシージャキャッシュの概念については既に何度か説明しています。SQL Server 7.0 のプロシージャキャッシュは、前のバージョンとかなり異なります。以前のリリースには、プロシージャキャッシュのサイズを制御するための 2 つの効果的な構成値がありました。そのうちの 1 つの値は SQL Server の総使用可能メモリの固定サイズを指定し、もう 1 つの値は、固定サイズのうちクエリプランの格納専用に使用される割合を指定します。また、前のバージョンでは、アドホック SQL ステートメント用のクエリプランはキャッシュに格納されず、ストアドプロシージャ用のプランのみが格納されました。SQL Server 7.0 では、特に指定しない限り、メモリの合計サイズは動的で、クエリプラン用に使用される領域も非常に流動性があります。

クエリを処理する際に SQL Server 7.0 が確認する項目の 1 つは、コンパイルに関してアドホックであり低コストであるかどうかです。クエリがアドホックであり低コストである場合、SQL Server はクエリをまったくキャッシュしません。より高価なプランやメモリ外のデータページを強要するよりも、これらのプランを将来再コンパイルしたほうが低コストです。コンパイルに関してクエリがアドホックまたは低コストではない場合、SQL Server は、バッファキャッシュからメモリを取得して、そのキャッシュにプランを保存します。バッファキャッシュは、SQL Server 7.0 でサーバーが必要とする 99% のメモリの唯一のソースであるため、メモリはバッファキャッシュから取得されます。SQL Server が大量のメモリをオペレーティングシステムから直接割り当てる特殊なケースがいくつかありますが、それらは非常に珍しいケースです。

プランは、クエリをコンパイルして実際に作成するコストを反映したコスト要因と一緒にキャッシュに保存されます。アドホックプランの場合、SQL Server はコストを 0 に設定します。これは、プロシージャキャッシュからすぐに追い出すことができることを意味します。アドホック SQL の場合、再利用されるかもしれないという危険を冒すことになりますが、その可能性はほとんどありません。システムにメモリ圧力が存在する場合、アドホックステートメント用のプランを最初に処理する必要があります。このため、アドホッククエリ用のプランは、キャッシュから追い出される資格があるものとして開始されます。クエリがアドホックではない場合、SQL Server は、クエリをコンパイルするための実際のコストに設定します。これらのコストはディスク I/O の単位で設定されます。1 つのデータページがディスクから読み取られる場合、1 つの I/O のコストを持ちます。このプランが作成されると、統計およびクエリ自体のテキストなどの情報がディスクから読み取られます。SQL は追加の処理を実行し、その処理は I/O のコストに正規化されます。ここでプロシージャを構築するためのコストを、I/O を実行するためのコストと比較することができます。コストは、データのディスクキャッシュに割り当てられる量と比較され、ストアドプロシージャまたはあらゆる種類のクエリプランに実際に割り当てられるキャッシュの量を管理する能力に大きく関係します。コストが計算されたら、プランはキャッシュに置かれます。

図 8 は、プランのコスト計算とキャッシュへの配置のフローを示しています。

図 8 プランをキャッシュに挿入する

プランの再利用が可能な別のクエリを受け取った場合も、SQL Server はプランの種類を確認します。そのクエリがアドホックプランの場合、SQL Server はコストに 1 を加えます。アドホックプランが実際に再利用されている場合、コスト要因が増えるにつれてキャッシュに滞在する時間が長くなります。プランが頻繁に再利用される場合、実際の作成コストに達するまでコストは 1 ずつ増えていきます。つまり、コストが設定されていく限り、コストが高くなっていきます。ただし、プランは頻繁に再利用されます。同じユーザーまたは別のユーザーがまったく同じ SQL テキストの再送信を続けると、プランはキャッシュにとどまります。

クエリがアドホックではない場合 (つまり、ストアドプロシージャ、パラメータクエリ、または自動パラメータクエリの場合)、プランが再利用されるたびに作成コストと同じコストがかかります。プランが再利用される限り、プランはキャッシュに滞在します。長い間プランが再利用されない場合でも、最初の作成コストの高さに応じて、長い間キャッシュに残る可能性があります。

図 9 は、キャッシュからのプランの取得、およびコストの調整のフローを示しています。

図 9 キャッシュからプランを取得する

Lazywriter は、プランを時効にするメカニズムで、必要に応じてキャッシュからプランを削除します。Lazywriter は、実際にはストレージエンジンの一部ですが、クエリ処理メカニズムとって非常に重要であるため、ここで説明します。SQL Server 7.0 では、プランは通常のバッファキャッシュに格納されるため、Lazywriter は、クエリプランがページを管理するために使用するのと同じメモリ管理用メカニズムを使用します。Lazywriter は、システム内のすべてのバッファヘッダーを調べます。システムのメモリ圧力が非常に小さい場合、Lazywriter は非常にゆっくりと実行し、メモリ圧力が大きくなると、頻繁に実行するようになります。Lazywriter が実行されると、バッファヘッダーを調べ、そのバッファ内のページの現在のコストを調べます。コストが 0 の場合は、Lazywriter の最後の巡回以来、使用されていないことを意味し、Lazywriter はそのページを解放して、ページ I/O またはほかのプランに使用するために、システム内の空きメモリを取得します。また、バッファにプロシージャプランが含まれている場合、Lazywriter は SQL マネージャを呼び出して、後処理を行います。最後に、バッファは再利用のためのフリーリストに置かれます。

バッファに関連付けられているコストが 1 以上の場合、Lazywriter はコストを追加し、ほかのバッファの検査を続行します。コストは、再利用されることなくキャッシュに存在するプランが破棄されるまでの Lazywriter のサイクル数を示します。オブジェクトがストアドプロシージャの場合に SQL マネージャを呼び出すステップを除き、このアルゴリズムは、キャッシュ内のプランとキャッシュ内のデータまたはインデックスを区別しません。Lazywriter は、プロシージャであるオブジェクトについての知識をまったく持ちません。このアルゴリズムは、ディスク I/O 用キャッシュの使用とプロシージャプラン用キャッシュの使用のバランスをうまく保ちます。

Lazywriter により、コンパイルに関して高価なものがあること、長い間使用されなくても、初期コストが高すぎるためにキャッシュに存在するものがあることがわかります。頻繁に再利用されるものは、使用されるたびにコストがリセットされ、0 になった状態を Lazywriter が確認しないため、長い間キャッシュに置かれます。

図 10 は、Lazywriter によるキャッシュの処理のフローを示しています。

図 10 Lazywriter のキャッシュ処理のフロー

クライアントの SQL を処理する

ここでは、SQL ステートメントが送信される際に発生する処理についてもう一度説明します。最初に、クライアントから RPC イベントが SQL Server に送信される場合について説明します。SQL Server は RPC イベントを受信するため、イベントが一種のパラメータ SQL (準備/実行モデルまたは EXECUTESQL) であることを認識しています。SQL Server は、キャッシュキーを作成して、この特定の SQL Seｒver テキストを識別する必要があります。SQL Server が実際のストアドプロシージャを処理する場合は、独自のキーを作成する必要はなく、単純にプロシージャの名前が使用されます。RPC 呼び出しを通じて受信する通常の SQL テキストの場合は、SQL テキストをハッシュすることで、キャッシュキーが作成されます。また、キーは一部の ANSI 設定などの特定の状態情報を反映している必要があります。すべての ANSI 設定が ON の接続とすべての ANSI 設定が OFF の接続は、同じクエリで渡される場合でも、同じプランを使用できません。処理が異なります。たとえば、ある接続が concat_null_yields_null の値を ON に設定している場合、まったく同じ SQL テキストを実行する場合でも、concat_null_yields_null を OFF に設定している接続とはまったく異なる結果を生成する可能性があります。このため、SQL Server はプランの複数のコピー (有効な ANSI 設定の異なる組み合わせに 1 つずつ) をキャッシュに保持する必要があります。オプションの有効なセットはキーの一部であり、そのキーはこのキャッシュメカニズムを使用した検索の中心に位置します。このため、SQL Server はこのキーを作成して、キャッシュ内の検索に使用します。プランがキャッシュに見つからない場合は、SQL Server は、前に説明したようにプランをコンパイルし、プランと一緒にそのキーをキャッシュに置きます。

SQL Server は、コマンドが準備操作である (つまり、プランはコンパイルされるのみで、実行されない) かどうかを確認する必要もあります。準備操作の場合、SQL Server は、ハンドルをクライアントに返します。クライアントは、プランを取得して実行するために後でこのハンドルを使用します。準備操作ではない場合、SQL Server は、プランが元のキャッシュの検索で見つかったかのように、プランを取得して実行します。

準備/実行モデルは、キャッシュの管理に複雑な要素を追加します。準備では、プランを実行するために後で使用されるハンドルを分配します。アプリケーションは、このハンドルを数時間または数日間アクティブにして、定期的にプランを実行することがあります。よりアクティブなプランまたはデータページのためにキャッシュ内の領域を確保する必要がある場合は、ハンドルを無効にすることはできません。SQL Server が実際に行うは処理は、プランをキャッシュに置くこと、また、準備操作からの SQL をよりコンパクトな領域に保存することです。メモリ圧力が存在する場合は、プランによって使用される領域を前に説明した方法で解放することができますが、準備された SQL のコピーは依然として存在します。クライアントが準備された SQL の実行を要求したときに、キャッシュにプランがない場合、SQL Server はテキストを取得して再コンパイルし、キャッシュに戻します。このように、プランを保持するために使用されるキャッシュ内のページのうち 16 KB 以上を再利用することができ、長期間保持される領域は別の場所に格納される SQL コードのうちの 100 ～ 200 バイトです。

クライアントからのステートメントを処理するほかのケースは、クエリが SQL 言語イベントとして送信される場合です。1 つの例外を除き、フローに大きな違いはありません。この場合、SQL Server は自動パラメータ化というテクニックを使用します。SQL テキストは、自動パラメータ化テンプレートに適合します。自動パラメータ化は困難な問題であるため、共有 SQL の利用が可能な過去のデータベース管理製品では、通常、このオプションは提供されていませんでした。自動パラメータ化に関する問題は、SQL Server がすべてのクエリを自動的にパラメータ化すると、一部の (またはほとんどの) クエリは、続けて送信される特定の値に対して非常に不適切なプランを取得することがあります。プログラマがパラメータマーカーをコードに追加する場合、プログラマは必要な値の範囲を認識し、SQL Server が見つけ出したプランを問題なく受け入れるとみなされます。ただし、プログラマが実際に特定の値を指定し、SQL Server がその値を変更可能なパラメータとして処理する場合、特定の値に関して機能するものとして生成されたプランが、それ以降の値に関して機能しない可能性があります。ストアドプロシージャを使用する場合、プログラマは、WITH RECOMPILE オプションをプロシージャに追加することで、新しいプランが生成されるようにすることができます。自動パラメータ化を使用する場合、新しい値に対してそれぞれ新しいプランが生成される必要があることをプログラマが示す方法はありません。

自動パラメータを行う場合、SQL Server は非常に保守的です。自動パラメータ化に関して安全なクエリのテンプレートがあり、テンプレートに一致するクエリのみに自動パラメータ化が適用されます。たとえば、等値演算子を持ち、結合なしの WHERE 句を含むクエリがあり、WHERE 句の列に一意のインデックスがあるとします。SQL Server は、複数の行が返されないことを認識し、プランが常にその一意のインデックスを使用することを認識します。SQL Server はスキャンを考慮せず、実際の値はプランを一切変更しません。このようなクエリは、自動パラメータ化に関して安全です。

クエリが自動パラメータ化テンプレートに一致する場合、SQL Server はリテラルを @p1 や @p2 などのパラメータマーカーに置き換え、sp_executesql 呼び出しのようにサーバーに送信されます。SQL Server が自動パラメータ化に関して安全ではないと指定するクエリの場合、クライアントはリテラル SQL テキストをアドホック SQL として SQL Server に送信します。

図 11 は、クライアントが SQL Server に要求を送信する際の処理のフローを示しています。

図 11 クライアントの SQL を処理する

コンパイル

ここでは、コンパイルと最適化についてさらに詳しく説明します。コンパイルプロセスでは、SQL Server はステートメントを解析し、ステートメントの内部表現であるシーケンスツリーと呼ばれるものを作成します。これは、SQL Server 6.5 から SQL Server 7.0 に継承されているデータ構造の 1 つです。次に、シーケンスツリーが正規化されます。ノーマライザの主な機能は、バインディングを実行することです。バインディングには、テーブルと列の存在の確認、およびテーブルと列に関するメタデータの読み込みが含まれます。必要な (暗黙の) 変換に関する情報もシーケンスツリーに追加されます。たとえば、クエリによって整数 10 が数値に追加される場合、SQL Server は暗黙の変換をツリーに挿入します。また、正規化ではビューへの参照がそのビューの定義に置き換えられます。最後に、正規化でいくつかの構文ベースの最適化が実行されます。ステートメントが伝統的な SQL ステートメントの場合、SQL Server はそのクエリに関するシーケンスツリーから情報を取得し、オプティマイザが非常に効果的に機能するように設定された、クエリグラフと呼ばれる特殊な構造を作成します。次に、クエリグラフは最適化され、プランが作成されます。

図 12 は、コンパイルプロセスのフローを示しています。

図 12 コンパイル

最適化

SQL Server のオプティマイザは、実際には分離した要素で構成されています。最初の要素は、簡易プラン最適化と呼ばれる非コストベースのオプティマイザです。簡易プラン最適化は、SQL ステートメントに関して実行可能なプランが 1 つしか存在しない場合、コストベースの最適化は高価であるという概念です。基本的な例は、VALUES 句を持つ INSERT ステートメントで構成されるクエリです。実行可能なプランは 1 つしか存在しません。もう 1 つの例は、すべての列が一意のカバーリングインデックスにあり、その列セットを持つインデックスがほかに存在しない場合の SELECT ステートメントです。これらは、SQL Server が単にプランを生成し、より適したプランがあるかどうか複数のプランを調べない場合の例です。簡易プランオプティマイザは、非常に明白な (通常、非常に低コストな) プランを見つけます。そのため、最も単純なクエリは、プロセスの初期に除去される傾向があり、オプティマイザは適したプランを検索するのに多くの時間を費やしません。SQL Server 7.0 では、ハッシュ結合、マージ結合、およびインデックス交差が処理テクニックのリストに追加され、実行可能なプラン数が大幅に増加したため、オプティマイザは非常に役に立ちます。

簡易プランオプティマイザがプランを見つけられない場合、SQL Server は、簡略化と呼ばれるオプティマイザの次の部分に入ります。簡略化は、交換可能なプロパティおよび再編成可能な操作を検索するクエリ自体の構文変換です。SQL Server は、定数の組み合わせおよびその他の操作を実行して、コストの検査やインデックスの分析を必要とすることなく、クエリをより効果的にすることができます。次に、SQL Server は、インデックスおよび列に関する統計情報を読み込み、最適化の最後の主要部分であるコストベースオプティマイザに入ります。

コストベースオプティマイザには 3 つのフェーズがあります。これらのコストベースフェーズの最初のフェーズは、トランザクション処理フェーズと呼ばれます。このフェーズは、トランザクション処理システムの特色である単純要求のためのプランを見つけます。これらの要求は、通常、簡易プランオプティマイザで処理されるものより複雑で、最低コストのプランを見つけるためにプランをコンパイルことを要求します。トランザクション処理フェーズが完了すると、SQL Server は、見つかった最低コストのプランのコストと内部しきい値を比較します。しきい値は、さらに最適化が必要かどうかを確認するために使用されます。プランのコストがしきい値を下回っている場合、追加の最適化を実行すると、既に見つかっているプランを実行するよりコストがかかります。そのため SQL Server は、それ以降の最適化を中止して、トランザクション処理フェーズで見つかったプランを使用します。

フェーズのしきい値と比較して、トランザクション処理フェーズで見つかったプランが高価な場合、SQL Server は 2 番目のフェーズに進みます。このフェーズは、QuickPlan フェーズと呼ばれることもあります。QuickPlan フェーズは、ある程度複雑なクエリに適した選択を含むように最適なプランの検索を拡張します。QuickPlan は、実行可能なプランの範囲を調べ、実行が終了したら最適なプランのコストと 2 番目のしきい値を比較します。トランザクション処理フェーズの場合と同様に、しきい値を下回るコストのプランが見つかった場合、最適化は中止され、そのプランが使用されます。通常、クエリが SQL Server 6.5 で保有する最適なプランは、SQL Server 7.0 でも最適なプランです。プランは、簡易プランオプティマイザまたはコストベースオプティマイザの最初の 2 つのフェーズのいずれかで見つかる傾向があります。規則は、そういった結果が得られるように故意に編成されています。プランは、単一インデックスの使用およびネストされたループ結合の使用で構成されます。

最適化の最後のフェーズは、最大限の最適化と呼ばれます。このフェーズは複雑クエリまたは非常に複雑なクエリに最適なプランを作成することを目的としています。複雑なクエリの場合、QuickPlan で作成されたプランは、より最適なプランの検索を続けるコストよりも高価であると判断されることが多く、最大限の最適化が実行されます。最大限の最適化では、2 つの別個のオプションを使用できます。QuickPlan フェーズで見つかった最適なコストが "並列処理のコストしきい値" の設定値よりも大きく、サーバーがマルチプロセッサコンピュータの場合、オプティマイザの最終フェーズでは、マルチプロセッサで同時に実行できるプランを検索します。QuickPlan フェーズからの最適なプランのコストが、設定されている "並列処理のコストしきい値" を下回る場合、オプティマイザは直列プランのみを考慮します。最大限の最適化フェーズは、可能性のあるセットを徹底的に調べることができますが、この最後のフェーズでプランを見つける必要があるため、非常に時間がかかる可能性があります。オプティマイザは、考えられるプランのコストと最適化のこの時点に到達するためのコストを比較するため、この時点でも考えられるすべてのプランを確認していない可能性があります。そのため、オプティマイザは、別の最適化を引き続き実行するためのコストを見積もります。ある時点で、オプティマイザは、検索を続けるより現在のプランを使用したほうがコストが低いこと、引き続き最適化を行うために追加のコンパイルコストを払うのはコスト効果が低いことを決定する可能性があります。この最終フェーズで処理されるようなクエリは、通常、一度限り使用されるプランを持つため、コンパイルのために余分なコストを払う可能性が少なく、最適化は、このプランがその後の実行で再利用される場合に効果があります。ただし、そういったその後の実行の可能性はほとんどありません。

プランが見つかるとプランはオプティマイザの出力になり、前に説明したように、SQL Server はプランを実行する前にすべてのキャッシュメカニズムを調べます。最大限の最適化フェーズがこのクエリの並列プランを見つけた場合、プランが複数のプロセッサで実行されるとは限らないことに注意してください。コンピュータの処理が非常に混雑して、単一のクエリを複数の CPU で実行できない場合、そのプランは単一のプロセッサを利用します。

図 13 は、オプティマイザを通じた処理のフローを示しています。

図 13 最適化

実行

クエリ処理の最終ステップは実行です。実行については、この短い段落以外で詳しく説明することはありません。実行エンジンは、オプティマイザが作成したプランを取得して、実行します。実際の実行のほかに、実行エンジンは、実行するプロセスのスレッドをスケジュールし、スレッド間の通信を提供します。

要約

前に説明したように、SQL Server の内部およびアーキテクチャには膨大なトピックがあり、この記事ですべてを説明することはできません。この記事では、SQL Server とクライアントの相互作用、および SQL Server のリレーショナルエンジンによるクライアントからの要求の処理について焦点を合わせました。SQL Server がどのようにクエリを処理するか、また、SQL Server がそれらのクエリをいつ、どのようにコンパイルまたは再コンパイルするかを知ることで、SQL Server 7.0 の能力および機能を利用した最適なアプリケーションを作成できるようになることを願っています。

Microsoft SQL Server クエリ プロセッサの内部およびアーキテクチャ

はじめに

SQL Server はクライアント/サーバー システムである

クライアント アーキテクチャ

クライアント インターフェイス