SQL Serverのアーキテクチャ入門:クエリ・メモリ・ログの役割

SQLと実行プラン、メモリ上のページ、ログと確定・取消しの3つの役割を整理した独自図解。変更したデータページの永続化より先に対応ログを記録することを補足。

SQL Serverで検索はできても、「SQLはどこで処理されるのか」「変更の確定と保存は何が違うのか」が分からないと、内部構造の説明を追いにくくなります。まず、SQLの処理手順、データを扱うメモリ、変更を記録するログに分けると整理できます。

この記事では、役割の比較表と4つのSQL例を対応付け、接続先の確認からエラー後の取消しまでを説明します。手順を試す場合は、学習用のSQL Serverと管理ツールのSSMS、接続の許可を用意してください。掲載結果はSQL Server 2022の実行記録に基づきます。

先に結論

  • 実行プランはSQLの処理手順、バッファープールはデータページを置くメモリ領域です。役割を分けて読みます。
  • コミットは変更の確定です。その時点で、すべてのデータページがファイルへ書き戻されるとは限りません。
  • エラー後もトランザクションが残る場合があります。掲載例では状態を確認し、ROLLBACKで変更を取り消します。

SQLを送る道具と、処理・保存する仕組みを分ける

SQLの送信、処理手順の決定、データとログの保存を分けると、各部品の役割を追えます。

SQL Serverは表形式のデータを管理するソフトウェアです。表では縦が列、横が行です。ここでいうアーキテクチャは、内部の部品と役割の組合せを指します。

データベースは表などのデータをまとめて管理する単位です。インスタンスはSQL Serverのエンジンが動作する単位で、その中に複数のデータベースを持てます。

SQLはデータの取得や変更を指示する言語です。SSMSはSQL Server Management Studioの略で、SQLを送信して結果を表示する管理ツールです。SQL Server側では、リレーショナルエンジンが命令の解釈や実行を担います。

ストレージエンジンは、行やページへのアクセスを支えます。ページはデータを一定の大きさに区切った単位で、索引は行を探すための仕組みです。バッファー管理は、ページを置くメモリを管理する働きです。

トランザクションは、まとめて確定または取消しを行う一まとまりの処理です。トランザクションログには、変更の復旧に必要な記録を残します。次の表で、それぞれの担当を見比べてください。

場所・役割 主に扱うもの 混同しないこと
SSMSなどのクライアント SQLの送信と結果表示 SSMS自体がDBを保存するわけではない
リレーショナルエンジン 解析・実行プラン・実行 計画は再利用される場合もある
ストレージエンジンとバッファー管理 行やページへのアクセス 各SELECTが物理読取りを行うとは限らない
データファイル 表や索引などのページ 1つの表に1つのmdfとは限らない
トランザクションログ 変更の復旧に必要な記録 データページの単純な別コピーではない

SQLOSは、エンジン内でメモリや処理の割当てなどを支える仕組みです。Windowsなどの基本ソフトウェアを置き換える部品ではありません。

学習用の接続先と、SQLを実行する画面を用意する

掲載する4例の実行記録は、Windows上のSQL Server 2022 Developer 16.0.1000.6を対象としています。接続はローカルのWindows認証で、プログラムから行われています。Developerは開発・テスト用の製品区分です。

説明にはSQL Server 2025向けの公式資料も用いますが、2025での実行や性能は未確認です。以下はSSMSで追試するための操作案内で、SSMSの画面操作を再現した記録はありません。

追試には、学習用のSQL ServerとSSMS、接続の許可が必要です。未導入の場合は先に環境を用意してください。業務用かどうか判断できない接続先では実行せず、管理者に学習先を確認します。

  1. SSMSを起動し、接続画面で管理者から指定された学習用のサーバー名と認証方式を選んで接続します。
  2. オブジェクト エクスプローラーで学習先の接続を選び、「新しいクエリ」を開きます。ここがSQLを入力する画面です。
  3. 画面上部のデータベース選択をtempdbにします。tempdbは、一時データなどを置くシステムのデータベースです。
  4. 以降は1例ごとに新しいクエリを開き、tempdbを選び直します。SQLのコード欄だけを先頭から末尾まで貼り付け、選択範囲がない状態でF5を押します。掲載された出力結果は貼り付けません。

