Ms-sql-server-architecture

MS SQL Server-アーキテクチャ

理解を容易にするために、SQL Serverのアーキテクチャを次の部分に分類しました-

• 一般的なアーキテクチャ
• メモリアーキテクチャ
• データファイルのアーキテクチャ
• ログファイルのアーキテクチャ

一般的なアーキテクチャ

クライアント-要求が開始された場所。

クエリ-高レベル言語であるSQLクエリ。

論理ユニット-キーワード、式、演算子など

• N/Wパケット*-ネットワーク関連のコード。

プロトコル-SQL Serverには4つのプロトコルがあります。

• 共有メモリ（ローカル接続およびトラブルシューティング用）。
• 名前付きパイプ（LAN接続の接続用）。
• TCP/IP（WAN接続の接続用）。
• VIA-Virtual Interface Adapter（ベンダーがセットアップする特別なハードウェアが必要であり、SQL 2012バージョンからも非推奨）。

サーバー-SQLサービスがインストールされ、データベースが存在する場所。

リレーショナルエンジン-これは実際の実行が行われる場所です。 クエリパーサー、クエリオプティマイザー、クエリエグゼキューターが含まれています。

クエリパーサー（コマンドパーサー）およびコンパイラ（翻訳者）-これはクエリの構文をチェックし、クエリを機械語に変換します。

クエリオプティマイザー-入力としてクエリ、統計、および代数ツリーを取得することにより、実行プランを出力として準備します。

実行計画-ロードマップのようなもので、クエリ実行の一部として実行されるすべてのステップの順序が含まれています。

*Query Executor* -これは、実行計画の助けを借りて段階的にクエリが実行される場所であり、ストレージエンジンにも接続されます。

ストレージエンジン-ストレージシステム（ディスク、SANなど）上のデータのストレージと取得、データ操作、トランザクションのロックと管理を担当します。

*SQL OS* -これは、ホストマシン（Windows OS）とSQL Serverの間にあります。 データベースエンジンで実行されるすべてのアクティビティは、SQL OSによって処理されます。 SQL OSは、バッファプール、ログバッファ、ブロッキングおよびロック構造を使用したデッドロックの検出を扱うメモリ管理など、さまざまなオペレーティングシステムサービスを提供します。

チェックポイントプロセス-チェックポイントは、バッファキャッシュから物理ディスクにすべてのダーティページ（変更されたページ）を書き込む内部プロセスです。 これとは別に、ログバッファから物理ファイルにログレコードを書き込みます。 バッファキャッシュからデータファイルへのダーティページの書き込みは、ダーティページの強化とも呼ばれます。

これは専用のプロセスであり、特定の間隔でSQL Serverによって自動的に実行されます。 SQL Serverは、各データベースに対して個別にチェックポイントプロセスを実行します。 チェックポイントは、予期しないシャットダウンまたはシステムクラッシュまたは障害が発生した場合のSQL Serverの回復時間を短縮するのに役立ちます。

SQL Serverのチェックポイント

SQL Server 2012では、4種類の*チェックポイント*があります-

