6章 パフォーマンスの監視とトラブルシューティング

SQL Server MCP

6.1 パフォーマンス監視のためのデータ収集

1.1 SQLSVメモリ領域
  • メモリ動作の流れ
    1. SELECT実行
    2. プロシージャキャッシュSELECT
    3. 物理ファイルからSELECTデータ読み取り
    4. クライアントに結果返す
    5. UPDATE,INSERT実行
    6. プロシージャキャッシュUPDATE,INSERT
    7. バッファキャッシュの変更
    8. ログキャッシュからログファイルに先行書き込み
    9. チェックポイントでバッファキャッシュ分をデータファイルに書き込み
  • キャッシュ
  • バッファマネージャで管理
    1. プロシージャキャッシュ
    2. バッファキャッシュ
      • 実は２つあるので注意
      1. テーブルから取得したデータをキャッシュ
      2. チェックポイントがくるまでのデータ。これ重要。
    3. ログキャッシュ
      • INSERT,UPDATE,DELETEのTRの履歴を格納する領域
1.2 ロックによるTR分離
  • ロックマネージャにより、データ、もしくはインデックスキーにかけられる。
  • ロックは下記の順で範囲を拡大する。エスカレーションという。
    1. ページ
    2. エクステント
    3. テーブル
  • ロックの種類
  1. 共有ロック
    • SELECTで起こる。これは基本テーブルロックだね。
  2. 更新ロック
    • WHEREをつかったUPDATE,DELETEのときに行が決まるまでの検索状態の時にかかる。みつかったら排他ロックに昇格！
  3. 排他ロック
    • INSERT, UPDATE, DELETEによるデータ更新中のとき起こる。TR終了まで完全行ロックする。
  • 共有ロックと更新ロックは共存できる。そりゃ新発見。。でもどっちも検索だからできるかー。

1.3 デッドロック

  • ２つずつのUPDATE文で簡単に起こせます。
  • デッドロックモニタが常に監視してる。
  • 強引にTR完了させる。エラーメッセージ1205が返る。
1.4 SQLServerの監視
  • 動的管理オブジェクト
  • 動的管理ビュー
  • プレフィックスはdm
    1. dm_exec_sessions
      • すべてのセッション
    2. dm_exec_requests
      • セッションごとのコマンド処理要求
    3. dm_exec_query_stats
      • プロシージャキャッシュのクエリプランのパフォーマンス統計
    4. dm_io_pending_io_requests
      • 保留中の入出力要求情報
    5. dm_os_memory_pools
      • 各メモリオブジェクト情報
    6. dm_tran_locks
      • ロック情報
    7. dm_db_index_usage_stats
      • インデックス利用状況の統計
    8. dm_db_index_physical_stats
      • インデックスのサイズおよび断片化情報
    • このまんまじゃやっぱ使えねーんだよね。Obj番号とかで言われても、わかんないし。ほんとに使える動的クエリはMSページで探すしかないわな。
  • システムモニタ
  • 管理ツールの『パフォーマンス』ってやつだね。SQLSVインストールしてると項目が増えてます。
    1. SQL Server:Buffer Manager
      • メモリ使用量、キャッシュヒット率
    2. SQL Server:Database
      • 使用できるログ空きディスク容量。TR数。
    3. SQL Server:Statistics
    4. SQL Server:General Statistics
    5. SQL Server:Locks
    6. SQL Server:Memory Manager
      • ロック構造の総数
    7. SQL Server:Plan Cache
      • ストアド、トリガ、プロシージャキャッシュ
  • SQL Server Profiler
    1. トレーステンプレートに基づいたトレースデータ収集
    2. トレースデータ収集しながら、リアルタイムなトレース結果の表示
    3. トレースデータ保存
    4. トレースデータ再生実行
  • 知らなかったー
    • トレースのプロパティでの設定は使える
      • ファイルも最初に設定して、KBでどんどん入れ替えってできるのね
      • テーブルに入れていける設定あるのね。勝手にテーブルできたー。SELECT INTOでいきなり作ってくれんだー。すげ。
      • サーバがトレースデータを処理するにチェックすると負荷をかけない
    • トレースファイルのテンプレートって使えるねー。
      • 実はパフォーマンスチェック用とかいろいろ用意されてる
    • deadlock graphだせたー

6.2 データベースエンジンのチューニング

  1. SQL profilerでテンプレートにtuningってのがあるのでそれでtrcファイルを作成する。
  2. それをデータベースチューニングアドバイザのワークロードってとこにセットして分析する。
  • キホンはインデックスの状況とかそのへんを一気にみれるよってとこかなー。

6.3 トラブルシューティング

  • SQLServerログの表示
    • フィルタもかけれるよ
    • インスタンスが起動してないときは
      • 〜\MSSQL.1\MSSQL\LOGフォルダの下のERRORLOGって名前のログファイルにログがあるよ
  • 利用状況モニタ
    • これはよく使うしね。プロセスとロック情報取得できるし、強制終了もできる。うん。使えます。
    • DBCC OPENTRAN()って使うとブロックしてるクエリすぐわかるよ。こりゃ使えます！