SQL Serverの内部構造を理解してみた

Posted at 2024-11-16

お久しぶりの投稿でございます。kitonです。
SQL Serverについて勉強する機会があったので、アウトプット兼ねて記事を書いてみようと思います。
まずはSQL Serverの内部構造を理解しようと思いまして、今回はこちらの本を先輩様からお借りして完読させていただきました。
↓↓

~絵で見てわかるSQL Serverの仕組み〜
著者：平山　理

読んでみた感想

本のタイトル記載通り「絵でみてわかる」これです！
私自身アプリケーションでSQLクエリやインデックスは意識して開発していましたが、内部構造やデータ構造のような基礎的なことがあまりわかっていなかったのでとても勉強になりました。

スループットを上げる、効率良くデータを取得・保存する仕組みはアプリケーションでも当然意識しますが、SQL Serverでも様々な工夫がされているんだなと発見がありました。

SQLクエリの書き方やインデックスのベストプラクティス的なことは触れておりませんが、SQL Serverの基本的な構造を理解したい方にはおすすめ本の１つです！

下記はこの本を読んでみて私なりに気づきや理解した内容を一部抜粋して記載しております。
※ほぼ個人的な備忘的な内容となっておりますので、本の内容を全て網羅できているわけではないのでご了承ください。

マルチスレッド処理：CPUを並列で処理させて処理効率を上げる
コンテキストスイッチ：CPUの使用時間（タイムスライス）を設けて、別の処理をCPUに割り当てる
※ただし、割り当て回数が多くなるほど負荷は高くなる。
SQL OSスケジューラ：CPU管理はWindowsスケジューラで行っているが、SQL Server内の処理（クエリのロック獲得・解放）までは管理できないので、独自のコンポーネントを開発した（とわいえ、SQL ServerのプロセスはWindowsスケジューラの支配下であることは忘れてはいけない。）

トランザクションログファイル
- データに対して実行した更新内容を記録
- 先行書き込みログ：データファイル操作前にログファイルを書き込んでいる
  → クエリ実行途中で障害発生してもデータの状態がわかるようになっている。
データファイル：その名の通りCRUDするためのデータ本体を格納

チェックポイント
- 論理書き込み：バッファキャッシュのデータ更新（物理データは更新されていない状態）
- 物理書き込み：物理ディスク上のデータ更新
- ダーティページ：論理書き込みはできていて、物理書き込みはまだできていないデータ
- チェックポイントとは、ダーティページに対して物理書き込みする手段の１つ
  ※物理ディスクのデータを直接更新するのではなく、バッファキャッシュに更新することでI/O操作を抑える
集中書き込み
- チェックポイント同様ダーティページの物理書き込み手段
- トランザクションログの大量書き込みはスループット低下、ディスク容量肥大化する問題がある
  →一括操作のトランザクションログの書き込みだけ行われるので上記問題の解決案の１つ

ページング：仮想記憶領域と物理メモリのサイズのギャップを埋めるための手段
ページアウトされたデータ取得を繰り返すことはパフォーマンスの低下になる
→Lock Page in Memory特権をSQL Serverのアカウントに付与するとページアウト対象外になる
バッファキャッシュ：SQL Serverがデータ操作する場合にバッファキャッシュからデータを使う
プランキャッシュ：クエリのコンパイル回数を減らしてパフォーマンスを向上させる
ガベージコレクションメインスレッド：不要メモリ領域を自動的に解放する機能

SNI(SQL Server Network Interface)：クライアントと通信する際にSNI層各プロトコルを抽象化している
※クライアントもSNIを使うのでインストール必要
※ ググると、「SQL Network Interfaces, error: 26」：接続エラー系が結構ヒットしますね。
送信フォーマットはTDS(Tubular Data Stream)形式でSNIに受け渡す
※TDSはアプリケーションレベルのプロトコル
SQL Serverは複数のプロトコルを待ち受けできる
SQL Server Browserサービス：クライアントが名前付きインスタンスのSQL Serverに接続するための必要な情報の問合せ先
接続情報キャッシュ：接続情報をキャッシュすることで、SQL Server Browserサービスへの問合せ回数を減らす

上記以外の内容の他にもデータ構造の原理や、バックアップ・復元や、トラブルシューティングもわかりやすく書かれておりました。
SQL Server2016から実装された「クエリストア」は実際の運用でも使ってみて記事にしたいと思います。

１つ１つの絵がわかりやすい本となっておりましたので、興味のある方はぜひ一読してみてください！
ここまで読んでいただいてありがとうございました！