SQLサーバー Archi構造（説明あり）
フィオナ・ブラウン
⚡ スマートサマリー
SQLサーバー Archiアーキテクチャはクライアント/サーバーモデルを採用しており、ネットワーク通信のためのプロトコル層、クエリ処理のためのリレーショナルエンジン、データ管理と取得のためのストレージエンジンという3つのコア層で構成されています。
MS SQL Serverはクライアント/サーバーアーキテクチャを採用しています。MS SQL Serverの処理は、クライアントアプリケーションがリクエストを送信することから始まります。SQL Serverはリクエストを受け取り、処理し、処理済みのデータで応答します。以下に示すアーキテクチャ全体について詳しく説明します。
以下の図に示すように、SQL Serverには3つの主要なコンポーネントがあります。 Archi構造:
- プロトコル層
- リレーショナル エンジン
- ストレージエンジン
プロトコル層 – SNI
SQL Serverプロトコル層（サーバーネットワークインターフェイス（SNI）とも呼ばれる）は、3種類のクライアント/サーバーアーキテクチャをサポートしています。それぞれのプロトコルは、異なるネットワークシナリオに対応しています。クエリが内部的にどのように処理されるかを理解する上で、これらのプロトコルを理解することは不可欠です。
共有メモリ
早朝の会話を想像してみてください。トムと彼の母親は、同じ場所、つまり自宅にいます。トムがコーヒーを頼むと、母親はすぐにコーヒーを出します。同様に、SQL Serverは、クライアントとサーバーが同じマシン上で動作する場合、共有メモリプロトコルを提供します。両者はネットワークのオーバーヘッドなしに、共有メモリを介して通信します。
類推： トムはクライアントに、ママはSQL Serverに、家はマシンに、そして口頭でのコミュニケーションは共有メモリプロトコルにそれぞれ対応します。
設定に関する注意事項： In SQL Management Studioローカル接続の「サーバー名」オプションには、「.」、「localhost」、「127.0.0.1」、または「Machine\Instance」を指定できます。
TCP / IP
ここで、トムが10km離れた場所にあるコーヒーショップでコーヒーを飲みたいとしましょう。トムは自宅にいて、コーヒーショップは賑やかな市場にあります。彼らは携帯電話ネットワークを介して通信します。同様に、SQL Serverは TCP / IPプロトコル クライアントとSQL Serverがネットワーク経由で接続された別々のマシン上にある場合。
類推： トムはクライアントに対応し、コーヒーショップはSQL Serverに対応し、自宅と市場は遠隔地に対応し、携帯電話ネットワークはTCP/IPプロトコルに対応します。
設定に関する注意事項： SQL Management Studioでは、TCP/IP接続の「サーバー名」オプションは「サーバーのマシン\インスタンス」に設定する必要があります。SQL Serverは、TCP/IP接続にデフォルトでポート1433を使用します。
名前付きパイプ
最後に、トムは隣人のシエラから緑茶をもらいたいと思っています。彼らは隣人同士で同じ場所に住んでおり、内部ネットワークを介して通信します。同様に、SQL Serverは、クライアントとサーバーがローカルエリアネットワーク（LAN）を介して接続されている場合、名前付きパイププロトコルを提供します。
類推： Tomはクライアントに、SierraはSQL Serverに、隣人であることはLANに、そしてネットワーク内通信は名前付きパイププロトコルにそれぞれ対応します。
設定に関する注意事項： 名前付きパイプはデフォルトでは無効になっており、SQL構成マネージャーを使用して有効にする必要があります。
TDSとは何ですか？
クライアント/サーバーアーキテクチャの3つのタイプが明確になったところで、TDSについて見ていきましょう。
- TDS は表形式データ ストリームの略です。
- これら3つのプロトコルはすべてTDSパケットを使用します。
- TDSはネットワークパケットにカプセル化されており、クライアントマシンからサーバーマシンへのデータ転送を可能にする。
- TDSは最初にSybaseによって開発され、現在は Microsoft.
次の表は、3つのSQL Server接続プロトコルを比較したものです。
| 機能 | 共有メモリ | TCP / IP | 名前付きパイプ |
|---|---|---|---|
| ネットワーク範囲 | 同じマシン | リモート（WAN/インターネット） | LANのみ |
| デフォルトのポート | 無し | 1433 | 445 |
| パフォーマンス | 最速（ネットワークオーバーヘッドなし） | 良好（WAN向けに最適化済み） | 良好（LAN向けに最適化済み） |
| デフォルトで有効 | はい | はい | いいえ |
| 最適な使用例 | 現地での開発とテスト | 生産現場へのリモートアクセス | 信頼できるLAN環境 |
プロトコル層がネットワーク通信を処理した後、SQL Serverアーキテクチャにおける次のステップは、クエリ自体の処理です。ここでリレーショナルエンジンが役割を担います。
リレーショナル エンジン
リレーショナルエンジンはクエリプロセッサとも呼ばれます。クエリが何を実行する必要があるか、そしてどのようにすれば最も効率的に実行できるかを判断するSQL Serverコンポーネントが含まれています。ストレージエンジンからデータを要求し、返された結果を処理することで、ユーザーのクエリを実行する役割を担います。
アーキテクチャ図に示すように、リレーショナルエンジンには主に3つの構成要素があります。
CMDパーサー
プロトコル層から受信したデータは、リレーショナルエンジンに渡されます。CMDパーサーは、クエリデータを受け取る最初のコンポーネントです。その主な役割は、クエリの構文エラーと意味エラーをチェックし、クエリツリーを生成することです。
構文チェック: 他のプログラミング言語と同様に、SQL Serverにも定義済みのキーワードと文法規則があります。SELECT、INSERT、UPDATEなど、多くのキーワードが定義済みキーワードリストに含まれています。CMDパーサーは、入力がこれらの規則に従っているかどうかを検証します。ユーザーの入力が想定される構文から逸脱している場合、パーサーはエラーを返します。
例： ロシア人が日本のレストランに入ってロシア語で注文する場面を想像してみてください。店員は日本語しか理解できないため、注文を処理できません。同様に、ユーザーが「SELECT」の代わりに「SELECR」と入力した場合、CMDパーサーはキーワードを認識できないためエラーを返します。
セマンティックチェック: これはノーマライザーによって実行されます。ノーマライザーは、クエリ対象の列名、テーブル名、その他のオブジェクトがスキーマに実際に存在するかどうかを確認します。存在する場合は、ノーマライザーはそれらをクエリにバインドします。このプロセスはバインディングとも呼ばれます。ユーザークエリにビューが含まれている場合、ノーマライザーはそれを内部に格納されているビュー定義に置き換えます。
例： Running: SELECT * from USER_ID テーブルUSER_IDがデータベースに存在しない場合、パーサーは意味チェック中にエラーをスローします。
クエリ ツリーを作成します。 このステップでは、クエリを実行するさまざまな方法を表す複数の実行ツリーを生成します。すべてのツリーは、同じ目的の出力を生成します。
オプティマイザ
オプティマイザは、ユーザーのクエリに対して実行プランを作成します。このプランによって、クエリの実行方法が決定されます。すべてのクエリが最適化されるわけではありません。最適化は、SELECT、INSERT、DELETE、UPDATEなどのDML（データ変更言語）コマンドに適用されます。CREATEやALTERなどのDDLコマンドは最適化されず、内部形式にコンパイルされます。
クエリのコストは、CPU使用率、メモリ使用量、入出力要件などの要素に基づいて計算されます。オプティマイザの役割は、必ずしも最適な実行プランを見つけることではなく、最もコスト効率の良い実行プランを見つけることです。
例： オンライン銀行口座を開設したいと想像してみてください。ある銀行では最大2日かかります。他に20の銀行のリストがあり、それらの銀行ではもっと早く開設できるかもしれません。20の銀行すべてを検索しても、より速い選択肢が見つかるとは限らず、検索自体にも時間がかかります。最初の銀行を選んだ方が良かったでしょう。同様に、SQLオプティマイザは、クエリの実行時間を最小限に抑えるために、網羅的かつヒューリスティックなアルゴリズムを使用します。
オプティマイザは3つのフェーズで探索を行います。
フェーズ0：些細な計画の探索
これは最適化前の段階です。クエリによっては、実用的な実行プランが1つしか存在しない場合があり、これを「単純実行プラン」と呼びます。それ以上検索しても、追加コストがかかるだけで同じ実行プランが見つかるだけなので、それ以上検索する必要はありません。
フェーズ1：トランザクション処理プランの検索
これには、単純なプランと複雑なプランの両方の検索が含まれます。単純なプランの検索では、列とインデックスデータの統計分析が使用され、通常はテーブルごとに1つのインデックスに限定されます。単純なプランが見つからない場合は、テーブルごとに複数のインデックスを含む、より複雑な検索が実行されます。
フェーズ2：並列処理と最適化
前述の戦略で適切な実行計画が得られない場合、オプティマイザはマシンの処理能力に基づいて並列処理の可能性を探ります。並列処理が不可能な場合は、残りのすべてのオプションを使用して最適な実行計画を見つける最終最適化フェーズが開始されます。
クエリ実行プログラム
クエリ実行部は、ストレージエンジン内のアクセスメソッドを呼び出します。クエリ実行部は、実行に必要なデータ取得ロジックを含む実行プランを提供します。ストレージエンジンからデータを受信すると、その結果はプロトコル層に公開され、エンドユーザーに送信されます。
リレーショナルエンジンがクエリの実行方法を決定した後、ストレージエンジンが物理データ操作を処理します。このレイヤーは、データの保存、キャッシュ、およびディスクからの取得方法を管理します。
ストレージエンジン
ストレージエンジンは、ディスクやSANなどのストレージシステムにデータを保存し、必要に応じてデータを取り出す役割を担います。ストレージエンジンの構成要素を詳しく調べる前に、データが物理的にどのように保存されるかを理解することが重要です。
データファイルとエクステント
データファイルは物理的にデータをデータページの形で保存し、各ページのサイズは8KBです。これは、 SQLサーバーデータページは論理的にエクステントにグループ化されます。オブジェクトに個別のページが直接割り当てられることはなく、代わりにエクステントを介してメンテナンスが行われます。各ページには、ページタイプ、ページ番号、使用領域、空き領域、次ページおよび前ページへのポインタなどのメタデータを含むページヘッダー（96バイト）があります。
ファイルの種類
プライマリファイル: すべてのデータベースには、プライマリファイルが1つ含まれています。このファイルには、テーブル、ビュー、トリガー、その他のオブジェクトに関連するすべての重要なデータが格納されます。拡張子は通常.mdfですが、任意の拡張子を使用できます。
二次ファイル: データベースには、複数の補助ファイルが含まれる場合と含まれない場合があります。これらはオプションであり、ユーザー固有のデータが含まれています。拡張子は通常.ndfですが、任意の拡張子を使用できます。
ログファイル: ライトアヘッドログとも呼ばれます。拡張子は.ldfです。ログファイルは、トランザクション管理、不要なインスタンスからの復旧、およびコミットされていないトランザクションのロールバックに使用されます。
ストレージエンジンは主に3つのコンポーネントで構成されています。それぞれがデータアクセスとデータ整合性の管理において特定の役割を担っています。
アクセス方法
アクセス メソッドはクエリ実行と Buffer マネージャまたはトランザクションログ。実行自体は行わず、クエリの種類を決定します。
- クエリが SELECT文（DML）渡される Buffer さらなる処理のための管理者。
- クエリが SELECT文以外のステートメント（DDLおよびDML）トランザクションマネージャに渡されます。これには主にUPDATE、INSERT、DELETEステートメントが含まれます。
Buffer マネージャー
その Buffer マネージャーは、プランキャッシュ、データ解析、ダーティページ処理といったコア機能を管理します。
プランキャッシュ
既存のクエリプラン: その Buffer マネージャは、実行プランが保存されているプランキャッシュに存在するかどうかを確認します。存在する場合は、キャッシュされたクエリプランとその関連データキャッシュが直接使用されます。
初回キャッシュプラン： 初回クエリ実行プランが複雑な場合、プランキャッシュに保存されます。これにより、SQL Serverが次回同じクエリを受信した際の可用性が向上します。
データ解析： Buffer キャッシュとデータストレージ
その Buffer マネージャーは必要なデータへのアクセスを提供します。データがキャッシュに存在するかどうかに応じて、2つのアプローチが可能です。
Buffer キャッシュ – ソフトパース
その Buffer マネージャーはデータを検索します Buffer キャッシュ。データが存在する場合、クエリ実行エンジンはそれを直接使用します。キャッシュからデータを取得する方が、ディスクストレージから取得するよりもI/O操作が少なくて済むため、パフォーマンスが向上します。
データストレージ – ハードパース
データが存在しない場合 Buffer キャッシュとは、必要なデータをディスク上のデータストレージから検索し、将来の使用のためにデータキャッシュに保存する仕組みです。
トランザクションマネージャー
トランザクションマネージャは、アクセスメソッドがクエリがSELECT文ではないと判断したときに呼び出されます。トランザクションマネージャは、いくつかのサブコンポーネントを通じてデータの一貫性と永続性を保証します。
ログマネージャー
ログマネージャは保持します tracシステムで行われたすべての更新は、トランザクションログに保存されたログを通じて記録されます。各ログエントリには、トランザクションIDとデータ変更レコードとともにログシーケンス番号が含まれています。このメカニズムは、 tracks はコミットおよびロールバックされたトランザクションを実行しました。
ロックマネージャー
トランザクション中、ストレージ内の関連データはロック状態になります。ロックマネージャはこのプロセスを処理し、データの一貫性と分離性を確保します。これらの特性は、ACID (Atom氷性、一貫性、分離性、耐久性)。
実行プロセス
実行プロセスは以下の手順で行われます。
- ログマネージャがログ記録を開始し、ロックマネージャが関連データをロックします。
- データのコピーは Buffer キャッシュ。
- 更新対象データのコピーはログに保持されます Buffer、すべてのイベントはデータ内のデータを更新します Buffer.
- 変更されたデータを保存するページは、 ダーティページ.
チェックポイントおよびライトアヘッドロギング
チェックポイント処理は、約 1 分に 1 回実行され、すべてのダーティ ページをディスクへの書き込み対象としてマークします。ただし、ページは最初にログ ファイルのデータ ページにプッシュされます。 Buffer ログ。この仕組みはライトアヘッドロギングとして知られています。ダーティページは、ディスクに書き込まれた後もキャッシュに残ります。
不精な Writer
SQL Server は、負荷が高く、新しいトランザクションにバッファ メモリが必要な場合、キャッシュからダーティ ページを解放します。 Writer LRU（最近使用頻度の低い）アルゴリズムに基づいて動作し、バッファプールからディスクへページをクリーンアップします。
SQL Serverがクエリをエンドツーエンドで処理する方法
各レイヤーを個別に理解することは重要ですが、それらがどのように連携して動作するかを見ることで、全体像が明確になります。クライアントアプリケーションがSQLクエリを送信すると、次のシーケンスが発生します。
その プロトコル層 共有メモリ、TCP/IP、または名前付きパイプを介してリクエストを受信し、それをTDSパケットにラップします。 リレーショナル エンジン その後、処理は引き継がれます。CMDパーサーが構文と意味をチェックし、オプティマイザが最適な実行プランを生成し、クエリ実行エンジンがデータ取得を開始します。
クエリ実行ツールは、 ストレージエンジンの SELECT クエリをルーティングするアクセス メソッド Buffer トランザクションマネージャへのマネージャおよび変更に関するクエリ。 Buffer マネージャーはプランキャッシュを確認し、 Buffer まずキャッシュを使用します（ソフトパース）。データがキャッシュされていない場合は、ディスク読み取りを実行します（ハードパース）。書き込み操作の場合、トランザクションマネージャはログマネージャ、ロックマネージャ、およびチェックポイント処理を調整して、ACID準拠を確保します。
ストレージエンジンが要求されたデータを返すと、リレーショナルエンジンが結果セットをフォーマットし、プロトコルレイヤーが同じTDSプロトコルを介してクライアントアプリケーションに結果を配信します。
SQL Server接続に適したプロトコルを選択する方法
適切なプロトコルを選択するには、クライアントとサーバー間の物理的な関係だけでなく、パフォーマンス要件も考慮する必要があります。
共有メモリを使用する クライアントアプリケーションがSQL Serverと同じマシン上で実行される場合。ネットワークオーバーヘッドが一切発生しないため、これが最速のオプションです。ローカル開発、テスト、および単一マシンでの展開に最適です。
TCP/IPを使用する クライアントとサーバーがWANまたはインターネット経由で接続された異なるマシン上にある場合に使用されます。これは、運用環境で最も一般的に使用されるプロトコルです。SQL Serverはデフォルトでポート1433でリッスンし、このプロトコルはTLSによる暗号化接続をサポートしています。
名前付きパイプを使用する クライアントとサーバーが同じ信頼できるLAN上にあり、内部ネットワークのパフォーマンスが優先される場合に有効です。名前付きパイプは既定では無効になっており、SQL Server構成マネージャーを使用して有効にする必要があります。最新の環境ではあまり一般的ではありませんが、従来のイントラネットアプリケーションでは依然として有用です。
