SQL Serverのアーキテクチャを徹底解説｜全般・メモリ・データファイル・ログファイルの仕組み

これまでの記事では、SQL Serverの概要やコンピューターへのインストール方法について学びました。本記事では、SQL Serverのアーキテクチャ(内部構造)について詳しく解説していきます。

理解しやすくするために、SQL Serverのアーキテクチャは以下の4つのセクションに分けて説明します。

1. 全般アーキテクチャ(General)
2. メモリアーキテクチャ(Memory)
3. データファイルアーキテクチャ(Data file)
4. ログファイルアーキテクチャ(Log file)

それでは、それぞれのアーキテクチャの詳細を見ていきましょう。

全般アーキテクチャ(General)

SQL Serverへの要求が処理されるまでの流れは、以下の要素で構成されています。

1. クライアント(Client): リクエストが発生する場所です。
2. クエリ(Query): 高水準言語で記述されたSQL文です。
3. 論理ユニット(Logical Units): キーワード、式、演算子などの要素です。
4. ネットワークパケット(N/W Packets): ネットワークに関連するコードです。
5. プロトコル(Protocols): SQL Serverには以下の4つのプロトコルがあります。
   1. 共有メモリ(Shared Memory): ローカル接続およびトラブルシューティング用。
   2. 名前付きパイプ(Named Pipes): LAN上での接続用。
   3. TCP/IP: WAN接続用。
   4. VIA(Virtual Interface Adapter): ベンダーが指定した特殊なハードウェアが必要で、SQL Server 2012以降はサポートされていません。
6. サーバー(Server): SQLサービスがインストールされ、データベースが存在する場所です。
7. リレーショナルエンジン(Relational Engine): 実際の実行が行われる場所です。クエリ解析、最適化、実行の各コンポーネントを含みます。
8. クエリパーサー(Command Parser)とコンパイラ(Translator): クエリの構文チェックを行い、機械語に変換する役割を担います。
9. クエリオプティマイザー(Query Optimizer): クエリ、統計情報、Algebrizerツリーを入力として受け取り、「実行プラン」を出力として生成します。

1. 実行プラン(Execution Plan): 道路地図のように、クエリ実行時の一連の手順の順序を含むものです。
2. クエリエグゼキューター(Query Executor): 実行プランに基づいてクエリを段階的に実行する場所であり、ストレージエンジンと連携します。
3. ストレージエンジン(Storage Engine): ドライブやSANなどのストレージシステムに対するデータの格納・取得、データ操作、ロック、トランザクション管理を担当します。
4. SQL OS: ホストマシン(Windows OS)とSQL Serverの間に位置するレイヤーです。データベースエンジンに対するすべての操作はSQL OSによって管理されます。バッファープールによるメモリ管理、ログバッファ、デッドロック検出など、さまざまなOSサービスを提供します。
5. チェックポイント(Checkpoint): 変更されたページ(Dirty Pageと呼ばれます)をバッファーキャッシュから物理ドライブへ書き込む内部プロセスです。さらに、ログバッファから物理ファイルへログを書き込みます。Dirty Pageをドライブに書き込むことは「ハードニング」とも呼ばれます。
6. レイジーライター(Lazy Writer): Dirty Pageをドライブへ書き出す点ではチェックポイントと似ていますが、目的がまったく異なります。Lazy Writerはバッファープール内のメモリを解放するために動作します。これはSQL Serverのメモリが不足した場合に発生し、内部プロセスによって制御されるため、設定変更はできません。

SQL Serverはメモリ使用量を常時監視してリソースの可用性と競合状態を評価し、一定の空き容量を常に確保できるようにしています。リソースの競合を検出すると、Lazy Writerが起動され、一部のDirty Pageをドライブへ書き出してメモリを解放します。どのページをディスクに追い出すかはLRU(Least Recently Used:最近最少使用)アルゴリズムで決定されます。なお、Lazy Writerが頻繁に動作している場合は、メモリがボトルネックになっている可能性があります。