最初の3例は読取りです。最後の例だけ、接続内の一時テーブルを作成・変更します。接続や証明書のエラーが出た場合は、検証の制限を解除せず、接続先と許可を管理者に確認してください。

接続先のエンジンとデータベースを確認する

最初に確認するのは、管理ツールのSSMSではなく、接続先SQL Serverの版です。

SERVERPROPERTYは、接続先エンジンの情報を返す関数です。関数は、指定に応じた値を返す仕組みです。ここではProductVersionで版、Editionで製品区分を取得します。DB_NAMEは現在選択中のデータベース名を返します。

CASTは値の型を変える指定で、ここでは表示用の文字列にそろえます。nvarcharは文字列を表す型名で、128は長さの指定です。ASの後の名前が、結果に表示される列名になります。

tempdbを選んだクエリ画面で、次のコード欄全体を実行します。結果はSSMS下部の「結果」で確認します。掲載結果では、列の区切りを縦線で表しています。

接続先のエンジンとデータベースを調べる

SELECT CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(128)) AS ProductVersion,
       CAST(SERVERPROPERTY('Edition') AS nvarchar(128)) AS Edition,
       DB_NAME() AS DatabaseName;

実行結果

ProductVersion | Edition | DatabaseName
16.0.1000.6 | Developer Edition (64-bit) | tempdb

掲載結果のProductVersionは16.0.1000.6で、先頭の16はSQL Server 2022のエンジンを表します。EditionはDeveloper Editionです。SSMS自体の版や、別の接続先の版を示す値ではありません。

DatabaseNameがtempdbなら、この記事で使うデータベースを選べています。違う場合は画面上部でtempdbへ切り替え、再実行します。版や製品区分は環境によって異なるため、掲載値との違いだけで異常とは判断しません。

2行の入力を絞り込む処理で流れをつかむ

SQL Serverは命令の内容を確認し、必要に応じて処理手順を作成または再利用してから実行します。この手順が実行プランです。表の行が必要な場合は、ストレージエンジンへアクセスを求めます。

次の例では、VALUESがその場で2行の値を用意します。Itemsは入力に付けた名前で、ItemIdとLabelは列名です。WHEREは絞込み条件、SELECTは返す列を指定します。保存済みの表は変更しません。

2行の入力から条件に合う行を返す

SELECT ItemId, Label
FROM (VALUES (1, 'red'), (2, 'blue')) AS Items(ItemId, Label)
WHERE ItemId = 2;

実行結果

ItemId | Label
2 | blue

結果はItemIdが2、Labelがblueの1行です。2行の入力から、条件に一致する行だけが返ることを確認できます。

この結果だけでは、保存装置から実際にデータを読み込んだか、メモリ上のデータを再利用したかは判断できません。実行プランの図や処理時間も、今回の検証対象には含まれていません。

tempdbのファイル数と、ページ・ログの違いを読む

通常のディスク上に保存する表では、行や索引などを8KiBのページ単位で扱います。KiBは1024バイトを単位とする表記なので、8KiBは8192バイトです。ページはデータファイルに割り当てられ、表とファイルは1対1とは限りません。

主データファイルの推奨拡張子はmdf、任意で追加するデータファイルはndf、ログファイルはldfです。ログは変更を復旧するための記録で、データページの単純な別コピーではありません。実際の種類は、拡張子だけで判定しません。

次の例では、USEで対象をtempdbに指定します。sys.database_filesは選択中のデータベースのファイル情報です。GROUP BYで種類ごとにまとめ、COUNTで件数を数え、ORDER BYで並べます。ファイルの移動や変更は行いません。

tempdbのデータファイルとログファイルを数える

USE tempdb;
SELECT type_desc AS FileType, COUNT(*) AS FileCount
FROM sys.database_files
GROUP BY type_desc
ORDER BY type_desc;

実行結果

FileType | FileCount
LOG | 1
ROWS | 8

FileTypeはファイルの種類、FileCountはその数です。掲載結果はLOGが1、ROWSが8で、この環境のtempdbにログファイルが1つ、データファイルが8つあることを示します。

ファイル数は環境によって変わります。ROWSが8でなくても、そのことだけで異常とは判断できません。ROWSの数は表の数を示すものではなく、ほかのデータベースの構成を表すものでもありません。

メモリ上の変更・ログの保存・コミットを区別する

コミットの完了は、すべてのデータページの書戻しが終わったという意味ではありません。

