【SQL Server】接続しているDB内のすべてのテーブルの定義情報を抽出する
SQLServerにおいて、すべてのテーブルの定義情報をまとめて取得する際にお手軽に取得できるクエリです。
sp_Msforeachtableはundocumentedなストアドプロシージャですが、すべてのテーブルに対し、処理を実行してくれるため何かと便利です。
sp_MSforeachtable @command1 = "sp_help '?'"
尚、事前に以下手順で出力をファイル形式かつタブ区切りにしておくとエクセルに貼り付けやすくてよいかと思います。
①クエリエディタ上で右クリックを押下して、「結果の出力（R)」-「結果をファイルに出力」を選択。
②クエリエディタ上で右クリックを押下し、「クエリオプション」を選択。
③左ペインの「結果」配下の「テキスト」を選択し、出力形式(O)で”タブ区切り”を選択して「OK」を押下。
あとはクエリを実行すると出力先を決めることができるので、保存すればよいかと。
ちなみにテーブルおよびインデックスのDDLをすべて出力したいこともあるかと思います。
こちらに関しては、GUI操作に出力が簡単です。
①左ペインのオブジェクトエクスプローラでデータベースを選択し、右クリックを押下し、「タスク（T)」-「スクリプトの生成（E)」を選択。
②次へを押下。
③「テーブル」にチェックをいれます。
※今回はテーブル情報の出力ですが、ここでは下記のようにストアドプロシージャなども出力可能です。
④以下に応じて選択します。
全テーブルで1ファイルとしたければ、「生成するファイル」を単一ファイル
1テーブルごとに1ファイルとしたければ、「生成するファイル」をオブジェクトごとに1つのファイル
また、詳細設定(A)を選択すると下記のような出力オプションが選ぶことができます。
今回は、インデックスのスクリプトを作成をFalse⇒Trueとして実行してみます。
出力したファイルは下記のようなものとなります。
【SQL Server】再コンパイル時の原因調査
バッチでステートメントレベルの再コンパイルが発生した際の原因調査として、拡張イベントが利用できます。
下記のようにsql_statement_recompileイベントを拡張イベントとして追加すれば確認できます。
実際に当該イベントが発生した場合は以下のようにイベントが取得されます。
実行プランを再作成する条件として以下がありますが、後から調査する際にどれであったかわかるのは便利かと思います。
(※拡張イベントを設定することによる負荷増加は念頭に入れておく必要はあります。)
【実行プランを再作成する条件】
・クエリ(ALTER TABLE および ALTER VIEW)によって参照されるテーブルまたはビューに変更を加えた場合
・実行プランで使用されるインデックスに加えた場合
・UPDATE STATISTICS などのステートメントを使用して明示的に生成した実行プラン、または自動的に生成された実行プランによって使用される統計を更新した場合
・実行プランで使用されるインデックスを削除した場合
・sp_recompile を明示的に呼び出した場合
・クエリによって参照されるテーブルを変更する他のユーザが、INSERT ステートメントまたはDELETE ステートメントを使用して大量の変更をキーに加えた場合
・トリガーを含むテーブルで、inserted テーブルまたは deleted テーブルの行数が大幅に増加する場合
・WITH RECOMPILE オプションを使用してストアドプロシージャを使用して実行する場合
【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%くらいあるとよいですが、普段のベースラインから大幅に変化しているかどうか確認して、変化があれば詳細を確認するとよいかと思います。
