普段から業務で当たり前のように使用しているSQL Serverですが、そういえば内部構造のことは曖昧なままでよく把握しておらず、もっと理解を深めたいなと思い、下記のような本を読んでみました。
第1章 CPUソースの最適化
SQLサーバーがCPUリソースを効率的に使用するため、独自のスケジュール管理機能が実装されている。
なぜなら、SQLサーバー内のワーカーには、ロック機能や並列処理など、Windowsが持っているスケジューラには理解できない待ち状態や処理が存在するからである。
このスケジュール管理をWindowsのスケジューラが担当すると、貴重なCPUリソースを無駄に使用してしまう。そのため、より多くの要求へ迅速に対処するためにも、SQL独自のスケジューラを実装することによって、大規模なシステムでも使用可能なものになっている。
第2章 ディスクI/O操作の理解
SQLサーバーが管理するデータベースはWindowsが管理するフォルダに作成されたファイルであり、データファイルとトランザクションログファイルという2種類の物理ファイルで構成されている。
この物理ファイルへのI/Oを効率的に行うために、先行読み取りや物理書き込みの一つであるチェックポイント、集中書き込み、レイジー書き込みなどの動作が行われる。
このI/Oを実行する際に使用されているのがWin32 APIであり、目的のファイルをオープンするのにはCreateFile関数、ファイルの読み込みにはReadFileScatter関数とReadFile関数、ファイルへの書き込みにはWriteFileGather関数やWriteFile関数と、用途によって様々なAPIが使用される。
また、データベースファイルはRAIDシステムに配置されており、RAID0、RAID1、RAID5、RAID10とレベルによってそれぞれ利点が異なる。
第3章 メモリ管理
SQLサーバーは、より良いパフォーマンスを得るために様々な目的でメモリを使用している。
Windowsオペレーティングシステムの管理下で動作するプロセス（アプリケーション）は、それぞれが仮想アドレス空間を保持しており、SQLサーバーも同様に管理されている。この中で、SQLサーバーのメモリ使用量は、デフォルト設定の場合、動的に管理されるよう設定されている。
SQLサーバー自体のメモリ管理方法については、自分自身のメモリ領域を効率的に使用するため、ワークスペースという領域を割り当てて管理している。
ワークスペース内では各種コンポーネントによりメモリ管理が行われており、メモリマネージャーがメモリ管理の各作業の割り当てを行い、Fixed Size Block Allocatorがメモリを必要とするコンポーネントに素早くメモリ領域を受け渡し、Top Level Block Allocatorが最終的にフラグメント（予約領域）からメモリを獲得する。
各コンポーネントはメモリマネージャを使用して獲得したメモリ領域を、ページと呼ばれる8KBごとに区切って使用している。
メモリの監視については、DBCC MEMORYSTATUSコマンドやパフォーマンスカウンタ、動的管理ビューを使用することで行うことができる。
第4章 データベース構造の原理
データベースを構成するデータファイルの内部構造は、8KBのページ構成を基本とし、オブジェクトに新たな領域を割り当てる場合は、この8KBのページが8個で構成されたエクステントが作成される。
データファイル内のほとんどのページはデータやインデックスキーを格納することに使用されているが、ごく一部のページではGAMやSGAM、PFS、IAMといった管理情報による、データベースを効率よく管理するための割り当て情報を保持している。
また、トランザクションログファイルについては、オブジェクトの中では例外的に8KBのページ単位が使用されておらず、4〜60KBの範囲で管理されている。この中で、さらに複数の仮想ログファイルと呼ばれる単位に分割して使用しており、領域の「使用中」「未使用」といったステータスが管理されている。
データベース内の各オブジェクトへのアクセス方法や順序については、クエリオプティマイザというコンポーネントが決定する。
第5章 行ストア型デーブル
8KBの各ページには行と列を表現するために必要な情報が格納されており、そのページがテーブルに所属するひとまとまりの存在であることを示すために使用されるのが、オブジェクトIDという概念である。データベース内全てのテーブルには、固有のオブジェクトIDが割り当てられている。
また、8KBの最初から96バイトの部分までをページヘッダーという管理情報を格納するための領域として使用しており、ここにオブジェクトIDを埋め込み、これにより一連のページがテーブルとして存在が示される。
それから、ページの用途は大きく分けて2種類あり、データの内容を格納するデータページとインデックスキーを格納するインデックスページがある。
データページは、テーブルに定義された全ての列を行のイメージで格納したページであり、テーブルに格納されるべきデータそのものが格納されている。
インデックスページは、インデックスキーとして定義された列の値と管理情報が行ごとに格納されており、行の構造などを示すメタデータやインデックスキー同士の関連性などの情報が管理されている。
第6章 列ストア型オブジェクト（列ストアインデックス）
SQLサーバーは、大量のデータ処理に対応し、より良いパフォーマンスを発揮するために列ストアインデックスが実装されている。
列ストアインデックスは、列単位でデータを格納している。こうすることで、アクセスする対象をクエリで必要とされるデータにだけに限定することができ、必要となるディスクいI/O数を削減できる。更に、メモリ上に読み込むデータ量の抑制にもつながり、必要なメモリサイズも少なくて済む。
ただ、少数の結果セットを取得するような処理の場合などには、テーブルの定義に従って一行ごとにページ内へ配置される方式である行ストア型が適している。
行ストア型インデックスと列ストアインデックスを組み合わせて使用するなどして、状況によって適切な定義を行うことが大切である。
第7章 メモリ最適化オブジェクト（インメモリOLTP）
SQLサーバーにはOLTPシステムに特化したインメモリ機能群があり、これをインメモリデータベースと呼ぶ。中でも、マイクロソフトで独自機能が実装された方式がインメモリOLTPである。
インメモリOLTPには、常に全てのデータがメモリ上に存在していたり、インデックスがメモリ上にのみ存在していたりと、独自の使用が採用されている。
大量の小規模処理を効率的に実行できることに特化しているため、IoTやTempTable、ETLの中間テーブルなどのワークロードに適している。
ただ、格納されているデータ自体にテーブルと関連付けられる情報を保持していないなど、従来のディスクテーブルと工場が大きく異なっているため、留意点を踏まえた上で入念な事前検証の実施が必要である。
第8章 リレーショナルエンジンの動作
SQLサーバーでクエリが実行されるまでの一連の処理の流れや、その過程で決定されるデータアクセスの処理を行うコンポーネント群を総称して、リレーショナルエンジン、またはクエリプロセッサと呼ぶ。
クライアントから受け取ったクエリの処理要求は、リレーショナルエンジン内で「プランキャッシュの確認→クエリの解析→クエリのパラメータ化→結果セットのバインド→最適化→クエリオプティマイザによるクエリ実行プラン生成または再利用→クエリ処理にあたるリソース確保→クエリの実行→クエリ実行プランの登録」の順番で行われる。
第9章 ネットワーク
SQLサーバーはクライアントと通信を行う際にSNI（SQL Server Network Interface）という層が、TCP/IPや名前付きパイプなどの各プロトコルを抽象化している。このSNIはクライアント側でも使用し、接続コンポーネントをクライアント側にもインストールする必要がある。
SQLサーバーおよびクライアントはTDS（Tubular Data Stream：表形式データストリーム）形式というフォーマットに成型し、SNI層に受け渡す。SNI層では、ヘッダー情報を受け取ったデータに付加する。
SQLサーバーがクライアントとデータの送信を行う際には、バッファという構造体を使用している。バッファは必ずバッファヘッダーとバッファデータの組み合わせで存在しており、バッファヘッダーにはSPIDやバッファデータに含まれるメッセージタイプなど、各種の管理情報が含まれており、バッファデータには、ログイン情報やSQLコマンドなどのメッセージが含まれている。
このメッセージも、内容がシンプルな場合に使用されるトークンなしデータストリームと、複数のバッファに分割されて送信され、トークンという形式が付加されているトークン付きデータストリームの2つに分かれる。
第10章 データベースのバックアップと復元
SQLサーバーのバックアップファイルには、磁気テープへ出力するためのふぉーまっとであるMTF形式が採用されている。
MTF形式に準拠するため、バックアップの開始部分としゅうりょうぶぶんにMTF形式用の管理情報を含んでおり、それらの管理情報に含まれる形で、データベース内のデータやトランザクションログが格納されている。
バックアップ方式にはいくつかの種類が用意されており、完全バックアップや差分バックアップ、ファイルバックアップ、ファイル差分バックアップ、トランザクションログバックアップがある。
処理の流れとしては、まず、データベースのエクステント使用状況管理に使用している8KBページであるGAMを使用してデータベースファイルをスキャンする。データベースが複数存在する場合は、すべての読み込みは並列で処理される。次に、割り当て済みのエクステントを、物理的な並び順で読み込む。それから、読み込んだエクステントを、バックアップファイルに転送する。それ以外にも、様々な作業が行われており、いくつかの処理は並行で実行されている。
そして、バックアップ自体が破損していることもあり、定期的にDBC CHECKDBを行ったり、バックアップチェックサムを使用したりすることにより、破損を予防する。
バックアップの方法には、BACKUP/RESTORE以外にも、sp_detach_dbを実行してデタッチを行い、sp_attach_dbを使用してアタッチをするという方法もある。
第11章 トラブルシューティング
この章ではよく起こるトラブルと、それに対する解決方法についてまとめている。
トラブル1 SQLサーバーへの接続が成功しない
このようなトラブルが発生した場合、まず、SQLサーバーがlisten（待ち受け）しているプロトコルの確認をする。もしも、必要なプロトコルが無効になっている場合は有効化する。各プロトコルがlistenしているかを確認するには、SQLサーバーログを参照するといい。
次に、クライアントコンピュータのプロトコル設定を確認する。必要なプロトコルがあれば有効化する。
そして、もしも、SQLサーバーの名前付きインスタンスだけでリモートクライアントからの接続が失敗する場合、SQL Server Browserが正しく動作しているか確認する。SQL Server Browserサービスを有効化および開始してから、再度外部クライアントから接続させると、問題が解決する場合がある。
それから、WindowsのファイヤウォールがSQLサーバーとSQL Server Browserへのアクセスをブロックしている場合、手動でファイアウォールの例外を作成する必要がある。
トラブル2 ブロッキングの問題
複数のクエリが実行された際、同じオブジェクトに対して競合するロック獲得要求が行われると、後から獲得要求を行った方の処理が、先行する処理がロックを解放するまで待ち状態となり、処理を接続できなくするブロッキングが発生する。
この状況を改善するにあたり、まず、拡張イベントを使用するなどしてブロッキング状況の解析を行う。
それから、個々のトランザクションを短くしてロックされる時間を短縮したり、効果的なWHERE句の指定などでロックの範囲を狭めたり、トランザクション分離レベルを変更するなどして、ブロッキングの軽減を行う。
トラブル3 デッドロックの問題
複数の処理が同じリソースへのロック獲得を必要とした際、デッドロックが発生することがある。多くの場合は、ロックの獲得や解放のタイミング、トランザクション範囲の考慮などによって回避できるが、2つ以上のクライアントがそれぞれのトランザクションで、互換性のないロックの獲得要求を行った場合に発生するサイクルデッドロックやトランザクション分離レベルをSERIALIZABLEに設定した場合に発生する変換デッドロックなど、同時に実行される処理のタイミングにより避けられない場合もある。
これらの対処方法として、まず、トレースフラグ1204を有効化することで、出ッとロックに関係して処理の詳細な情報をログに出力する。
それから、オブジェクトへのアクセス順序の考慮を行ったり、トランザクションの範囲外でデータ入力を持つようにロジックを組むことでトランザクション内でのユーザー入力待ちを回避するなどして、デッドロックの発生を防ぐようにする。
第12章 新たなプラットフォームへの展開
SQLサーバーは元々Windowsプラットフォームの身をサポート対象としていたが、Linuxへの対応など、他のプラットフォームでも利用できるように拡張を行っている。
Linux版SQLサーバーを適用させるにあたり、Library OSと呼ばれる仮想化されたコンテナ内にOSの機能の一部を包含する機能が使用され、これによってOSカーネルとのやり取りを最小限に抑えることが可能になった。また。OS固有のシステムコールの差異を吸収する階層としてHost Extensionを用意することで、移植の作業やコードのメンテナンスを大幅に削減することができるようになった。
しかし、汎用的な仮想化を目的としたLibrary OSには、SQLサーバーが必要としない機能領域も含まれていたため、不要な機能を取り除き、SQLOSスケジューラと組み合わせることによりSQLPALと呼ばれるコンポーネントを作成した。
また、SQLサーバーはクラウドへの展開も行われており、PaaSサービスへの展開やIaaSの仮想マシンにSQLサーバーをインストールして使用する方法などが可能になっている。
感想
本書を一通り読むことで、何となく曖昧に把握していたSQLサーバーの仕組みや構造を詳細まで理解をすることができました。概念的な部分もタイトル通り絵にして解説されており、非常にイメージがしやすく、スッと頭に入ってきました。
今後も業務でSQLサーバーやデータベースに触る機会が多々訪れると思いますが、今回学んだことを活かして、仕組みを考慮しながらデータベース設計を行っていきたいです。