メモリアーキテクチャ(Memory)

メモリアーキテクチャの主な特徴は以下の通りです。

1. すべてのデータベースソフトウェアにおける基本的な設計目標の一つは、ディスクI/Oを最小限に抑えることです。ディスクの読み書きは最もリソースを消費する処理の一つだからです。
2. Windowsの仮想アドレス空間(Virtual Address Space)は、カーネルモード(OS側)とユーザーモード(SQL Serverなどのアプリケーション)で共有されます。
3. SQL Serverのユーザーアドレス空間は、MemToLeaveとBuffer Poolの2つに分けられます。
4. MemToLeave(MTL)とBuffer Pool(BPool)のサイズは、SQL Serverの起動プロセス中に決定されます。
5. 高いI/Oパフォーマンスを実現したいなら、バッファー管理(Buffer Management)が重要な要素となります。これには、データベースへのアクセス・更新を行うBuffer Managerと、データベースファイルへのI/Oを削減するBuffer Poolページの2つの仕組みが含まれます。
6. Buffer Poolは複数の部分に分かれており、最も重要なのがBuffer CacheとProcedure Cacheです。Buffer Cacheはデータページをメモリ内に保持することで、頻繁にアクセスされるデータをキャッシュから取得できるようにします。置き換え処理の際にのみドライブからデータページを読み込みます。メモリからのデータアクセスはディスクI/Oよりも高速なため、I/O回数を減らすことでパフォーマンスが最適化されます。
7. Procedure Cacheはストアドプロシージャと実行プランを保持し、実行プランの再作成回数を最小化します。Procedure Cacheの容量や動作状況はDBCC PROCCACHEコマンドで確認できます。
8. Buffer Poolのその他の構成要素は次の通りです。
   1. システムレベルのデータ構造: データベースやロックに関するインスタンスレベルのデータを保持します。
   2. Log Cache: トランザクションページの読み書き専用に確保された領域です。
   3. Connection Context: インスタンスへの各接続ごとに小さなメモリ領域があり、接続の現在の状態を記録します。これにはストアドプロシージャやユーザー定義関数のパラメータ、カーソル位置などが含まれます。
   4. Stack Space: WindowsはSQL Serverで開始された各スレッドにスタックスペースを割り当てます。

データファイルアーキテクチャ(Data file)

このアーキテクチャは以下のコンポーネントで構成されます。

ファイルグループ(File Group)

データベースファイルは、管理や分散を目的としてファイルグループにまとめることができます。1つのファイルが所属できるファイルグループは1つだけです。ログファイルはFile Groupに含めることができません。ログファイルのサイズはデータ量とは別に管理されるためです。

SQL Serverのファイルグループには「Primary」と「User-defined」の2種類があります。Primaryにはメインデータファイルと、特定のFile Groupに割り当てられていないすべてのファイルが含まれ、システムテーブルのすべてのページもPrimaryに配置されます。User-definedはユーザーが定義するファイルグループで、データベース作成時にFILEGROUPキーワードを使用して指定します。

各データベース内のいずれかのFile Groupは「既定のファイルグループ」として機能します。SQL Serverがテーブルやインデックスにページを割り当てる際(作成時にFile Groupが指定されていない場合)、そのページは既定のファイルグループに配置されます。既定のファイルグループを別のFile Groupに変更するには、db_owner固定データベースロールが必要です。

初期状態ではPrimaryが既定のファイルグループです。また、ファイルや個別のファイルグループをバックアップする場合にも、db_owner固定データベースロールが必要になります。

ファイル(File)

データベースには3種類のファイルがあります。Primary(メインデータファイル)、Secondary(補助データファイル)、Log(ログファイル)です。Primaryはデータベースの起点となるファイルで、データベース内の他のファイルを参照します。

各データベースには必ず1つのPrimaryファイルがあります。拡張子は任意に設定できますが、推奨されるのは.mdfです。Secondaryデータファイルはメインデータファイル以外のファイルで、1つのデータベースに複数持たせることも、まったく持たないことも可能です。拡張子は.ndfを推奨します。

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

