第1章 SQL ServerによるCPUリソースの使用
SQLOSスケジューラ
WINDOWSスケジューラにすべてのスレッドの管理を全て任せると、
待機中のSQL ServerにCPUリソースが割り当てられることがあり効率が下がる。
(ロック獲得待ちのスレッド、子スレッドの処理を待っている親スレッドなど)
Windowsスケジューラの配下にありながら、効率的にCPUリソースを使用する仕組みがSQLOSスケジューラ
SQL Server起動時にCPU数(コア数)と等しい数のスケジューラが作成される。
スケジューラがワーカの管理を行う。
ワーカ:
クライアントからのクエリは最終的に一つ以上のワーカに関連付けられて処理される。
ワーカスレッドプール:
各スケジューラで使用可能なワーカ数を管理する。
ワーカの最大数は「max worker threads」の設定値に依存する。
デフォルトで255個。
ワーカ使用要求があると、最大数に達していない場合は新たに作成され、
上限に達している場合はランナブルキューに追加され待機状態となる。
ランナブルキュー:
各スケジューラは一つのランナブルキューを持っている。
2つ以上のワーカが実行可能状態だった場合キューに追加され使用可能になるのを待つ。
ワークリクエストキュー:
ワーカ数が不足した時このリストに追加され、使用可能になるのを待つ。
ワーカ不足の一般的な要因はブロッキングや過度の並列処理。
I/Oリクエストリスト:
I/Oリクエストを要求したワーカは完了するまでこのリストに追加される。
ウェイターリスト:
ワーカ処理実行時に必要となるSQL Server内のリソースを確保できない場合にワーカはこのリストに追加される。
ロックやラッチが起きた時。ラッチとはページやアロケーションなどの物理的な単位に対して行われる。
第2章 SQL Serverが実行するI/O動作
管理するデータベースの実態
データファイル(.mdl, .ndf)とログファイル(.ldf)が存在する。
データファイル:
ユーザが参照・挿入・更新・削除を行うデータ本体。
8KBのブロックに分割されていて、ブロックは「ページ」という論理的な単位で管理されている。
ログファイル:
実行した更新内容を記録している。
先行書き込みログとなっており、トランザクションとデータの整合性を保つために
先にログに書き込み、その後処理を行う。必ず時系列順となる。
データベースへのアクセスパターン
・オンライントランザクジョンシステムの場合(OLTP)
多数のクライアントがアクセスを行い、それぞれが必要とする小規模データの操作を行う。
類似する小規模なトランザくジョンが多数存在する。
・意思決定支援システム(DSS)
データベースに蓄積されたデータを、経営戦略のためなどに様々な視点からの分析に使用する。
多くの場合結果としてデータファイルへ順次アクセスする傾向が強い。
先行読み取り(Read-Ahead)
実際の読み込み要求が発生する前にバッファキャッシュにデータを読み込むこと。
実際に読み込み要求が発生した際にI/Oのオーバーヘッドを緩和する。
チェックポイント(Check point)
以下、データ更新のフロー
先行書き込みログ操作⇨バッファキャッシュ上のデータが更新(論理書き込み)⇨物理ディスクに書き込み(物理書き込み)
まだ物理書き込みが行われていないバッファキャッシュ上のデータをダーティページと呼ぶ。
チェックポイントプロセスと呼ばれる内部コンポーネントが定期的にスキャンを行い、
ダーティページを物理ディスクに書き込む。
集中書き込み(Eager Write)
大量のデータを書き込む場合、大量のログ書き込みが発生し、
この処理がボトルネックとなりスループットを低下させることがある。
その場合に備えて一括操作と呼ばれる選択肢が存在する。
個別の更新データに対してログを書き込まなくなり、最小限のデータだけログに記録される。
一括操作が実行されるとバッファキャッシュ上に大量のダーティページが発生する。
このダーティページを物理書き込みする操作が集中書き込み。
レイジー書き込み(Lazy Write)
将来的なデータの読み込みに備えて、参照された頻度が最も低いバッファキャッシュ上のページを初期化する。
そのページがダーティページだった場合は物理ディスクへ書き込みを行う。
第3,4章 SQL Serverのメモリ管理
仮想アドレス空間の状態
・Committed
仮想アドレス空間で実際に使用されている領域。
この領域に対してはOSが物理メモリの領域を割り当てている。
・Reserved
後の使用に備えて仮想アドレス空間が予約済みとなっている状態のこと。
この状態では物理メモリは一切与えられていない。
SQLサーバで設定されたメモリ使用量は起動時に必要な分だけcommittedされ残りはReserved状態となる。
・Free
文字通り自由な割り当てが可能。
物理メモリ以上のサイズをReserved, Committedの状態にせず残りはFreeとなる。
ページング
SQL Serverの使用するメモリをページファイルへ書き込むことを抑制するために
「Lock Page in Memory」特権を
SQL Serverの起動アカウントに追加すればページング対象になることを避けることができる。
メモリ使用量
デフォルト設定の場合「物理メモリのサイズ-5MB」まで拡張される。
上限を設定する場合以下のコマンドを実行する。
exec sp_configure ‘max server memory’, 1024 — 最大値をMB単位で設定する。
go
reconfigure
バッファキャッシュ領域
・データキャッシュ
バッファキャッシュの大部分を占める。
DBのデータページやインデックスページを読み込んでキャッシュするために使用される。
各データページが持つ固有の値を基にハッシュ値が生成され、ハッシュバケットと呼ばれるページへ格納される。
・プロシージャキャッシュ
クライアントが実行したクエリは最適な形で処理されるようにコンパイルされ、
クエリプランに変換される。
一度コンパイルされたクエリプランは同じクエリが再度実行された時に備えてプロシージャキャッシュに保存される。
MemToLeave領域
8KBよりも大きなメモリブロックを使用する場合MemToLeave領域が使用される。
・SQLCLR
・拡張ストアドプロシージャ
・スレッドスタック
解放タイミングは不要になったらすぐに解放される。
第10章SQL Serverとネットワーク
通信の際SNI(SQL Server Network Interface)と呼ばれる層がTCP/IPや名前付きパイプといったプロトコルを抽象化。
上位層ではプロトコル間の差異を意識する必要がない。
複数のプロトコルの優先順位を設定して何らかのトラブルで特定のプロトコルで通信できなくても別のプロトコルで接続を試行する。
接続時にクライアント側で特定のプロトコルを指定することも可能だが、何らかの原因でそのプロトコルを使用して通信できない時に
問題が解決するまで通信ができなくなる。
・TCP/IPの場合
sqlcmd -E -Stcp : SQL1¥Instance1
デフォルトインスタンス
明示的に変更されない限り、常にポート番号1433を使用してクライアントからの接続を待ち受けている。
名前付きインスタンス
クライアントから接続要求を待つポートが複数。動的に決定される。
インスタンス起動時に未使用のポートを確認して自身に割り当てる。
クライアントが接続先のインスタンスを発見するのが容易ではない。
クライアントは接続時にSQL Server Browserサービスに必要な情報を問い合わせて接続に行く。
2度目以降はキャッシュされた情報を利用。
【覚書】メンテナンス・トラシュ用コマンド
・CPU状態確認
dbcc sqlperf(‘umsstats’)
・ディスクIO確認
dbcc sqlperf(‘waitstats’)
select * from sys.dm_os_wait_stats
・メモリ使用量確認
dbcc memorystatus go
・メモリ最大使用量設定
exec sp_configure ‘max server memory’, 1024 — 最大値をMB単位で設定する。
go
reconfigure
・キャッシュクリアコマンド
dbcc DROPCLEANBUFFERS — データキャッシュの削除
dbcc FREEPROCCACHE — プロシージャキャッシュの削除
・セッション確認
select * from sys.dm_exec_session
・コネクション確認
select * from sys.dm_exec_connections