マイクロソフト系技術情報 Wiki」は、「Open棟梁Project」,「OSSコンソーシアム .NET開発基盤部会」によって運営されています。

目次

概要

SQL Serverにおいて、

等の問題発生時の一般的な問題分析方法について説明します。

ツール

Management Studio

実行プランのグラフィカル表示

SQLプロファイラ(SQLトレース)

システムテーブル/動的管理ビュー

  • SQL Server 2005から動的管理ビューと言うものも導入されています。
    パフォーマンス・カウンタより詳細な情報を収集できます。

インデックスチューニングウィザード → データベース・エンジン・チューニング・アドバイザ

  • データベース エンジン チューニング アドバイザーの起動および使用
    https://msdn.microsoft.com/ja-jp/library/ms174202.aspx
    • データベース エンジン チューニング アドバイザーの初期化
    • データベース エンジン チューニング アドバイザーを起動する
    • ワークロードを作成する
    • データベースのチューニング
    • XML 入力ファイルの作成
    • ユーザー インターフェイスの説明

レポート(各種 標準レポート、Performance Dashboard Reports)

ログ

エラーログやイベントログ

はじめにエラーログやイベントログを確認ください
(Windowsのものだけでなく、SQL Serverのログも)。

SQLトレースログ

SQLトレースをログ出力する。

パフォーマンスログ

SQLトレースログに加えて、

パフォーマンスカウンタを選択、パフォーマンスログを取得し
問題(ソース消費量が多い処理は何処か?)を特定できる可能性がある。

一般的なカウンタとしきい値

  • SQL Serverの一般的なカウンタとしきい値:その1
    #リソース<オブジェクト>:<カウンタ>カウンタの説明「しきい値」を超える条件「しきい値」を超えた場合の対策
    備考
    1メモリ
    1-1SQL Server:
    Memory Manager:
    Total Server Memory(KB)
    SQL Serverのバッファ プールが使用しているメモリ量サーバの物理メモリ容量に比べて高い。メモリを増設する。
    この値は、仮想メモリもカウントするため、サーバの物理メモリ容量を超えることがある。タスク マネージャでは示されない、AWEのメモリ使用状況も提供される。
    2バッファ キャッシュ
    2-1SQL Server:
    Buffer Manager:
    Free pages
    バッファ プールの未使用ページリスト(Free Page List) にあるページの総数4ページ以下メモリを増設する。
    メモリ不足の場合、レイジー ライター(実行プランのキャッシュに必要なメモリを管理する)あるいはチェック ポイントなどの処理を保持できなくなる可能性がある。
    2-2SQL Server:
    Buffer Manager:
    Buffer cache hit ratio
    バッファ キャッシュのヒット率90%以下メモリを増設する。
    100%に近いほど適正。
    2-3SQL Server:
    Buffer Manager:
    Page Life expectancy
    バッファ プール内で、ページが参照されなくても保持される秒数300秒以下メモリを増設する。
    3ディスク
    3-1SQL Server:
    Buffer Manager:
    Page Reads/Sec
    SQL Serverのバッファ マネージャによる、ディスク上の読み取り回数/秒ディスクの仕様で規定されている値の最大値に近い。・メモリを増設する。
    ・ハードウェアのI/O容量を増やす。
    ・非正規化、インデックスの使用など、I/O操作が減るようにアプリケーションまたはDBを調整する。
    一般的に、Ultra Wide SCSIディスクは毎秒50~70回のI/O操作を処理できる。
    3-2SQL Server:
    Buffer Manager:
    Page Writes/Sec
    SQL Serverのバッファ マネージャによる、ディスク上の書き込み回数/秒ハードウェアのI/O容量を増やす。
    一般的に、Ultra Wide SCSIディスクは毎秒50~70回のI/O操作を処理できる。
  • SQL Serverの一般的なカウンタとしきい値:その2
    #リソース<オブジェクト>:<カウンタ>カウンタの説明「しきい値」を超える条件備考
    1負荷(トランザクション)
    1-1SQL Server:
    Databases:
    Transactions/sec
    DBで開始されたトランザクションの数/秒なしこの数値はシステム規模の参考になる。また、トランザクション負荷の増減をチェックするのにも役立つ。
    1-2SQL Server:
    Databases:
    Active Transactions
    現在のDBで、アクティブなトランザクションの数「SQL Server:Databases:Transactions/sec」が、「SQL Server:Databases(_Total):Active Transactions」を超える場合、サーバの負荷が超過状態であることを示す。
    1-3SQL Server:
    General Statistics:
    User Connections
    SQL Serverに現在接続しているユーザ数この値の大幅な変動には注意すべき。
    2インデックス
    2-1SQL Server:
    Access Methods:
    Full Scans/Sec
    フル スキャンの数/秒なしこの値が大きくなる場合、アプリケーションがインデックスを効率的に使用していないことが考えられる。原因となっているコード(クエリ)を調べ、必要に応じてインデックスを作成する。また、「tempdb」テーブルはほとんどインデックス付けされないため、「tempdb」データベースから情報を返す場合、この数値が高くなることがある。
    2-2SQL Server:
    Access Methods:
    Index Searches/Sec
    インデックス検索数/秒このカウンタによって、システムのデータ アクセスのパターンをチェックできる。インデックスは、「範囲スキャン」、「単一インデックス レコードのフェッチ」、「インデックスの中で再度位置付け」に使用される。
    2-3SQL Server:
    Access Methods:
    Page Splits/Sec
    ページ分割回数/秒性能上問題となるページ分割の回数をチェックできる。
    3ロック
    3-1SQL Server:Locks:
    Average Wait Time(ms)
    「待ち状態の原因となる各ロック要求」の「平均待ち時間(ミリ秒)」なし状況の監視に使用する。
    3-2SQL Server:Locks:
    Lock Waits/Sec
    ロック取得のために、待機しなければならない要求の数/秒
    3-3SQL Server:Locks:
    Lock Timeouts/sec
    タイムアウトしたロック要求の数/秒NOWAITロックの要求を除く。状況の監視に使用する。
    3-4SQL Server:Locks:
    Number of Deadlocks/sec
    デッドロックに帰着するロック要求の数/秒状況の監視に使用する。
    3-5SQL Server:Latche:
    Average Latch Wait Time
    ラッチ要求の「平均待ち時間 (ミリ秒)」(ラッチは、負荷の軽い短期の同期化オブジェクトで、トランザクション全体にわたってロックする必要がない動作を保護する。主に、接続に対して行が読み取られている間、行を保護するために使用される。)状況の監視に使用する。この数値が大きくなると、サーバがリソースを求めて競合に巻き込まれる恐れがある。
    4その他
    4-1SQL Server:
    SQL Statistics:
    SQL Compilations/Sec
    SQLコンパイルの回数/秒。クエリの再コンパイルの回数も含まれる。なしSQL Serverのユーザ利用状況が安定したら、通常、この値は安定した状態になる。クエリの再コンパイル処理は、「スキーマの変更」、「テーブルに多くの行をインサートする」、「テーブルから多くの行をデリートする」などの操作によって発生する。値が常に高い場合は、調査が必要。アドホック クエリの問題の監視に使用できる。
    4-2SQL Server:
    SQL Statistics:
    SQL Re-Compilations/Sec
    クエリの再コンパイルの回数/秒。
    4-3SQL Server:
    Buffer Manager:
    Memory Grants Pending
    作業領域メモリの使用許可を待っている処理の数。アドホック クエリ結合方法の問題を監視するのに使用できる。
    4-4SQL Server:
    Buffer Manager:
    Stolen Page Count
    他のサーバ メモリ要求によって奪われたバッファ キャッシュのページの数