• 自動-これは、バックグラウンドでプロセスとして実行される最も一般的なチェックポイントであり、Recovery Interval-Server Configuration Optionで定義された制限時間内にSQL Serverデータベースを回復できるようにします。
• 間接-これはSQL Server 2012の新機能です。 これもバックグラウンドで実行されますが、オプションが設定されている特定のデータベースのユーザー指定の目標復旧時間を満たすためです。 特定のデータベースのTarget_Recovery_Timeが選択されると、サーバーに指定された回復間隔が上書きされ、そのようなDBでの自動チェックポイントが回避されます。
• 手動-これは、他のT-SQLステートメントと同じように実行されます。チェックポイントコマンドを発行すると、完了するまで実行されます。 手動チェックポイントは、現在のデータベースに対してのみ実行されます。 オプションのCheckpoint_Durationを指定することもできます-この期間は、チェックポイントを完了する時間を指定します。
• 内部-ユーザーとして、内部チェックポイントを制御することはできません。 などの特定の操作で発行された
• シャットダウンは、シャットダウンがクリーンでない場合（nowaitを指定したシャットダウン）を除き、すべてのデータベースでチェックポイント操作を開始します。
• 復旧モデルがFull \ Bulk-loggedからSimpleに変更された場合。
• データベースのバックアップ中。
• DBが単純復旧モデルの場合、チェックポイントプロセスは、ログが70％満杯になるか、サーバーオプション-復旧間隔に基づいて自動的に実行されます。
• data \ logファイルを追加または削除するデータベースコマンドを変更すると、チェックポイントも開始されます。
• チェックポイントは、DBの復旧モデルが一括ログ記録され、最小限のログ記録操作が実行されたときにも発生します。
• DBスナップショットの作成。
• レイジーライタープロセス-レイジーライターは、まったく異なる理由でダーティページをディスクにプッシュします。これは、バッファープールのメモリを解放する必要があるためです。 これは、SQLサーバーがメモリ不足に陥ったときに発生します。 私の知る限り、これは内部プロセスによって制御されており、設定はありません。

SQLサーバーは常にメモリ使用量を監視して、リソースの競合（または可用性）を評価します。その仕事は、常に一定量の空きスペースを確保することです。 このプロセスの一部として、そのようなリソースの競合に気付くと、Lazy Writerがトリガーされ、ダーティページをディスクに書き出すことでメモリ内の一部のページが解放されます。 どのページをディスクにフラッシュするかを決定するために、最長時間未使用（LRU）アルゴリズムを採用しています。

レイジーライターが常にアクティブな場合、メモリのボトルネックを示している可能性があります。

メモリアーキテクチャ

以下は、メモリアーキテクチャの主要な機能の一部です。

• すべてのデータベースソフトウェアの主な設計目標の1つは、ディスクの読み取りと書き込みが最もリソースを消費する操作であるため、ディスクI/Oを最小限に抑えることです。
• ウィンドウ内のメモリは、カーネルモード（OSモード）とユーザーモード（SQL Serverなどのアプリケーション）で共有される仮想アドレススペースで呼び出すことができます。
• SQL Serverの「ユーザーアドレススペース」は、MemToLeaveとバッファプールの2つの領域に分かれています。
• MemToLeave（MTL）およびバッファプール（BPool）のサイズは、起動時にSQL Serverによって決定されます。
• バッファ管理*は、I/Oの高効率を実現するための重要なコンポーネントです。 バッファ管理コンポーネントは、データベースページにアクセスして更新するバッファマネージャと、データベースファイルI/Oを削減するバッファプールの2つのメカニズムで構成されています。 *バッファプールはさらに複数のセクションに分割されます。 最も重要なものは、バッファキャッシュ（データキャッシュとも呼ばれます）およびプロシージャキャッシュです。 バッファキャッシュ*は、頻繁にアクセスされるデータをキャッシュから取得できるように、データページをメモリに保持します。 代替手段は、ディスクからデータページを読み取ることです。 キャッシュからデータページを読み取ると、メモリからデータを取得するよりも本質的に遅い必要なI/O操作の数が最小限に抑えられるため、パフォーマンスが最適化されます。
• *プロシージャキャッシュ*は、ストアドプロシージャとクエリ実行プランを保持して、クエリプランを生成する必要がある回数を最小限にします。 DBCC PROCCACHEステートメントを使用して、プロシージャキャッシュ内のサイズとアクティビティに関する情報を見つけることができます。

バッファプールの他の部分には次のものがあります-

• システムレベルのデータ構造-データベースとロックに関するSQL Serverインスタンスレベルのデータを保持します。
• ログキャッシュ-トランザクションログページの読み書きのために予約されています。
• 接続コンテキスト-インスタンスへの各接続には、接続の現在の状態を記録するための小さなメモリ領域があります。 この情報には、ストアドプロシージャとユーザー定義関数のパラメーター、カーソル位置などが含まれます。
• スタックスペース-Windowsは、SQL Serverによって開始された各スレッドにスタックスペースを割り当てます。

