SQL Server 問題の分析方法のバックアップの現在との差分(No.13)

追加された行はこの色です。
削除された行はこの色です。
「[[マイクロソフト系技術情報 Wiki>http://techinfoofmicrosofttech.osscons.jp/]]」は、「[[Open棟梁Project>https://github.com/OpenTouryoProject/]]」,「[[OSSコンソーシアム .NET開発基盤部会>https://www.osscons.jp/dotNetDevelopmentInfrastructure/]]」によって運営されています。

-[[戻る>SQL Server]]

* 目次 [#v18ee228]
#contents

*概要 [#r47a15be]
SQL Serverにおいて、
-[[SQL Server 大量データ処理時の性能問題]]
-[[SQL Server でのロック・タイムアウト]]

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

*ツール [#l2cfd1df]
**Management Studio [#b49b7446]

***[[実行プランのグラフィカル表示]] [#ic07a23c]
**[[実行プランのグラフィカル表示]] [#ic07a23c]

***[[SQLプロファイラ（SQLトレース）]] [#bed21543]
**[[SQLプロファイラ（SQLトレース）]] [#bed21543]

***システムテーブル/動的管理ビュー [#ld604943]
-SQL Server Management Studio による監視~
https://technet.microsoft.com/ja-jp/library/ms191199.aspx
>SQL Server の現在の利用状況に関する情報を表示（利用状況モニター、標準レポート）
**T-SQLステートメント / システムテーブル [#m8a6ad10]
様々なT-SQLステートメント / システムテーブルが用意されている。

-Transact-SQL ステートメントによる監視~
https://technet.microsoft.com/ja-jp/library/ms191147.aspx
> Transact-SQL ステートメントとシステム ストアド プロシージャを使用。

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

--真・Dr. K's SQL Serverチューニング研修（4）~
DB管理者がいますぐ確認すべき3つの設定 (3/3) - ＠IT~
http://www.atmarkit.co.jp/fdb/rensai/10_drk/04/drk03.html~
---押さえておきたい4つの動的管理ビュー

--連載：Dr. K's SQL Serverチューニング研修 Part II （2）~
チューニングに大変革をもたらす動的管理ビュー
---http://www.atmarkit.co.jp/fdb/rensai/drk2_02/drk2_02_1.html~
---http://www.atmarkit.co.jp/fdb/rensai/drk2_02/drk2_02_2.html

***インデックスチューニングウィザード → データベース・エンジン・チューニング・アドバイザ [#b2d9fa8c]
**インデックスチューニングウィザード → データベース・エンジン・チューニング・アドバイザ [#b2d9fa8c]
-データベース エンジン チューニング アドバイザの概要~
https://technet.microsoft.com/ja-jp/library/ms173494.aspx

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

-チュートリアル:データベース エンジン チューニング アドバイザー~
https://msdn.microsoft.com/ja-jp/library/ms166575.aspx

***レポート（各種 標準レポート、Performance Dashboard Reports） [#t62eedcf]
**利用状況モニタ、動的管理ビュー（DMV）、クエリストア [#kfd16e0e]
利用状況モニタ：SQL Server 2000の頃から存在。
-動的管理ビュー（DMV）：SQL Server 2005から導入。
-[[クエリストア>SQL Server のログ#rcafcba1]]：SQL server 2016から導入。

*ログ [#acec1183]
**エラーログやイベントログ [#jdbdecf2]
はじめにエラーログやイベントログを確認ください~
（Windowsのものだけでなく、SQL Serverのログも）。
-参考

-[SQL Troubleshooting] 第1回 : Tips - SQL Server エラーログとイベント ログを採取する (SQL 2000 ～ 2008 R2)~
http://blogs.msdn.com/b/jpsql/archive/2012/03/27/info-sql-server-sql-server-error-log-and-event-log.aspx
--【SQL server】利用状況モニターの情報取得について - 小物SEのメモ帳~
https://memorandom-nishi.hatenablog.jp/entry/2016/09/22/195916

**[[SQLトレースログ>SQLプロファイラ（SQLトレース）]] [#d792e5a7]
[[SQLトレース>SQLプロファイラ（SQLトレース）]]をログ出力する。
--真・Dr. K's SQL Serverチューニング研修（4）~
DB管理者がいますぐ確認すべき3つの設定 (3/3) - ＠IT~
http://www.atmarkit.co.jp/fdb/rensai/10_drk/04/drk03.html~
---押さえておきたい4つの動的管理ビュー

**パフォーマンスログ [#id8e8d35]
SQLトレースログに加えて、
--連載：Dr. K's SQL Serverチューニング研修 Part II （2）~
チューニングに大変革をもたらす動的管理ビュー
---http://www.atmarkit.co.jp/fdb/rensai/drk2_02/drk2_02_1.html~
---http://www.atmarkit.co.jp/fdb/rensai/drk2_02/drk2_02_2.html

パフォーマンスカウンタを選択、パフォーマンスログを取得し~
問題（ソース消費量が多い処理は何処か？）を特定できる可能性がある。
**各種レポート（各種 標準レポート、Performance Dashboard Reports） [#t62eedcf]

***一般的なカウンタとしきい値 [#oae0bd02]
**[[各種ログ（SQLトレース、エラー ログ、クエリストア、パフォーマンス ログ）>SQL Server のログ]] [#acec1183]

-SQL Serverの一般的なカウンタとしきい値：その１
|#|リソース|<オブジェクト>：<カウンタ>|カウンタの説明|「しきい値」を超える条件|「しきい値」を超えた場合の対策|h
|~|~|~|~|~|備考|h
|1|>|>|>|>|メモリ|
|1-1||SQL Server：&br;Memory Manager：&br;Total Server Memory（KB）|SQL Serverのバッファ プールが使用しているメモリ量|サーバの物理メモリ容量に比べて高い。|メモリを増設する。|
|~|~|~|~|~|この値は、仮想メモリもカウントするため、サーバの物理メモリ容量を超えることがある。タスク マネージャでは示されない、AWEのメモリ使用状況も提供される。|
|2|>|>|>|>|バッファ キャッシュ|
|2-1||SQL Server：&br;Buffer Manager：&br;Free pages|バッファ プールの[[未使用ページリスト（Free Page List） >物理メモリ管理]]にあるページの総数|4ページ以下|メモリを増設する。|
|~|~|~|~|~|メモリ不足の場合、レイジー ライター（実行プランのキャッシュに必要なメモリを管理する）あるいはチェック ポイントなどの処理を保持できなくなる可能性がある。|
|2-2||SQL Server：&br;Buffer Manager：&br;Buffer cache hit ratio|バッファ キャッシュのヒット率|90%以下|メモリを増設する。|
|~|~|~|~|~|100%に近いほど適正。|
|2-3||SQL Server：&br;Buffer Manager：&br;Page Life expectancy|バッファ プール内で、ページが参照されなくても保持される秒数|300秒以下|メモリを増設する。|
|~|~|~|~|~|－|
|3|>|>|>|>|ディスク|
|3-1||SQL Server：&br;Buffer Manager：&br;Page Reads/Sec|SQL Serverのバッファ マネージャによる、ディスク上の読み取り回数/秒|ディスクの仕様で規定されている値の最大値に近い。|・メモリを増設する。&br;・ハードウェアのI/O容量を増やす。&br;・非正規化、インデックスの使用など、I/O操作が減るようにアプリケーションまたはDBを調整する。|
|~|~|~|~|~|一般的に、Ultra Wide SCSIディスクは毎秒50～70回のI/O操作を処理できる。|
|3-2||SQL Server：&br;Buffer Manager：&br;Page Writes/Sec|SQL Serverのバッファ マネージャによる、ディスク上の書き込み回数/秒|~|ハードウェアのI/O容量を増やす。|
|~|~|~|~|~|一般的に、Ultra Wide SCSIディスクは毎秒50～70回のI/O操作を処理できる。|

-SQL Serverの一般的なカウンタとしきい値：その２
|#|リソース|<オブジェクト>：<カウンタ>|カウンタの説明|「しきい値」を超える条件|備考|h
|1|>|>|>|>|負荷（トランザクション）|
|1-1||SQL Server：&br;Databases：&br;Transactions/sec|DBで開始されたトランザクションの数/秒|なし|この数値はシステム規模の参考になる。また、トランザクション負荷の増減をチェックするのにも役立つ。|
|1-2||SQL Server：&br;Databases：&br;Active Transactions|現在のDBで、アクティブなトランザクションの数|~|「SQL Server：Databases：Transactions/sec」が、「SQL Server：Databases(_Total)：Active Transactions」を超える場合、サーバの負荷が超過状態であることを示す。|
|1-3||SQL Server：&br;General Statistics：&br;User Connections|SQL Serverに現在接続しているユーザ数|~|この値の大幅な変動には注意すべき。|
|2|>|>|>|>|[[インデックス>SQL Server のインデックス]]|
|2-1||SQL Server：&br;Access Methods：&br;Full Scans/Sec|フル スキャンの数/秒|なし|この値が大きくなる場合、アプリケーションがインデックスを効率的に使用していないことが考えられる。原因となっているコード（クエリ）を調べ、必要に応じてインデックスを作成する。また、「tempdb」テーブルはほとんどインデックス付けされないため、「tempdb」データベースから情報を返す場合、この数値が高くなることがある。|
|2-2||SQL Server：&br;Access Methods：&br;Index Searches/Sec|インデックス検索数/秒|~|このカウンタによって、システムのデータ アクセスのパターンをチェックできる。インデックスは、「範囲スキャン」、「単一インデックス レコードのフェッチ」、「インデックスの中で再度位置付け」に使用される。|
|2-3||SQL Server：&br;Access Methods：&br;Page Splits/Sec|ページ分割回数/秒|~|性能上問題となる[[ページ分割>SQL Server のインデックス#z9ed663c]]の回数をチェックできる。|
|3|>|>|>|>|[[ロック>DBMSのロック・分離戦略と同時実行制御]]|
|3-1||SQL Server：Locks：&br;Average Wait Time（ms）|「待ち状態の原因となる各ロック要求」の「平均待ち時間（ミリ秒）」|なし|状況の監視に使用する。|
|3-2||SQL Server：Locks：&br;Lock Waits/Sec|ロック取得のために、待機しなければならない要求の数/秒|~|~|
|3-3||SQL Server：Locks：&br;Lock Timeouts/sec|[[タイムアウト>SQL Server でのロック・タイムアウト]]したロック要求の数/秒|~|NOWAITロックの要求を除く。状況の監視に使用する。|
|3-4||SQL Server：Locks：&br;Number of Deadlocks/sec|[[デッドロック>SQL Server でのデッドロック]]に帰着するロック要求の数/秒|~|状況の監視に使用する。|
|3-5||SQL Server：Latche：&br;Average Latch Wait Time|ラッチ要求の「平均待ち時間 （ミリ秒）」（ラッチは、負荷の軽い短期の同期化オブジェクトで、トランザクション全体にわたってロックする必要がない動作を保護する。主に、接続に対して行が読み取られている間、行を保護するために使用される。）|~|状況の監視に使用する。この数値が大きくなると、サーバがリソースを求めて競合に巻き込まれる恐れがある。|
|4|>|>|>|>|その他|
|4-1||SQL Server：&br;SQL Statistics：&br;SQL Compilations/Sec|SQLコンパイルの回数/秒。クエリの再コンパイルの回数も含まれる。|なし|SQL Serverのユーザ利用状況が安定したら、通常、この値は安定した状態になる。クエリの再コンパイル処理は、「スキーマの変更」、「テーブルに多くの行をインサートする」、「テーブルから多くの行をデリートする」などの操作によって発生する。値が常に高い場合は、調査が必要。[[アドホック クエリの問題の監視>SQL Server アドホック クエリ問題の監視]]に使用できる。|
|4-2||SQL Server：&br;SQL Statistics：&br;SQL Re-Compilations/Sec|クエリの再コンパイルの回数/秒。|~|~|
|4-3||SQL Server：&br;Buffer Manager：&br;Memory Grants Pending|作業領域メモリの使用許可を待っている処理の数。|~|[[アドホック クエリ>SQL Server アドホック クエリ問題の監視]]、[[結合方法>SQL Server 結合方式の問題を監視する]]の問題を監視するのに使用できる。|
|4-4||SQL Server：&br;Buffer Manager：&br;Stolen Page Count|他のサーバ メモリ要求によって奪われたバッファ キャッシュのページの数|~|~|

***参考 [#x27dbece]
-[[インシデント>SQL Server#i20a1481]]

*事象別の分析方法 [#a4f1b827]
**長時間実行クエリ、ストアド情報 [#df4c3615]
***sys.dm_exec_query_stats、_sql_text動的管理ビュー [#v04a8314]
T-SQLファイルを作成し実行する（スケジューラでsqlcmdから実行する等）。

**ブロッキング情報 [#zaecafdd]
***sys.dm_exec_request動的管理ビュー [#b7ddd0be]
T-SQLファイルを作成し実行する（スケジューラでsqlcmdから実行する等）。

***sp_blocker_pss80ストアド [#obc8f49b]
T-SQLファイルを作成し実行する（スケジューラでsqlcmdから実行する等）。

-参考
--SQL Server 2005 および SQL Server 2000 のブロッキングを監視する方法~
http://support.microsoft.com/kb/271509/ja

***トレースのblocked process reportイベント [#j24b2863]
T-SQLファイルを作成し実行する（sqlcmdから実行する等）。

*構成情報の分析方法 [#sc4ae627]
**スケジューラ情報 [#jb435162]
***sys.dm_os_schedulersビュー [#f21fb277]
T-SQLファイルを作成し実行する（スケジューラでsqlcmdから実行する等）。

**データベース・ミラーリング [#o8a76f2d]
いずれも、T-SQLファイルを作成し実行する（スケジューラでsqlcmdから実行する等）。

***sys.database_mirroring_endpoin、_connectionsカタログビュー [#of09c67d]
エンドポイントとコネクション情報

***sys.database_mirroringカタログビュー [#lc7c4049]
プリンシパルとミラー情報

***sys.database_mirroring_witnessesカタログビュー [#d35493cc]
監視サーバ情報

**[[MS-DTC]] [#cbbf0db4]
***[[MS-DTC]]トレース [#k51d2ac9]
GUIとレジストリから設定。

***CommunicationManagerエラー・トレース [#r837e687]
レジストリから設定。

*参考 [#y028a649]
-SQL Server Management Studio による監視~
https://technet.microsoft.com/ja-jp/library/ms191199.aspx
>SQL Server の現在の利用状況に関する情報を表示（利用状況モニター、標準レポート）

-Transact-SQL ステートメントによる監視~
https://technet.microsoft.com/ja-jp/library/ms191147.aspx
> Transact-SQL ステートメントとシステム ストアド プロシージャを使用。

**[[インシデント>SQL Server#i20a1481]] [#i55f8598]

**Support Team Blog [#z6c0deb5]
-[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]], [[:障害対応]], [[:性能]], [[:デバッグ]]
Tags: [[:データアクセス]], [[:SQL Server]], [[:障害対応]], [[:性能]], [[:デバッグ]], [[:ツール類]]
SQL Server 問題の分析方法 のバックアップの現在との差分(No.13)

SQL Server 問題の分析方法のバックアップの現在との差分(No.13)