データベース内のすべてのファイルの場所は、masterデータベースとデータベース自身のPrimaryファイルの両方に記録されます。ほとんどの場合、データベースツールはmasterデータベースからファイルの場所を取得します。

ファイルには論理名(Logical)と物理名(Physical)の2つの名前があります。論理名はT-SQLコマンドでファイルを参照する際に使われます。物理名はOS_file_nameであり、オペレーティングシステムの命名規則に従う必要があります。データファイルとログファイルはFATまたはNTFSファイルシステム上に配置できますが、圧縮ファイルシステム上には配置できません。1つのデータベースに含められるファイル数の上限は32,767です。

エクステント(Extent)

Extentは、テーブルやインデックスに空間を割り当てる際の基本単位です。1つのExtentは連続する8ページ、つまり64KBで構成されます。SQL ServerにはUniformとMixedの2種類のExtentがあります。Uniformは単一のオブジェクトで構成され、Mixedは最大8つのオブジェクトで構成されます。

ページ(Page)

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

1. Data: text、ntext、imageを含むすべてのデータ行。
2. Index: インデックスエントリ。
3. Text/Image: text、ntext、image型のデータ。
4. GAM: 割り当て済みエクステントに関する情報。
5. SGAM: システムレベルで割り当てられたエクステントに関する情報。
6. Page Free Space(PFS): 各ページの空き容量に関する情報。
7. Index Allocation Map(IAM): テーブルやインデックスが使用しているエクステントに関する情報。
8. Bulk Changed Map(BCM): 最後のバックアップ以降に一括操作(bulk operation)によって変更されたエクステントの情報。
9. Differential Changed Map(DCM): 最後のデータベースバックアップ以降に変更されたエクステントの情報。

ログファイルアーキテクチャ(Log file)

SQL Serverのトランザクションログは、一連のログレコードとして正しく機能します。各ログはログシーケンス番号(LSN)で識別され、そのログが属するトランザクションのIDを含んでいます。

ログにはデータの変更内容や実行された操作が記録され、データ編集前後のイメージ(before/after image)が保存されます。「前のイメージ」は操作実行前のデータのコピー、「後のイメージ」は操作実行後のデータのコピーです。

操作の復旧手順はログの種類によって異なります。

1. 論理操作が記録されている場合:
   1. 操作をやり直すには、その論理操作を再実行します。
   2. 操作を取り消すには、逆の論理操作を実行します。
2. 前後のイメージが記録されている場合:
   1. 操作をやり直すには、後のイメージを適用します。
   2. 操作を取り消すには、前のイメージを適用します。

トランザクションログには、さまざまな操作が記録されます。主な対象は以下の通りです。

1. 各トランザクションの開始と終了。
2. すべてのデータ変更(insert、update、delete)。システムストアドプロシージャへの変更や、システムテーブルを含むテーブルへのDDL(データ定義言語)コマンドも含まれます。
3. すべてのエクステントとページの割り当て・割り当て解除。
4. テーブルやインデックスの作成・削除。

ロールバック操作もログに記録されます。各トランザクションは、ロールバックの実行やエラーメッセージ表示に必要な十分なログ領域を確保します。この領域はトランザクション完了時に解放されます。

ログファイルのうち、データベース全体の復元に必要な最初のログレコードから最後のログレコードまでの部分は「アクティブログ(active log)」と呼ばれます。アクティブログの一部を切り離すことはできません。最初のログレコードのLSNは「最小復旧LSN(Min LSN)」と呼ばれます。

SQL Serverデータベースエンジンは、各物理ログファイルを複数の仮想ログファイル(Virtual Log File)に分割して管理します。仮想ログファイルのサイズは固定ではなく、物理ログファイルあたりの数にも固定値はありません。

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