データファイルのアーキテクチャ

データファイルのアーキテクチャには、次のコンポーネントがあります-

ファイルグループ

データベースファイルは、割り当てと管理の目的でファイルグループにグループ化できます。 1つのファイルを複数のファイルグループのメンバーにすることはできません。 ログファイルがファイルグループの一部になることはありません。 ログ領域は、データ領域とは別に管理されます。

SQL Serverには、プライマリとユーザー定義の2種類のファイルグループがあります。 プライマリファイルグループには、プライマリデータファイルと、別のファイルグループに特に割り当てられていないその他のファイルが含まれます。 システムテーブルのすべてのページは、プライマリファイルグループに割り当てられます。 ユーザー定義ファイルグループは、create databaseまたはalter databaseステートメントでfile groupキーワードを使用して指定されたファイルグループです。

各データベースの1つのファイルグループは、デフォルトのファイルグループとして動作します。 SQL Serverが、作成時にファイルグループが指定されていないテーブルまたはインデックスにページを割り当てると、ページはデフォルトのファイルグループから割り当てられます。 既定のファイルグループをあるファイルグループから別のファイルグループに切り替えるには、db_owner固定dbロールが必要です。

デフォルトでは、プライマリファイルグループがデフォルトのファイルグループです。 ファイルとファイルグループのバックアップを個別に取得するには、ユーザーはdb_owner固定データベースロールを持っている必要があります。

ファイル

データベースには、プライマリデータファイル、セカンダリデータファイル、ログファイルの3種類のファイルがあります。 プライマリデータファイルは、データベースの開始点であり、データベース内の他のファイルを指します。

すべてのデータベースには、1つのプライマリデータファイルがあります。 プライマリデータファイルには任意の拡張子を付けることができますが、推奨される拡張子は .mdf です。 セカンダリデータファイルは、そのデータベースのプライマリデータファイル以外のファイルです。 一部のデータベースには、複数のセカンダリデータファイルがあります。 一部のデータベースには、単一のセカンダリデータファイルがない場合があります。 セカンダリデータファイルの推奨拡張子は .ndf です。

ログファイルには、データベースの回復に使用されるすべてのログ情報が保持されます。 データベースには少なくとも1つのログファイルが必要です。 1つのデータベースに複数のログファイルを作成できます。 ログファイルの推奨拡張子は .ldf です。

データベース内のすべてのファイルの場所は、マスターデータベースとデータベースのプライマリファイルの両方に記録されます。 ほとんどの場合、データベースエンジンはmasterデータベースのファイルの場所を使用します。

ファイルには、論理名と物理名の2つの名前があります。 論理名は、すべてのT-SQLステートメントでファイルを参照するために使用されます。 物理名はOS_file_nameです。OSの規則に従う必要があります。 データファイルとログファイルは、FATまたはNTFSファイルシステムに配置できますが、圧縮ファイルシステムには配置できません。 1つのデータベースには最大32,767個のファイルを含めることができます。

範囲

エクステントは、テーブルとインデックスにスペースが割り当てられる基本単位です。 エクステントは8連続ページまたは64 KBです。 SQL Serverには、均一と混合の2種類のエクステントがあります。 均一なエクステントは、単一のオブジェクトのみで構成されています。 混合エクステントは、最大8つのオブジェクトによって共有されます。

ページ数

これは、MS SQL Serverのデータストレージの基本単位です。 ページのサイズは8KBです。 各ページの先頭は、ページの種類、ページの空き容量、ページを所有するオブジェクトのオブジェクトIDなどのシステム情報を保存するために使用される96バイトのヘッダーです。 SQL Serverには9種類のデータページがあります。