バッファープールは、必要なデータページを置いて再利用するメモリ領域です。必要なページがあれば利用し、なければデータファイルから読み込みます。こうした再利用の仕組みをキャッシュと呼びます。全データがメモリに入るとは限りません。

メモリ上で変更されたページは、データファイルへまだ書き戻されていない場合があります。これが変更済みのページです。メモリ上の値が変わることと、保存装置への書込みが終わることを分けて考えます。

WALは先行書込みログの略で、変更済みページを永続領域へ書く前に、対応するログを先に永続化する規則です。永続化は、復旧に使えるよう、メモリ上だけでなく保存領域へ記録を残すことです。

COMMITは、トランザクションを確定する命令です。通常の完全持続性の設定では、必要なログが永続化されてから完了します。その時点で全データページの書戻しが終わるとは限りません。ログの保存を遅らせる遅延持続性の設定では、条件が異なります。

  • チェックポイントは、変更済みページを書き戻し、障害後の復旧作業を減らすための処理です。
  • ページの書戻しは、チェックポイント以外にも起こります。書戻しの時機を1つに限定しません。
  • チェックポイントや、稼働中のデータファイル・ログファイルの単純コピーを、復元可能なデータベースバックアップの代用にはしません。

ここは一次資料に基づく仕組みの説明です。今回の4例では、保存装置への書込み順、障害後の復旧、キャッシュのヒット率や速度を測定していません。

エラー後の明示ロールバックで未完了を閉じる

エラーが出ても、トランザクションが残る場合があります。この例では状態を調べ、ROLLBACKで変更を取り消します。

エラーが起きたときの取消し範囲は、エラーの種類や設定によって変わります。次の例では、値を100から80へ更新した後、条件に違反する負数を追加し、エラー後に残る状態を確認します。

新しいクエリを開いてtempdbを選び、コード欄の先頭から末尾までをまとめて実行します。CATCHの後にある、最後の一時テーブルを読み出す処理も実行対象です。結果は2組表示されます。

#ArchitectureLabは、その接続だけで使う一時テーブルです。名前の先頭の#がその目印です。Amountは整数の列です。CHECKの条件は負数を拒否します。NULLは値がない、または不明であることを表し、この比較だけでは拒否されません。NULLも禁止するにはNOT NULLという別指定が必要です。ここでは負数のエラーを観察します。既存の業務データは使いません。

NOCOUNTは行数メッセージを省く指定です。XACT_ABORT OFFは、文単位のエラー後にトランザクションが残る場合を観察するための指定で、業務用処理への推奨設定ではありません。

TRYは通常の処理、CATCHは捕捉できたエラーへの対処を囲みます。この例ではトランザクションを開始して更新し、負数の追加でエラーになった後、CATCHで状態を調べてROLLBACKによる取消しを行います。

エラー後に残る変更を明示的に取り消す

SET NOCOUNT ON;
SET XACT_ABORT OFF;
CREATE TABLE #ArchitectureLab (Amount int CHECK (Amount >= 0));
INSERT INTO #ArchitectureLab VALUES (100);
BEGIN TRY
    BEGIN TRANSACTION;
    UPDATE #ArchitectureLab SET Amount = 80;
    INSERT INTO #ArchitectureLab VALUES (-1);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber,
           XACT_STATE() AS TransactionState,
           @@TRANCOUNT AS OpenTransactions;
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
END CATCH;
SELECT Amount, @@TRANCOUNT AS OpenTransactions
FROM #ArchitectureLab;

実行結果

ErrorNumber | TransactionState | OpenTransactions
547 | 1 | 1

Amount | OpenTransactions
100 | 0

最初の結果は、エラー番号547、TransactionStateが1、OpenTransactionsが1です。CHECKの条件違反が起きても、確定できる状態のトランザクションが残っています。更新全体が自動で取り消された状態ではありません。

TransactionStateはXACT_STATEが返した状態です。0はトランザクションなし、1は確定可能、-1は確定できない状態を表します。OpenTransactionsは@@TRANCOUNTの値で、開始したままのトランザクションの数を示します。

この例は状態が0以外ならROLLBACKで取り消します。最後の結果がAmountは100、OpenTransactionsは0なら、80への変更が戻り、未完了のトランザクションも閉じたと判断できます。

途中で止めた場合も、学習用の接続を閉じて終える

