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、接続の許可が必要です。未導入の場合は先に環境を用意してください。業務用かどうか判断できない接続先では実行せず、管理者に学習先を確認します。
- SSMSを起動し、接続画面で管理者から指定された学習用のサーバー名と認証方式を選んで接続します。
- オブジェクト エクスプローラーで学習先の接続を選び、「新しいクエリ」を開きます。ここがSQLを入力する画面です。
- 画面上部のデータベース選択をtempdbにします。tempdbは、一時データなどを置くシステムのデータベースです。
- 以降は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はトランザクション内の変更を取り消します。一時テーブルの作成はトランザクションの前なので、この例では表自体は残り、接続終了で整理されます。途中停止の場合も、未完了の処理を残して別の作業へ進みません。
- 最後まで実行できた場合は、Amountが100、OpenTransactionsが0であることを確認します。
- 実行中に止める場合は、クエリ画面のツールバーにある「クエリ実行のキャンセル」を選び、実行が終了するまで待ちます。取消し要求はすぐ完了するとは限りません。
- 対象の学習用クエリの本文上で右クリックし、「接続」→「切断」を選びます。現在のクエリの接続だけが対象です。オブジェクト エクスプローラーの切断や、すべてのクエリを切断する操作と区別してください。
- 未完了トランザクションの警告が出たら文面を読み、変更を確定せず取り消して切断する選択をします。コミットによる確定は選びません。選択の意味が判断できない場合は終了操作を中止し、この学習用接続の始末を管理者へ確認します。
- SQL文をファイルへ保存する質問は、データベースの変更を確定する質問とは別です。必要ならSQLを保存します。接続を切断できたことを確認してクエリのタブを閉じます。
- やり直す場合は別の新しいクエリを開いて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の調査へ進む場合も、先に対象データベースと必要な権限を確認します。ここで得た小さな結果を本番環境の性能の証拠には使わず、調べたい動作に対応した別の検証を用意してください。