size(サイズ)はログファイルの初期サイズ、growth_incrementは新しい領域が必要になるたびに追加される容量です。小さな増分を繰り返すことでログファイルが大きくなると、仮想ログファイルの数が多くなりすぎます。その結果、データベースの起動やログバックアップ・復元操作が遅くなる可能性があります。

対策としては、ログファイルには最終的に必要となるサイズに近い初期サイズと、比較的大きなgrowth_incrementを設定することをおすすめします。SQL Serverは先行書き込みログ(WAL:Write-Ahead Logging)を採用しており、関連するログがドライブに書き込まれる前にデータ変更が書き込まれることがないよう保証しています。これにより、トランザクションのACID特性が維持されます。

Algebrizerとは

ここでAlgebrizerについて少し触れておきます。Algebrizerはクエリ実行プロセスの中の一段階で、Parserの後に動作を開始します。Query Parserが正しい構文のクエリを検出すると、Algebrizerに渡され、そこで処理が始まります。Algebrizerは、クエリ内で指定または参照されたオブジェクト名や列名の妥当性を検証する役割を担います。例えば、クエリ内の列名に誤字があれば、Algebrizerがそれを検出してエラーを発生させます。また、クエリで扱われるすべてのデータ型の識別も行います。さらに、GROUP BYの妥当性や集計列が正しい位置にあるかどうかも確認します。

例えば、次のようなクエリを記述してCtrl+F5(解析のみ)を実行してもエラーは表示されません。しかしF5で実際に実行すると、Algebrizerが動作してエラーを返します。

USE AdventureWorks
GO
SELECT MakeFlag, SUM(ListPrice)
FROM Production.Product
GROUP BY ProductNumber

SQL Serverのチェックポイント

SQL Server 2012には4種類のチェックポイントがあります。

1. 自動(Automatic): 最も一般的なチェックポイントで、バックグラウンドプロセスとして実行されます。サーバー構成オプションのRecovery Intervalで指定された時間内にSQL Serverデータベースを復旧できることを保証します。
2. 間接(Indirect): SQL Server 2012で導入されたチェックポイントです。こちらもバックグラウンドプロセスとして実行されますが、特定のデータベースに対してTarget_Recovery_Timeが設定された場合にのみ動作します。特定のデータベースにTarget_Recovery_Timeが指定されると、サーバー全体のRecovery Intervalよりも優先され、そのデータベースでは自動チェックポイントが回避されます。
3. 手動(Manual): 他のSQLコマンドと同じようにCHECKPOINTコマンドを実行すると動作し、完了まで実行されます。このチェックポイントは現在のデータベースに対してのみ実行されます。Checkpoint_Durationオプションで、チェックポイント完了までの時間を指定できます。
4. 内部(Internal): ユーザーが直接制御できないチェックポイントで、以下のような状況で発生します。
   1. シャットダウンが正常に失敗しなかった場合を除き、すべてのデータベースでシャットダウンによるチェックポイントが実行される(SHUTDOWN WITH NOWAITコマンドの使用時)。
   2. 復旧モデルがFull/Bulk-loggedからSimpleに変更されたとき。
   3. データベースのバックアップ中。
   4. データベースがSimple復旧モデルの場合、ログが70%に達したとき、またはサーバーのRecovery Intervalオプションに基づいてチェックポイントプロセスが自動的に実行される。
   5. ログ/データファイルの追加・削除を行うALTER DATABASEコマンドでもチェックポイントが開始される。
   6. データベース復旧モデルがBulk-loggedで、最小ログ記録操作(minimal logging operation)が実行された場合にもチェックポイントが発生する。

アーキテクチャの理解は、SQL Serverの学習において少し難しく感じる部分かもしれません。しかしこの内部構造を把握しておけば、システム全体がどのように動作しているのか、障害が発生した際にどこに問題があるのかを把握しやすくなります。結果として、データベースとの業務が格段に行いやすくなるでしょう。

次回はManagement Studioについて学び、SQL Serverの基本コマンドへと進んでいきます。