ServerSQLServer2014以降で使用することができるライブクエリ統計の機能がクエリのパフォーマンスチューニングに役立ちそうだったため使い方を記します。ライブクエリ統計を使うことで実行に長時間かかるクエリが、どの処理で時間を要しているのかわかるため、改善すべき処理が把握でき便利かと思います。使用するためには事前に以下の設定が必要です。SP1以降の場合、軽量版統計インフラストラクチャを有効にすることでも確認可能となります。拡張イベントでquery_thread_profileを有効にする実際に試してみるためしに時間のかかる適当なクエリを実行してライブクエリ統計を確認してみます。利用状況モニターを開いて、「アクティブなコストの高いクエリ」を押下して、確認するクエリを選択し右クリック、「ライブ実行プランの表示」を選択します。セッションから対象のクエリのみを確認する場合SSMSの上部メニューから「ライブクエリ統計を含む」アイコンをクリックします。確認結果は以下のようになります。処理中である箇所や、各処理に要した時間や処理行数がリアルタイムで確認することが可能になりますので、処理の完了を待たずにボトルネックが把握できるため長時間が予想されるクエリのチューニング時に活用してみては。UNKNOWNで使われる統計がどのように算出されているのか検証してみました。パラメータスニッフィングについては以下をご参照ください。UNKNOWNについて次のような説明がされています。オプティマイザーでクエリをコンパイルおよび最適化するときに、強制パラメーター化によって作成されたパラメーターも含め、すべてのローカル変数に対して初期値の代わりに統計データを使用することを指定します。literal_constant特定の値および"不明"の残りの変数の値に指定されています。これらの値はクエリを最適化する過程でのみ使用され、クエリの実行時には使用されません。(Transact-SQL)まずは検証用にテーブルとデータ、インデックスを用意します。ON)GO続いてインデックスの統計情報を更新し、統計情報を確認します。UNKNOWNを使用したストアドプロシージャを作成します。UNKNOWNを使用していない場合まずはUNKNOWNが無い場合のクエリを実行した場合の予測行数は次のとおりとなります。@Param1GOこの場合、パラメータスニッフィングが有効となっており実行プラン作成時のパラメータで最適化されているため、初回実行時のcol3=50の予測行数でプランが作成されていることが分かります。実行プランを削除してからパラメータを変更して実行プランを確認するとプラン作成時のパラメータに応じて予測行数が変わっていることがわかります。UNKNOWNを使用した場合UNKNOWNが有る場合のクエリを実行した場合の予測行数は次のとおりとなります。@Param1GO先ほどのパラメータを使用しているにも関わらず、予測行数が異なっています。先ほど同様に実行プランを削除してパラメータを変更して実行してみます。@Param1GOUNKNOWNが無い場合とは異なり、初回実行時のパラメータに左右されることなく一定の予測行数となっていることがわかるかと思います。density)から全体行(Rows)に対し何%含まれているか計算して予測行数としているようです。NO_INFOMSGSGO先ほど確認した実行プランの予測行数と一致することがわかります。UNKNOWNを使用する場合、統計情報から各データのユニークな値の分布と全体行数を考慮して平均的な行数を予測した実行プランを作るといえるかと思います。値毎の出現頻度までは考慮していないように見えます。今回でいうとcol3=50は件数が多いはずですが、col3のユニークな値と全体行数からしか予測行数を算出していないため。念のためヒストグラムを可視化しておくと以下の通りとなります。UNKNOWNを使う検討をするのもありかと思います。#その分パラメータによっては最速なプランは作成されないためトレードオフではありますが。Serverテーブル内のデータをINSERT文付きでエクスポートする手順です。データ移行用のデータ抽出などで利用できるかと。SSMSの左ペインのオブジェクトエクスプローラのデータベース名で右クリック、「タスク」を選択し、「スクリプト生成」を選びます。オプションの「全般」にある「スクリプトを作成するデータの種類」で「データのみ」を選択。「次へ」を選択し、次画面で「完了」を押下します。指定したフォルダにINSERT文付きのテーブルデータがエクスポートされます。「スクリプトを作成するデータの種類」の際に「スキーマとデータ」を選択すれば、テーブル作成や制約付きでエクスポートすることができますし、「統計のスクリプトを作成」で「統計とヒストグラムのスクリプトを作成します」を選択すれば現時点の統計情報も出力できるので別環境での検証の際にも使えそうですね。(商用環境から抽出する場合などのデータの取り扱いには十分注意が必要ですが。Serverたまに使用する際に毎回調べて書いていたので、備忘録&サンプルとして。SELECT...と同じ要領で記述できます。FROMを使用して他のテーブルの値で更新する構文です。('001','a001','y000001'),('002','a002','y000002'),('003','a003','y000003');準備したデータは以下のとおり。y000003Table_xのColumn1が001であるものについて、Table_xのColumn3をTable_yのColumn3の値で更新してみます。JOINを組み合わせることもできます。'001'UPDATE対象のデータの処理前、処理後を出力するSET句の後ろにOUTPUT句で記述します。('x000001','x000003');一時テーブルが存在するならば、削除する一時テーブルにデータを作成⇒データ格納をしながら繰り返し試行錯誤する際に便利です。簡潔に書くことができます。Server2016】とてもシンプル。Server2016】こちらもシンプルに記述できて便利です。EXISTSは、PROCEDUREやFUNCTION等にも使えるため、様々なDDL文に使用できます。カラムが存在しないならば、追加する単に削除の反対ですが。VARCHAR(50)GO新規ならINSERT、存在するならUPDATE処理をするデータ更新時に行が存在すればUPDATE、新規であればINSERTをする場合はMRGER構文を使います。Server2008以降)さらに処理した行の結果をOUTPUT句にて出力します。('001','a001','y000001'),('003','a003','y000003'),('004','a001','y000004'),('005','a002','y000002'),('006','a003','y000006');Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加してみます。Table_xのColumn1とTable_yのColumn1が同じものについて、Table_xに存在すればColumn3のみTable_yの値で更新、存在しなければ行を追加し、条件にあてはまらないTable_yの行を削除する場合。Server前回は、並列処理の設定確認や変更方法を記しましたが、今回は並列実行されていることの確認や、その効果検証についてです。Server2016以降から並列度の次数(dop)やスレッド数が追加されています。累積のうち並列処理時の最大のスレッド数#dopとthreadの違いはMSのblogに記載があります。https://blogs.msdn.microsoft.com/jpsql/2013/10/15/dop/直近の実行での並列度や平均並列度は上記動的管理ビューを見ることでわかります。以下のようなSQLで確認することができます。#以前作ったCPU、IO負荷の高いクエリの使いまわしですが。(RECOMPILE)グラフィカル表示の実行プランを確認する場合、次のアイコンがあれば並列実行される可能性があります。実際の実行プランを取得するとアイコンにカーソルを合わせると実行回数として並列度が確認できます。それぞれのスレッドでの処理行数やCPU時間や実行時間も見ることができます。Streamsなので、イメージ的には次のような動きかと。「実際の実行プラン」でなければこの情報は見ることができないため、拡張イベント等の仕込みが無い場合は後から見ることができません。その場合は上記で説明した動的管理ビューからの平均・最大並列数で確認するのが現実的でしょうか。並列処理した場合のパフォーマンスコスト並列処理した場合のクエリを比較して負荷の違いを見てみます。試しにMaxdopを1~4にした場合の各5回の実行からの平均統計で比較すると次のような結果となります。OFF非並列時の読み取りページと実行プラン並列時の読み取りページ実行プラン上記結果から、並列度が高いとCPU負荷が高くなるかわりに実行時間が少し早くなることがわかるかと思います。CPUTimeが実行時間(ElapsedTime)を超えている場合は、並列で処理している結果であると言えそうです。わずかですが並列無しに比べ、読み取りページもわずかに増えておりメモリにも多少なりとも影響を与えていると言えるでしょう。複数のCPUでクエリを処理して並列処理される場合、実行時間を短縮することができます。並列クエリの実行では、取得するデータセットを小さく分割して、分割されたデータセットをそれぞれのワーカー・スレッドが並列処理することでクエリ実行の実行時間を短縮することができます。並列クエリとなる基準は以下の通りです。1.-数のCPUが搭載されている環境か2.十分な数のスレッドが使用できるか3.並列プランを使用するに適しているクエリ、もしくはインデックスか4.処理行が十分に多いか5.分布統計が使用できるかMSに詳しい説明があります。parallelismオプションを使用して設定するか、特定のクエリの並列を最大並列度(MAXDOP)で指定することができます。デフォルトは0で、この場合論理プロセッサに対し最大限に使用することがある状態となります。インスタンス全体にかかる設定の確認をクエリにて行う場合は次のように確認できます。GOまたは、プロパティからも確認することができます。左ペインのオブジェクトエクスプローラからサーバを右クリックし、「プロパティ」を選択。「並列処理の最大限度」を確認。設定を変更する場合、次のように変更できます。OVERRIDE;GO特定のクエリに対するMAXDOPを変える場合は、オプション句でMAXDOPを以下のように指定して実行します。この場合、上記のsp_configureの設定値を超えた並列度を設定することができます。2)並列実行時は実行時間が早くなる分、CPUやメモリへの影響もありますので注意が必要ですが、MSとしてのMaxDOPは推奨値が8以下となっているようです。以下は参考になるかと思いますので、目を通しておくとよいでしょう。PowerShell完全に自分用の備忘録です。Serverに対しストアドを作ることなく、SQLの戻り値を使ってイベントログに書き込みをするスクリプトです。イベントログへの書き込み事前にイベントログにソースを登録したことがなければNew-Eventlogにて作成しておきます。今回はTemp_alertとしておきます。"Temp_alert"イベントログへの書き込みはWrite-Eventlogにて行います。"test"イベントログに書き込みができていることを確認します。PowerShellからのSQLの実行PowerShellからSQLを実行するので、Invoke-Sqlcmdを使用します。$Input_countその他詳しいオプションは下記を参照してください。https://msdn.microsoft.com/ja-jp/library/cc281720.aspx今回は単純に数をカウントするだけのSQLと詳細情報を参照する以下クエリを用意しました。[dbo].[JOBS]PowerShellからSQLを実行してイベントログへ書き込みSQLでのカウントが0の場合は問題ないことをログに書き込み、0でない場合エラーとしてイベントログに書き込むサンプルです。以下のようなフォルダ構成で実行します。PowerShellは以下の通りです。$Message}結果は以下のようにイベントログに書き込めていることがわかります。本番環境だと申請なしにストアドを作成するわけにもいかないこともありますのでストアドを使わない方法として一時しのぎに使おうかと。ブログを報告するスターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。