【SQL Server】リレーション図の出力
データベース内の各テーブルのER図の出力方法の備忘録です。
「データベースダイアグラム」を選択して右クリックを押下、「新しいデータベース ダイアグラム(N)」を選択します。
リレーション図に表示したいテーブルを選択します。
下記のように出力されます。
右クリックで「リレーションシップ ラベルの表示(L)」を選択すると、FK制約のラベルが表示できます。
右クリックで「ダイアグラムをクリップボードにコピー(P)」を選択すると、ダイアグラムのみをきれいにコピペすることができます。
このダイアグラムに表示されているテーブルにカラム追加等があった場合は、自動で反映されるので、SSMS上でサッと確認するには便利かと思います。
【SQL Server】データコレクションの「サーバーの利用状況の履歴」・「クエリ統計の履歴」レポート出力の仕方
データコレクションのレポートのうちよく参照するであろう「サーバの利用状況の履歴」「クエリ統計の履歴」の出力についてです。
「サーバの利用状況の履歴」「クエリ統計の履歴」で出力できるグラフの関係図は以下のとおりです。
「サーバの利用状況の履歴」のプロパティ情報は以下のようになっています。
ここでは、どのようなクエリで本レポート用の情報収集が行われているかも確認することができます。
「クエリ統計の履歴」のプロパティ情報は以下のようになっています。
◆サーバーの利用状況の履歴
このレポートでは、グラフ上段のタイムラインで指定した区間におけるCPＵ使用率、メモリ、ディスクI/O、ネットワークの使用量を確認することができます。
それぞれの使用率は、SQL Serverとシステムをわけてグラフとして表示されます。
中段では、「SQL Serverの待機」」グラフとして、どのような待ち事象がどれくらい発生していたか確認することができます。
下段では、「SQL Serverの利用状況」グラフとして1秒当たりのバッチリクエスト数や、Complilation数などを確認することができます。
飛べるリンク先については番号で後述していきます。
①SQL Serverの待機
「サーバーの利用状況の履歴」レポートの①をクリックすると、「SQL Serverの待機」レポートへ遷移することができます。
各待ち事象における詳細な内訳と割合などをここで確認することができます。
PageIOLatchやLockの状況を過去に遡って確認できるため、後からパフォーマンス低下時の状況を確認でき便利です。
"CPU"をクリックすると、後述する「④クエリ統計の履歴」レポートの"総CPU時間ごと"の上位10クエリが表示できます。
"Buffer I/O"をクリックすると、後述する「⑧SQL Server ディスクI/Oの使用量」レポートに遷移できます。
"Lock"をクリックすると、後述する「⑨SQL Serverのブロッキング」レポートに遷移できます。
そのほかのリンクをクリックすると、後述する「⑫SQL Serverのサンプリングされた待機」レポートに遷移できます。
②SQL Serverの利用状況
「サーバーの利用状況の履歴」レポートの②をクリックすると、「SQL Serverの利用状況」レポートへ遷移することができます。
各時間帯毎の接続数や、バッチリクエスト数プランキャッシュヒット率、tempdbの使用状況が確認できます。
プランキャッシュヒット率や、tempdbの使用状況が簡単に確認でき、こちらも便利かと思います。
③システムのＣＰＵ使用率
「サーバーの利用状況の履歴」レポートのＣＰＵグラフ中にプロットされている線（③）をクリックすると、「システムのＣＰＵ使用率」レポートへ遷移することができます。
このレポートでは、各プロセッサ毎の使用率や、スレッドの平均数を確認できます。
ＳＱＬ Server含むすべてのプロセッサの使用率がわかるので、サーバー全体でのＣＰＵ使用率の推移を見ることができます。
④クエリ統計の履歴
「サーバーの利用状況の履歴」レポートのＣＰＵグラフ中にプロットされている線（④）をクリックすると、「クエリ統計の履歴」レポートへ遷移することができます。
また、オブジェクトブラウザの「管理-データコレクション」を右クリックした際に表示できる「クエリ統計の履歴」レポートも本内容と同じもととなります。
「サーバーの利用状況の履歴」レポートのCPUグラフをクリックして遷移した本レポートは、該当時間帯における総CPU時間ごとの上位10クエリを確認することができます。
また、クエリの順位付け方法の各リンクをクリックすることで、同時間帯におけるそれぞれの上位10クエリを確認できます。
下段の各クエリをクリックすると各クエリの詳細レポートである「⑬クエリの詳細」へ飛ぶことができます。
⑤システムのメモリ使用量
「サーバーの利用状況の履歴」レポートのメモリグラフ中にプロットされている線（⑤）をクリックすると、「システムのメモリ使用量」レポートへ遷移することができます。
このレポートでは、サーバー全体で使用しているWorkingSet、Privatebyteなどの物理メモリ使用量やキャッシュのサイズを確認することができます。
⑥SQL Serverのメモリ使用量
「サーバーの利用状況の履歴」レポートのメモリグラフ中にプロットされている線（⑥）をクリックすると、「SQL Serverのメモリ使用量」レポートへ遷移することができます。
このレポートでは、SQL Serverの各プロセスが使用しているメモリの使用量※や、PLE、各コンポーネントごとのメモリ使用量が確認できます。
※SQL Serverプロセス メモリ使用量がなぜか取得できていなかったため、グラフに表示されておりません。。。
特にPLEや「種類別のSQL Server内部メモリの消費量」のSQLBUFFERPOOLを見ることでSQL Serverへのメモリ割り当てが十分か確認できるため重宝するレポートになるかと思います。
⑦システムのディスク使用量
「サーバーの利用状況の履歴」レポートのディスクI/Oグラフ中にプロットされている線（⑦）をクリックすると、「システムのディスク使用量」レポートへ遷移することができます。
各ディスクごとの応答時間やキューの平均の長さ、転送速度の推移などが確認できます。
⑧SQL ServerのディスクI/Oの使用量
「サーバーの利用状況の履歴」レポートのディスクI/Oグラフ中にプロットされている線（⑧）をクリックすると、「SQL ServerのディスクI/O使用量」レポートへ遷移することができます。
SQL Serverの各データファイル、ログファイルの格納先であるディスクの応答時間などがここでわかります。
中段にある「「ディスクI/O使用量別のクエリ統計の履歴レポートを表示」をクリックすると、上記④の「「クエリ統計の履歴」レポートの"I/Oの合計数"上位10クエリが表示できます。
下段では、データファイルやログファイル転送時の待機イベントについてもサンプリングされており、「⑫SQL Serverのサンプリングされた待機」レポートへ遷移することができます。
⑨SQL Serverのブロッキング
「①SQL Serverの待機」レポートの⑨"Lock"をクリックすると、「SQL Serverのブロッキング」レポートへ遷移することができます。
このレポートでは、該当時間帯に実行されているブロッキング情報を確認することができます。
ブロッキングの開始時刻や実行時間が過去分に遡って確認することができます。
下段のチェーン#の各項番(⑩)をクリックすると、後述する⑩SQL Serverのブロッキングチェーンの詳細」レポートへ遷移することができます。
⑩SQL Serverのブロッキングチェーンの詳細
このレポートでは、ブロッキングチェーンの概要として、ブロッキングの開始時刻やブロックセッション数、先頭ブロックの概要やクエリが確認できます。
また、下段の左側にある"サンプル時刻"の時刻リンク(⑪)をクリックすると、後述する「⑪SQL Serverのアクティブな要求」レポートへ遷移することができます。
下段の右側にある"クエリ"のクエリテキストのリンク(⑭)をクリックすると、後述する「⑭クエリプランの詳細」レポートへ遷移することができます。
⑪SQL Serverのアクティブな要求
このレポートでは、特定の時間(遷移前のレポートで選択した時間)におけるリクエストとセッションについての情報を確認することができます。
ロックを発生させていたクエリがそのセッションでどの程度のCPU時間、Phisical Read、実行時間であったか等々が確認できます。
各セッションを開くと表示できる"クエリ"のクエリテキストのリンク(⑭)をクリックすると、後述する「⑭クエリプランの詳細」レポートへ遷移することができます。
⑫SQL Serverのサンプリングされた待機
このレポートでは、各待機毎のカテゴリにおける待機イベントが発生している数と割合をデータベース毎、実行されているクエリ毎に確認することができます。
待機カテゴリを開いていくと表示できる"クエリ"のクエリテキストのリンク(⑬)をクリックすると、後述する「⑬クエリの詳細」レポートへ遷移することができます。
⑬クエリの詳細
このレポートでは、遷移前のレポートで選択したクエリの該当時間帯における詳細情報を確認することができます。
詳細な見方については、以前もご紹介していますので、過去記事を見ていただければと思います。
【SQL server】クエリ統計の履歴レポートについて - 小物SEのメモ帳
中段の"このクエリに関するサンプリングされた待機を表示"リンク(⑫)をクリックすると、前述した「⑫SQL Serverのサンプリングされた待機」レポートへ遷移することができます。
下段のプラン番号の各項番リンク(⑭)をクリックすると、後述する「⑭クエリプランの詳細」レポートへ遷移することができます。
「⑭クエリプランの詳細」
このレポートでは、遷移前のレポートで選択した実行プラン時のクエリの実行統計の情報が確認できます。
パラメータ最適化が行われている場合、実行プラン作成時のパラメータなどもここで確認可能です。
中段の"このクエリに関するサンプリングされた待機を表示"リンク(⑫)をクリックすると、前述した「⑫SQL Serverのサンプリングされた待機」レポートへ遷移することができます。
同じく中段の"グラフィカルなクエリ実行プランの表示"リンク(⑮)をクリックすると、実行プランが表示できます。※
※データコレクション機能で取得できている場合に限ります。
【SQL Server】データコレクションのディスク使用量レポート出力の仕方
システムデータコレクション(MDW)のディスク使用量レポートの出力の仕方を説明します。
ディスク使用量レポートとしては以下のような関係で出力が可能です。
プロパティ情報は以下のようになっています。
ここでは、どのようなクエリで本レポート用の情報収集が行われているかも確認することができます。
◆ディスク使用量の概要
データベース毎のデータファイル、ログファイルのディスク領域使用状況が確認することができます。
①Disk Usage Collection Set - Database:[Database_name]
上記①をクリックすると、対象データベースのデータファイル拡張状況の推移が確認できます。
②Disk Usage Collection Set - Log:[Database_name]
上記②をクリックすると、対象データベースのログファイル拡張状況の推移が確認できます。
【SQL server】ラッチ状況の確認
SQLserver ラッチ待ちの解説と確認
SQLserverには3種類のラッチが存在します。
1.LACTH_[xx] (非バッファーラッチ)
2.PAGELATCH_[xx] (バッファーラッチ)
3.PAGEIOLATCH_[xx] (バッファーラッチ)
※[xx]にはSH(参照),UP(更新),EX(排他)などが入ります。
これらについて主にどのような時に発生するのか解説します。
ラッチ全体としては以下のようなイメージになるかと思います。
ラッチ全体の状況を確認するSQLは以下でざっくり確認できます。
SELECT CASE WHEN [wait_type] LIKE N'LATCH%' THEN N'Non_Buffer_Latch' WHEN [wait_type] LIKE N'PAGELATCH%' THEN N'Buffer_Latch' WHEN [wait_type] LIKE N'PAGEIOLATCH%' THEN N'Buffer_Latch' END AS [type] ,* FROM sys.dm_os_wait_stats WHERE [wait_type] LIKE N'LATCH%' OR [wait_type] LIKE N'PAGELATCH%' OR [wait_type] LIKE N'PAGEIOLATCH%'
1.LACTH_[xx]
メモリ上の非バッファーキャッシュの読み取り時に発生します。様々な種類がありますが、例えばインデックスのB-treeの拡張等で発生します。
上記SQLのtypeが「Non_Buffer_Latch」であるレコードを確認すればどのくらい待ちが発生しているかは判断できますが、さらにラッチの詳細を確認する場合は以下のようなSQLで確認するとよいでしょう。
SELECT [latch_class], [wait_time_ms] AS [wait_time_ms], [waiting_requests_count] AS [wait_counts], 100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [wait_time_Percentage] FROM sys.dm_os_latch_stats WHERE [latch_class] NOT IN ( N'BUFFER') AND [wait_time_ms] > 0 ORDER BY [wait_time_Percentage] DESC
2.PAGELATCH_[xx]
バッファーキャッシュ上に存在するページの読み取り時に発生します。
tempdbのPFS(Page Free Space)に対する一時オブジェクトのCREATE,DROPや、インデックスの末尾ページへのデータの追加等で見受けられます。
対処法としては以下のようなものが考えられます。
tempdbのPFSに対しては
①tempdbのデータファイル数をCPU数に一致させる。
②tempdbのデータファイル容量をすべて同じサイズにする。
詳しくはMSのData Platform Support Team Blogにtempdbの考え方についての記載がありますのでこちらを見ていただければよいかと。
https://blogs.msdn.microsoft.com/jpsql/2013/01/16/dosdonts-17-tempdb-259/
インデックスページの末尾で発生するPAGELATCH_[xx]については
①同じファイルサイズのデータファイル(.ndf)を追加して処理対象となるデータファイルを分散させる。
②インデックスのキー項目がシーケンシャルな値の場合には追加処理が末尾ページに集中するので、GUIDなどの処理対象が分散するような値をインデックスのキーとして検討する。（業務要件として可能な場合に限る）
③テーブルやインデックスをパーティション分割する。
PAGEIOLATCH_[xx]
これはよく見かけるLATCHかと思います。
読み取り操作時にディスクからバッファーキャッシュへ移動する際に発生します。ゆえにインスタンス起動直後やキャッシュをクリアした後には発生してしまいます。
尚ディスクからキャッシュへのデータページの移動については以下のような順序で実施されます。
①データページの要求
②バッファーキャッシュにデータが存在しない
③バッファーにページ用領域を割り当て
④ディスクからバッファーキャッシュへ移動
対処法としては、以下の観点で確認をするとよいかと思います。
(1)ログからSQLserverが再起動していないか確認
再起動後はキャッシュが空のため、クエリ実行時にPAGEIOLATCHが多く発生します。
これに起因するPAGEIOLATCHの発生はやむを得ないかと思います。
(2)IOが多いクエリがないか確認
不要なIOを伴うクエリはディスクから大量ページをバッファーキャッシュに読み込むため、IOを減らすようチューニングしましょう。
(3)インデックスの断片化が発生していないか確認
断片化により1ページ当たりのデータ格納量が減っている場合、断片化を解消することで読み取る必要があるページ数を削減できます。
こちらに関してはメンテナンスプランで自動化する等して定期的に断片化を解消しましょう。
(4)SQLserverへの割り当てメモリが不足していないか確認
バッファーキャッシュの上限設定(MaxServerMemory)が必要としているメモリと比べ不足している場合、データページがキャッシュアウトされやすいため、ディスクからの読み取りが増加してしまいます。メモリ割り当てを増やすことを検討しましょう。
メモリが不足しているかどうかは別途分析が必要かと思いますが、簡単に以下だけでも閾値として確認しておくとよいでしょう。
| メトリック | 閾値 |
|---|---|
| バッファーキャッシュヒット率(Buffer cache hit ratio) | 97%以上※ |
| Page life expectancy | 300秒 |
※環境によっては90%以上でもよい場合があります。目安としては97%くらいあるとよいですが、普段のベースラインから大幅に変化しているかどうか確認して、変化があれば詳細を確認するとよいかと思います。
【SQL server】デッドロックの調査方法
デッドロック発生に際し、分析時の確認の仕方について紹介します。
エラーログやSQL Plofilerなどで調査可能ですが、個人的には拡張イベントがお勧めです。デフォルト設定であれば、拡張イベントの「system_health」でデッドロック情報は取得しているので、事後で確認する際に便利です。
拡張イベントで採取されたデータはデフォルトであれば以下のようにログファイル格納先と同様の場所にありますので、ダブルクリックして開きます。
尚、ファイル形式での拡張イベント「system_health」は、5MB：4世代しかデフォルトで残らないため、事後で確認可能とはいえデッドロック発生後は速やかに本ファイルを退避しておく方がよいでしょう。
上部のメニューで「フィルター」を選択し、以下のようにフィルタします。
| フィールド | 演算子 | 値 |
|---|---|---|
| name | = | xml_deadlock_report |
表示されたイベントのデッドロックタブを開いてデッドロックの詳細を確認します。ここではデッドロックとなったページ情報等(※1)が出力され、原因となったインデックスやクエリが表示できます。
※1 ページに格納されている情報を確認したい場合は、【SQL server】ロックの種類とページ情報 - 小物SEのメモ帳を参照
尚、クエリの表示はデッドロックグラフにマウスを当てると表示されます。
また、詳細タブに出力されているレコードをダブルクリックすることで、デッドロック時の詳細情報を確認することができます。
こちらにはsql_handleも残っているので、キャッシュ上にデータが残っていればこのsql_handleからデッドロック時の実行プランを確認できるかと思います。
拡張イベント「system_health」のring buffer上に残っているものは以下クエリで確認することも可能です。
SELECT XEventData.XEvent.query('.') AS [XEvent] FROM (SELECT CAST(target_data AS XML) AS [TargetData] FROM [sys].[dm_xe_session_targets] AS [targets] WITH(NOLOCK) INNER JOIN [sys].[dm_xe_sessions] AS [sessions] WITH(NOLOCK) ON [sessions].[address] = [targets].[event_session_address] WHERE [sessions].[name] = N'system_health' AND [targets].[target_name] = N'ring_buffer' ) AS [system_health] CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
デッドロックの発生箇所や原因となるクエリが特定できたら、次のアクションとしては対象クエリの実行プランや処理順序を見直してデッドロックの発生率を減少をしていきましょう。
デッドロックあるあるとしては、実行プランを確認したら暗黙型変換(CONVERT_IMPLICIT)が発生しておりインデックスがきいていなかったため、不要にページロック獲得をしていた等があるかと思います。decimal/numeric、varchar/nvarchar等は基本的なことですが、注意しましょう。
◆参考情報
・system_health セッションの使用
https://msdn.microsoft.com/ja-jp/library/ff877955.aspx
・暗黙型変換について
https://blogs.msdn.microsoft.com/jpsql/2011/02/01/dosdonts-2/
【SQL server】ロックエスカレーションの閾値について
悪者扱いされることの多いロックエスカレーションの発生する閾値と設定の確認方法についてです。
・ロック上限が動的な場合（locks オプションが既定値）のロックエスカレーションの閾値
（1）1 つの Transact-SQL ステートメントが 1 つのテーブルまたはインデックスに対して少なくとも 5,000 個のロックを獲得した場合
（2）ロックによって使用されるメモリ※がデータベースによって使用されるバッファープールの24%以上となった場合
※ロックオブジェクトのサイズは1ロックにつき96バイト
また、ロックの競合によりロックをエスカレートできない場合、新たなロックを 1,250 個獲得するごとにロックのエスカレーションが行われます。
尚、変化するワークロードに合わせてメモリを動的に確保・解放するため、上記閾値は動的に変わります。
・ロック上限が固定の場合（locks オプションが既定値でない）のロックエスカレーションの閾値
（1）ロック数が設定した値の上限の40％※に達した場合
※メモリに負荷がかかっている場合は 40% 未満
ロックエスカレーションはテーブル単位で無効化することは可能ですが、バッファープールのうち60%以上のロックを獲得した場合、もしくは使用できるメモリがなくなった場合にロックを獲得しようとするとエラーが発生しますので注意が必要です。
ちなみにロックエスカレーションの設定確認・変更は以下で行えます。
◆設定確認
SELECT [name], [lock_escalation], [lock_escalation_desc] FROM [sys].[tables]
◆設定変更
ALTER TABLE sales.product.test SET (LOCK_ESCALATION = AUTO)
| lock_escalation | lock_escalation_desc | 説明 |
|---|---|---|
| 0 | TABLE | ロックエスカレーションはテーブルレベルの粒度で行われる ※デフォルト |
| 1 | DISABLE | ほとんどの場合のロックエスカレーションを禁止する テーブルレベルのロックは完全には禁止されない |
| 2 | AUTO | テーブルスキーマに適したロックエスカレーションの粒度が選択される また、パーティション分割されている場合、ロックエスカレーションをパーティション分割できる |
◆参考資料
・locks オプションについて
https://technet.microsoft.com/ja-jp/library/ms175978(v=sql.105).aspx
・ロックのエスカレーションについて
https://technet.microsoft.com/ja-jp/library/ms184286(v=sql.105).aspx