• データ-テキスト、ntext、および画像データを除くすべてのデータを含むデータ行。
• インデックス-インデックスエントリ。
• Tex \ Image -テキスト、画像、ntextデータ。
• GAM -割り当てられたエクステントに関する情報。
• SGAM -システムレベルで割り当てられたエクステントに関する情報。
• ページ空き領域（PFS）-ページで利用可能な空き領域に関する情報。
• インデックス割り当てマップ（IAM）-テーブルまたはインデックスで使用されるエクステントに関する情報。
• 一括変更マップ（BCM）-最後のバックアップログステートメント以降に一括操作によって変更されたエクステントに関する情報。
• * Differential Changed Map（DCM）*-最後のバックアップデータベースステートメント以降に変更されたエクステントに関する情報。

ログファイルのアーキテクチャ

SQL Serverトランザクションログは、トランザクションログが一連のログレコードであるかのように論理的に動作します。 各ログレコードは、ログシーケンス番号（LSN）で識別されます。 各ログレコードには、それが属するトランザクションのIDが含まれています。

データ変更のログレコードは、実行された論理操作を記録するか、変更されたデータの前後の画像を記録します。 操作前イメージは、操作が実行される前のデータのコピーです。操作後イメージは、操作が実行された後のデータのコピーです。

操作を回復する手順は、ログレコードの種類によって異なります-

• 論理操作がログに記録されました。
• 論理演算をロールフォワードするために、演算が再度実行されます。
• 論理演算をロールバックするには、逆論理演算を実行します。
• 記録される前と後の画像。
• 操作を前に進めるには、変更後イメージが適用されます。
• 操作をロールバックするには、変更前イメージが適用されます。

さまざまな種類の操作がトランザクションログに記録されます。 これらの操作が含まれます-

• 各トランザクションの開始と終了。
• すべてのデータ変更（挿入、更新、または削除）。 これには、システムストアドプロシージャまたはシステムテーブルを含む任意のテーブルに対するデータ定義言語（DDL）ステートメントによる変更が含まれます。
• すべてのエクステントとページの割り当てまたは割り当て解除。
• テーブルまたはインデックスの作成または削除。

ロールバック操作もログに記録されます。 各トランザクションは、明示的なロールバックステートメントまたはエラーが発生した場合に発生するロールバックをサポートするのに十分なログスペースが存在することを確認するために、トランザクションログにスペースを確保します。 この予約済みスペースは、トランザクションが完了すると解放されます。

データベース全体で最後に書き込まれたログレコードに正常にロールバックするために存在する必要がある最初のログレコードのログファイルのセクションは、ログのアクティブな部分、またはアクティブなログと呼ばれます。 これは、データベースの完全な回復に必要なログのセクションです。 アクティブなログの一部を切り捨てることはできません。 この最初のログレコードのLSNは、最小回復LSN（最小LSN）として知られています。

SQL Serverデータベースエンジンは、各物理ログファイルを内部で複数の仮想ログファイルに分割します。 仮想ログファイルのサイズは固定されておらず、物理ログファイルの仮想ログファイルの数は固定されていません。

データベースエンジンは、ログファイルの作成中または拡張中に、仮想ログファイルのサイズを動的に選択します。 データベースエンジンは、少数の仮想ファイルを維持しようとします。 仮想ログファイルのサイズまたは数は、管理者が構成または設定することはできません。 仮想ログファイルがシステムパフォーマンスに影響を与えるのは、物理ログファイルが小さいサイズとgrowth_increment値で定義されている場合のみです。

サイズ値はログファイルの初期サイズであり、growth_increment値は新しいスペースが必要になるたびにファイルに追加されるスペースの量です。 多くの小さな増分のためにログファイルが大きくなると、多くの仮想ログファイルが作成されます。 これにより、データベースの起動が遅くなり、バックアップと復元の操作も記録されます。

必要な最終サイズに近いサイズ値をログファイルに割り当て、さらにgrowth_increment値を比較的大きくすることをお勧めします。 SQL Serverは先書きログ（WAL）を使用します。これにより、関連するログレコードがディスクに書き込まれる前に、データの変更がディスクに書き込まれないことが保証されます。 これにより、トランザクションのACIDプロパティが維持されます。