事象別の分析方法

長時間実行クエリ、ストアド情報

sys.dm_exec_query_stats、_sql_text動的管理ビュー

T-SQLファイルを作成し実行する(スケジューラでsqlcmdから実行する等)。

ブロッキング情報

sys.dm_exec_request動的管理ビュー

T-SQLファイルを作成し実行する(スケジューラでsqlcmdから実行する等)。

sp_blocker_pss80ストアド

T-SQLファイルを作成し実行する(スケジューラでsqlcmdから実行する等)。

トレースのblocked process reportイベント

T-SQLファイルを作成し実行する(sqlcmdから実行する等)。

構成情報の分析方法

スケジューラ情報

sys.dm_os_schedulersビュー

T-SQLファイルを作成し実行する(スケジューラでsqlcmdから実行する等)。

データベース・ミラーリング

いずれも、T-SQLファイルを作成し実行する(スケジューラでsqlcmdから実行する等)。

sys.database_mirroring_endpoin、_connectionsカタログビュー

エンドポイントとコネクション情報

sys.database_mirroringカタログビュー

プリンシパルとミラー情報

sys.database_mirroring_witnessesカタログビュー

監視サーバ情報

MS-DTC

MS-DTCトレース

GUIとレジストリから設定。

CommunicationManager?エラー・トレース

レジストリから設定。

参考

  • [SQL Troubleshooting] SQL Server トラブルシューティング 6 回シリーズのご案内
    Microsoft SQL Server Japan Support Team Blog - Site Home - MSDN Blogs
    http://blogs.msdn.com/b/jpsql/archive/2012/03/30/sql-server-6.aspx
    • 第1回 SQL Server のログ、イベントログの確認方法
    • 第2回 パフォーマンスログの採取方法
    • 第3回 パフォーマンスログの確認方法
    • 第4回 サーバートレースの解析方法 1
    • 第5回 サーバートレースの解析方法 2
    • 第6回 ブロッキング情報の確認方法

インシデント


Tags: :データアクセス, :SQL Server, :障害対応, :性能, :デバッグ, :ツール類


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2018-02-02 (金) 16:56:23 (501d)