最後の例は、一時テーブルの作成に加え、接続内の実行条件も変更します。結果を確認した後は、対象の学習用クエリ接続だけを切断して終えます。以下はMicrosoftの操作説明に基づく案内で、SSMS実画面での追試は未実施です。

ROLLBACKはトランザクション内の変更を取り消します。一時テーブルの作成はトランザクションの前なので、この例では表自体は残り、接続終了で整理されます。途中停止の場合も、未完了の処理を残して別の作業へ進みません。

  1. 最後まで実行できた場合は、Amountが100、OpenTransactionsが0であることを確認します。
  2. 実行中に止める場合は、クエリ画面のツールバーにある「クエリ実行のキャンセル」を選び、実行が終了するまで待ちます。取消し要求はすぐ完了するとは限りません。
  3. 対象の学習用クエリの本文上で右クリックし、「接続」→「切断」を選びます。現在のクエリの接続だけが対象です。オブジェクト エクスプローラーの切断や、すべてのクエリを切断する操作と区別してください。
  4. 未完了トランザクションの警告が出たら文面を読み、変更を確定せず取り消して切断する選択をします。コミットによる確定は選びません。選択の意味が判断できない場合は終了操作を中止し、この学習用接続の始末を管理者へ確認します。
  5. SQL文をファイルへ保存する質問は、データベースの変更を確定する質問とは別です。必要ならSQLを保存します。接続を切断できたことを確認してクエリのタブを閉じます。
  6. やり直す場合は別の新しいクエリを開いてtempdbを選び、コード欄全体を実行します。実行中や切断できていない接続で同じ一時テーブルを作り直しません。

XACT_ABORT ONは、多くの実行時エラーに対する全体の取消しに役立ちます。ただし、構文エラーなどすべてを扱う機能ではありません。

業務コードでは、エラーを呼出し元へ知らせる方法と、どの処理がトランザクションの開始・確定・取消しを担当するかも設計する必要があります。掲載例の設定やエラー処理を、そのまま業務用のひな型とは扱いません。

ACIDと、同時処理の見え方を整理する

ACIDは、トランザクションの4つの性質を表す略語です。全体を確定または取り消すこと、制約を保つこと、同時処理で見えるデータを調整すること、確定後の変更を保持することを分けて整理します。

前の例で確認した全体の取消しは原子性に関わります。一方、ログによる保存と復旧の仕組みは永続性に関わります。1つの一時テーブルの結果だけで、4つの性質すべてを検証できたとは判断しません。

  • 原子性:一まとまりの変更を、全部確定するか全部取り消すかという性質です。
  • 一貫性:制約などの整合性を保つ性質です。
  • 分離性:同時に進む処理から、どのデータが見えるかを調整する性質です。
  • 永続性:確定した変更を失わないための性質です。

分離性は、処理中にほかの利用者の読取りや書込みがすべて止まる、という意味ではありません。ロックによる競合の調整や、以前の行の状態を読む行バージョンなどが使われます。見え方は、分離レベルという設定などによって変わります。

今回の一時テーブル例は、1つの接続内だけの確認です。ほかの接続からの読取り、同時更新、障害復旧、業務上の整合性すべてを検証したものではありません。

各出力から判断できることと、未確認の範囲

実行結果は、どの接続先で何を確認したかと結び付けて読みます。接続先の版、ファイル数、検索結果、取消し後の状態は確認できますが、それだけで性能や障害への強さまでは判断できません。

物理I/Oは、ディスクなどの保存装置に対する実際の読取り・書込みです。今回の4例には、その測定は含まれていません。次の表で、各出力から判断できる範囲を確認してください。

掲載例 確認できること 確認できないこと
接続先のエンジンとデータベース 実際の接続先の版と現在のDB 別PCや別インスタンスの版
tempdbのファイル数 そのDBのROWS/LOGの数 全DBの配置や性能
2行の入力を絞り込む 条件に一致する1行が返ること 物理I/Oやキャッシュの効き方
エラー後の明示ロールバック 547、状態1、未完了1から100/0へ戻ること すべてのエラーでの自動取消し・障害復旧

実行プランやI/Oの調査へ進む場合も、先に対象データベースと必要な権限を確認します。ここで得た小さな結果を本番環境の性能の証拠には使わず、調べたい動作に対応した別の検証を用意してください。