【SQL Server】【Windows】セキュリティイベントログにログイン失敗のログを記録する
イベントログの「セキュリティ」にSQL Serverログインの失敗を記録する際の手順です。
以下参考のURLを実際に実行してみたものになります。
https://msdn.microsoft.com/ja-jp/library/cc645889.aspx
まず、auditpol.exeで成功・失敗の監査を有効にします。
ただし、上記URL記載のコマンドを実行してもエラーとなりますので、一部修正する必要があります。
auditpol.exe /set /subcategory:”Application Generated” /failure:enable /success:enable
私の環境では、サブカテゴリ名が日本語になっており、コマンド内でも日本語にする必要がありました。
auditpol.exe /set /subcategory:”生成されたアプリケーション” /failure:enable /success:enable
次に"セキュリティ監査の生成"権限をSQL Serverの起動ユーザに許可します。
「ファイル名を指定して実行」で"gpedit.msc"を入力します。
ローカル セキュリティ ポリシー ツールで、 [セキュリティの設定]、 [ローカル ポリシー]の順に展開し、 [ユーザー権利の割り当て]をクリックして、右ペインに表示される[セキュリティ監査の生成]をダブルクリックします。
[ローカル セキュリティの設定] タブの [ユーザーまたはグループの追加]をクリックします。
追加するユーザアカウントとしてSQL Serverの起動ユーザを選択します。
設定が完了したらSQL Serverに反映させるためにインスタンスを再起動します。
次にSQL Server側で監査を追加します。監査ログをイベントログのセキュリティに記録するようにします。
今回はSQL Serverのインスタンス全体を監査対象とするサーバーレベルの監視を作成してみます。
オブジェクトエクスプローラのインスタンス名配下のツリー直下にある「監査」を選択し、右クリック、「新しい監査」を選択します。
「監査の出力先」をセキュリティログにします。
尚、監査ログはイベントログのアプリケーション、もしくは別ファイルに記録することもできます。
作成したら有効化しておきます。
どのイベントを監査ログとして記録するかという仕様は、"サーバー監査の仕様"にて設定します。
オブジェクトエクスプローラのインスタンス名配下のツリー直下にある「サーバー監査の仕様」を選択し、右クリック、「新しいサーバー監査の仕様」を選択します。
ログイン失敗を監査するために、監査アクションの種類で「FAILED_LOGIN_GROUP」を選択します。
ここでは、他にも以下のように監査仕様を選べます。
作成が完了したらこちらも有効にしておきます。
実際にログイン失敗が記録されているか確認をしてみます。
監査ログの表示は、SSMS上であれば作成した監査を右クリックして、「監査ログの表示」を選択します。
ユーザIDの誤りによるログ
パスワード誤りによるログ
実際にイベントログを確認してみると次のように記録できていることがわかります。
ユーザIDの誤りによるログ
パスワード誤りによるログ
データベースレベルを監査対象とする監査仕様は別途こちらから設定できますので、個別DB毎の監査仕様はこちらで定義するとよいでしょう
【SQL Server】bcpを使用したインポートとエクスポート
テストする際にテーブルのデータを一括でインポート・エクスポートする場合の方法の一つとしてbcpを使用することがありますが、オプションやフォーマットファイル出力のやり方をよく忘れるので覚書。
フォーマットファイルについては、bcpだけでなくBULK INSERTにも使えます。
bcpはコマンドラインからの実行になるので、バッチなどを作っておいて、簡単なテーブルバックアップ用のスクリプトなども作っておくと便利かと思います。
フォーマットファイルの作成
エクスポートやインポートをする対象テーブルのフォーマットファイルを作成します。
Syntax :
bcp table_or_view format nul -c -x -f formatfile.xml -t, -S server_name\instance_name -U login_id -P password -d database_name
Option :
-f | format_file |
-S | server_name [\instance_name] |
-U | login_id |
-P | password |
-d | database_name |
-t | field_term(default \t) |
その他詳しいオプションは下記を参照してください。
https://msdn.microsoft.com/ja-jp/library/ms162802.aspx
実際に使う際は次のようにコマンドプロンプトで実行します。
bcp dbo.EMPLOYEES format nul -c -x -f formatfile.xml -t, -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales
"-f"で指定するフォーマットファイルは、出力先パスとファイル名を記載してください。
出力されたフォーマットファイルはxml形式となります。
bcpによる一括エクスポート
先ほど作成したフォーマットファイルを使用してテーブルのデータを全てエクスポートします。
bcp dbo.EMPLOYEES out .\EMPLOYEES.csv -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales -f .\formatfile.xml -t, -o .\EMPLOYEES_output.log
"-o"によって出力されるのは処理ログで、"out"によって指定されたパスにエクスポート結果が出力されます。
"-f"で指定するフォーマットファイルは格納先を指定してください。
処理ログ
エクスポート結果(ヘッダーはつけられません)
テーブルを指定するのではなく、SQLとqueryoutを使用して出力対象をいじることも可能です。
WHERE句を使って条件に合うデータのみ抽出する等に使えるかと。
bcp "SELECT TOP(10) * FROM dbo.EMPLOYEES" queryout .\EMPLOYEES_TOP10.csv -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales -f .\formatfile.xml -t, -o .\EMPLOYEES_TOP10_output.log
bcpによる一括インポート
作成したフォーマットファイルを使用して、データファイルをテーブルに全てインポートできます。
"-i"で指定するインポート用のデータファイルは、格納パスとファイル名を指定してください。
bcp dbo.EMPLOYEES in .\EMPLOYEES.csv -S YUUSUKE-VAIO\INS_NISHI2016 -U sa -P system -d sales -f .\formatfile.xml -t, -o .\EMPLOYEES_input.log
処理ログ
【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part2
前回に引き続き実行プランとパフォーマンス情報の取得について説明します。今回はトレースとデータ蓄積による情報取得です。
前回記事はこちらを参照して下さい。
memorandom-nishi.hatenablog.jp
3.トレースして取得する
トレースを仕掛けて詳細情報を取得する方法は2つあります。
ただし後述するSQLプロファイラーは非常にトレース負荷が高く本番環境で使用することは皆無かと思います。
使用する場合は、ブラックボックスで内部でどのようなSQLが実行されているかわからないものを確認したり、そのSQL実行開始のコンパイルからロック状況など実行状況を一連の流れで確認するなどに使えますが、基本的には拡張イベントで事足りるでしょう。
拡張イベントもSQLプロファイラーに比べ軽量とはいえ、負荷がかかることに変わりはないため、使用の際は注意してください。
拡張イベント(XEvent)
拡張イベントを設定することで、3種類の実行プランをとることができます。特に下記③query_post_execution_showplanは、前回記事で説明したSET STATISTICS XML相当の実際の実行プランであるため、有益な情報になるかと思います。
設定手順は以下のとおりです。
オブジェクトエクスプローラーの「管理」-「拡張イベント」-「セッション」で右クリックを押下し、「新規セッションウィザード」を選択します。
作成する拡張イベントのセッションに名前を付けます。今回はテンプレートなしで作成します。
取得したい実行プランのイベントを選択します。
選択したイベントでトレース対象をフィルタすることも可能です。
作成が完了したら、オブジェクトエクスプローラーの「管理」-「セッション」に追加されていることを確認します。
パフォーマンス情報によって取得するものを絞る場合は次のように追加でフィルタします。
準備ができたらセッションを開始します。
今回はリアルタイムで監視したいため、「ライブデータの監視」を選択します。
データが採取できたらセッションを停止します。
結果以下のようにパフォーマンス情報と実行プランが取得できていることが確認できます。
①クエリコンパイル後・クエリ実行前の推定の実行プラン(query_post_compilation_showplan)
②クエリコンパイル後・クエリ最適化時(実行時)の推定の実行プラン(query_pre_execution_showplan)
③クエリ実行後の実際の実行プラン(query_post_execution_showplan)
また、拡張イベントで取得できるイベントは、以下で確認できるので一度どのようなものが取得できるかざっと確認しておくとよいでしょう。
SELECT * FROM sys.dm_xe_objects
SQL Server Profiler
SQL Serverプロファイラーではプロファイラーを実行してから終了するまで指定したイベントをトレースできます。
非常に負荷が高いためあまり実用的ではありませんが、開発環境等で詳細情報を取得する場合等に使えないこともありません。
使い方としては以下のとおりです。
SQL Server Profilerを起動します。
認証して、トレースのプロパティを開きます。
「イベントの選択」タブで、「すべてのイベントを表示する」にチェックをいれます。
Events列のPerformanceをクリックします。
取得する実行プランを選びます。
「列フィルター」にてトレースする対象をフィルタすることができます。
実行すると対象のイベントについてトレースを開始します。
以下のようにパフォーマンス情報と実行プランが取得できていることが確認できます。
クエリコンパイル後の推定の実行プラン(Showplan ALL For Query Cimpile、Showplan XML For Query Cimpile)
Showplan ALL For Query Cimpile
Showplan XML For Query Cimpile
クエリコンパイル後、クエリ実行前の推定の実行プラン(Showplan ALL、Showplan TEXT、Showplan XML)
Showplan XML
実際の実行プラン(Showplan Statistics Profile、Showplan XML Statistics Profile)
Showplan Statistics Profile
Showplan XML Statistics Profile
4.データを蓄積して分析する
データを蓄積していくことで変化や傾向を分析する方法は、大きく分けてシステムデータコレクション(MDW)とクエリストアが有用かと思います。
システムデータコレクション(MDW)
システムデータコレクション(以下MDW)については過去記事で確認方法を説明していますので、こちらを参照してください。
MDWでは、クエリの実行プランやパフォーマンス情報に合わせて、当該クエリの待機イベントの状況やブロッキングの情報などが確認できるため、チューニングの際にボトルネックの発見がしやすくなります。OracleのStatspackに似ているかと思います。
memorandom-nishi.hatenablog.jp
クエリストア
こちらも過去記事でざっと説明していますので参照して頂ければと思います。
SQLServer2016からしか使用できませんが、クエリストアを使うことでクエリの急な遅延等のパフォーマンス変化時の分析に役立つかと思います。
memorandom-nishi.hatenablog.jp
【SQL Server】クエリの実行プランとパフォーマンス情報の取得方法Part1
クエリのパフォーマンス状態の分析において、必ずといってよいほど必要となる実行プランやCPU時間や実行時間、実行回数などの取得方法について、いくつか方法がありますのでまとめます。
大きく分けて以下のような方法で取得できます。
1.動的管理ビューで取得する
これは、実際に実行済みでかつキャッシュ上に残存している場合に取得できます。本番環境などで余計な負荷をかけずに取得できる方法かと思います。
ただし、キャッシュされていない実行プランはもちろん取得できないことになります。
クエリでDMV組み合わせて取得する
例として、CPU負荷が高いTOP100のSQLで、CPUtimeや実行時間、実行回数も同時に取得するクエリです。
USE master GO SET NOCOUNT ON GO SELECT TOP 100 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) AS [row_no] , db_name(st.dbid) AS [database_name] , creation_time , last_execution_time , (total_worker_time+0.0)/1000 AS [total_worker_time(ms)] , (total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime(ms)] , (total_elapsed_time+0.0)/1000 AS [total_elapsed_time(ms)] , (total_elapsed_time+0.0)/(execution_count*1000) AS [AvgElapsedTime(ms)] , total_logical_reads AS [LogicalReads(page)] , total_logical_writes AS [logicalWrites(page)] , total_logical_reads+total_logical_writes AS [AggIO(page)] , (total_logical_reads+total_logical_writes)/(execution_count + 0.0) AS [AvgIO(page)] , execution_count , total_rows , st.text AS [batch_query_text] , CASE WHEN sql_handle IS NULL THEN ' ' ELSE ( SUBSTRING(st.text,(qs.statement_start_offset+2)/2,(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.text))*2 ELSE qs.statement_end_offset END - qs.statement_start_offset) /2 ) ) END AS [statement_query_text] , plan_generation_num , qp.query_plan FROM sys.dm_exec_query_stats AS [qs] CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS [st] CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [qp] WHERE total_worker_time > 0 ORDER BY total_worker_time DESC OPTION (RECOMPILE)
また、実行プランなしでCPU時間や実行時間等が知りたい場合、上記クエリのほかにレポートに出力も可能ですので、下記記事をご確認ください。
memorandom-nishi.hatenablog.jp
利用状況モニターから取得する
実際に現在実行されているものや比較的負荷が高いものが対象にあります。DMVで取得するものを利用状況モニターから取得しているだけですので、こちらも過去記事をご確認いただければと思います。
memorandom-nishi.hatenablog.jp
2.実行時にSET~を使用する
この場合、これから実行しようとするクエリの推定プラン(実行自体はしない)や実際の実行したクエリが取得できます。
推定の実行プランを取得する
SET SHOWPLAN_xx で取得する場合、クエリ自体は実行せずに推測の実行プランを確認することができます。
グラフィカルな実行プランで確認したい場合は次のように確認します。
SET SHOWPLAN_XML ON GO SELECT * FROM dbo.EMPLOYEES AS [EMP] INNER JOIN dbo.DEPARTMENTS AS [DEPA] ON [EMP].MANAGER_ID = [DEPA].MANAGER_ID SET SHOWPLAN_XML OFF GO
また、クエリ実行時にSSMSの「推定実行プランの表示」をクリックしても同様の結果が取得できます。
グラフィカルな実行プランの場合、各アイコンやノードにマウスカーソルを合わせると詳細情報として、CPU・IOのコストや予測行数などを確認できます。
SSMSがインストールされている環境であれば視覚的に確認できてよいかと思います。
テキスト形式で実行プランを取得したい場合、実行プランのみ取得するものと、詳細情報も併せて取得するものの2パターンあります。
実行プランのみ取得する場合は次のように実行します。
SET SHOWPLAN_TEXT ON GO --実行プランを取得したいクエリ SELECT * FROM dbo.EMPLOYEES AS [EMP] INNER JOIN dbo.DEPARTMENTS AS [DEPA] ON [EMP].MANAGER_ID = [DEPA].MANAGER_ID GO SET SHOWPLAN_TEXT OFF GO
実行プランと詳細情報の場合は次のように実行します。
SET SHOWPLAN_ALL ON GO --実行プランを取得したいクエリ SELECT * FROM dbo.EMPLOYEES AS [EMP] INNER JOIN dbo.DEPARTMENTS AS [DEPA] ON [EMP].MANAGER_ID = [DEPA].MANAGER_ID GO SET SHOWPLAN_ALL OFF GO
情報としては上記グラフィカルな実行プランではマウスカーソルにて確認できる情報がまとめて確認できるような形となります。テキスト形式で保存すれば、環境に依存せず他人と情報を共有できて便利かと思います。
実際の実行プランを取得する
SET STATISTICS xx で取得する場合、クエリ自体は実行して実際の実行プランを確認することができます。
クエリを実際に実行してしまうため、本番環境での使用は十分注意が必要ですが、その分予測行数と実際の行数など実行時の情報が確認できるため非常に有効な情報となります。
グラフィカルな実際の実行プランで確認したい場合は次のように確認します。
SET STATISTICS XML ON --実行プランを取得したいクエリ SELECT * FROM dbo.EMPLOYEES AS [EMP] INNER JOIN dbo.DEPARTMENTS AS [DEPA] ON [EMP].MANAGER_ID = [DEPA].MANAGER_ID GO SET STATISTICS XML OFF
推定の実行プランと比較すると、各アイコンやノードにマウスカーソルを合わせた際に実際の行数や実行回数などが追加で取得できていることが確認できます。
テキスト形式で実際の実行プランで確認したい場合は次のように確認します。
SET STATISTICS PROFILE ON --実行プランを取得したいクエリ SELECT * FROM dbo.EMPLOYEES AS [EMP] INNER JOIN dbo.DEPARTMENTS AS [DEPA] ON [EMP].MANAGER_ID = [DEPA].MANAGER_ID GO SET STATISTICS PROFILE OFF
こちらも推定の実行プランに比べ、実際の行数や実行回数が追加されていることがわかるかと思います。
また、実行プランの取得ではありませんが、実行して分析する際には次のようにクエリがどのページを読み取ったかや構文解析時間、クエリ実行時間も合わせて取得しておくと解析時に便利かと思います。
SET STATISTICS IO ON SET STATISTICS TIME ON --実行プランを取得したいクエリ SELECT * FROM dbo.EMPLOYEES AS [EMP] INNER JOIN dbo.DEPARTMENTS AS [DEPA] ON [EMP].MANAGER_ID = [DEPA].MANAGER_ID GO SET STATISTICS TIME OFF SET STATISTICS IO OFF
上記のほかにトレースして実行計画やパフォーマンス情報を確認する方法はありますが、それは次回記載します。
【SQL Server】CPU使用率、IO負荷の高いクエリレポート
クエリのパフォーマンス状況の確認方法として、DMVを組み合わせた「CPU使用率の高いクエリ」や「IO負荷の高いクエリ」を取得するクエリを事前に準備しているシステム管理者も多いかと思いますが、グラフィカルなレポートという形で取得するのも視覚的に確認できてよいかと思いますので、出力方法の説明です。
※デメリットとして、レポート出力ではSQLハンドルは出力できているものの、実行プランは別途取得する必要があります。
特に次のレポートは比較的利用価値が高いのではないでしょうか。
「パフォーマンス - バッチ実行の統計」
「パフォーマンス - 平均IO数ごとの上位のクエリ」
「パフォーマンス - IOの総数ごとの上位のクエリ」
「パフォーマンス - CPUの平均時間ごとの上位のクエリ」
「パフォーマンス - CPUの総時間ごとの上位のクエリ」
尚、これらのレポートは出力時に裏でSQLを実行し、動的管理ビューからデータ取得をしていますので、キャッシュ上に残存しているデータのみがレポート対象となります。
レポート内容
①「パフォーマンス - バッチ実行の統計」
これはバッチごとの平均・総CPU、論理読み取り数、論理書き込み数についてのレポートになります。
平均はすべてデフォルト表示で、それぞれのプラス表示をクリックするとトータルの取得値が確認できます。
キャッシュ上に残存しているデータのうち、バッチ単位で高負荷なものを確認することができます。
またバッチ番号のプラス表示をクリックするとステートメント毎の情報が確認できます。
実際に裏でレポート表示に使われているクエリは次のようなものになります。
exec sp_executesql @stmt=N'begin try declare @dbid int; set @dbid = db_id(); declare @cnt int; declare @record_count int; declare @sql_handle varbinary(64); declare @sql_handle_string varchar(130); declare @grand_total_worker_time float ; declare @grand_total_IO float ; declare @sql_handle_convert_table table( row_id int identity , t_sql_handle varbinary(64) , t_display_option varchar(140) collate database_default , t_display_optionIO varchar(140) collate database_default , t_sql_handle_text varchar(140) collate database_default , t_SPRank int , t_SPRank2 int , t_SQLStatement varchar(max) collate database_default , t_execution_count int , t_plan_generation_num int , t_last_execution_time datetime , t_avg_worker_time float , t_total_worker_time bigint , t_last_worker_time bigint , t_min_worker_time bigint , t_max_worker_time bigint , t_avg_logical_reads float , t_total_logical_reads bigint , t_last_logical_reads bigint , t_min_logical_reads bigint , t_max_logical_reads bigint , t_avg_logical_writes float , t_total_logical_writes bigint , t_last_logical_writes bigint , t_min_logical_writes bigint , t_max_logical_writes bigint , t_avg_IO float , t_total_IO bigint , t_last_IO bigint , t_min_IO bigint , t_max_IO bigint ); declare @objects table ( obj_rank int , total_cpu bigint , total_reads bigint , total_writes bigint , total_io bigint , avg_cpu bigint , avg_reads bigint , avg_writes bigint , avg_io bigint , cpu_rank int , total_cpu_rank int , read_rank int , write_rank int , io_rank int ); insert into @sql_handle_convert_table Select sql_handle , sql_handle as chart_display_option , sql_handle as chart_display_optionIO , master.dbo.fn_varbintohexstr(sql_handle) , dense_rank() over (order by s1.sql_handle) as SPRank , dense_rank() over (partition by s1.sql_handle order by s1.statement_start_offset) as SPRank2 , (select top 1 substring(text,(s1.statement_start_offset+2)/2, (case when s1.statement_end_offset = -1 then len(convert(nvarchar(max),text))*2 else s1.statement_end_offset end - s1.statement_start_offset) /2 ) from sys.dm_exec_sql_text(s1.sql_handle)) as [SQL Statement] , execution_count , plan_generation_num , last_execution_time , ((total_worker_time+0.0)/execution_count)/1000 as [avg_worker_time] , total_worker_time/1000 , last_worker_time/1000 , min_worker_time/1000 , max_worker_time/1000 , ((total_logical_reads+0.0)/execution_count) as [avg_logical_reads] , total_logical_reads , last_logical_reads , min_logical_reads , max_logical_reads , ((total_logical_writes+0.0)/execution_count) as [avg_logical_writes] , total_logical_writes , last_logical_writes , min_logical_writes , max_logical_writes , ((total_logical_writes+0.0)/execution_count + (total_logical_reads+0.0)/execution_count) as [avg_IO] , total_logical_writes + total_logical_reads , last_logical_writes +last_logical_reads , min_logical_writes +min_logical_reads , max_logical_writes + max_logical_reads from sys.dm_exec_query_stats s1 cross apply sys.dm_exec_sql_text(sql_handle) as s2 where s2.objectid is null order by s1.sql_handle; select @grand_total_worker_time = sum(t_total_worker_time) , @grand_total_IO = sum(t_total_logical_reads + t_total_logical_writes) from @sql_handle_convert_table; select @grand_total_worker_time = case when @grand_total_worker_time > 0 then @grand_total_worker_time else 1.0 end ; select @grand_total_IO = case when @grand_total_IO > 0 then @grand_total_IO else 1.0 end ; Insert into @objects select t_SPRank , sum(t_total_worker_time) , sum(t_total_logical_reads) , sum(t_total_logical_writes) , sum(t_total_IO) , sum(t_avg_worker_time) as avg_cpu , sum(t_avg_logical_reads) , sum(t_avg_logical_writes) , sum(t_avg_IO) , rank() over(order by sum(t_avg_worker_time) desc) , row_number() over(order by sum(t_total_worker_time) desc) , row_number() over(order by sum(t_avg_logical_reads) desc) , row_number() over(order by sum(t_avg_logical_writes) desc) , row_number() over(order by sum(t_total_IO) desc) from @sql_handle_convert_table group by t_SPRank ; update @sql_handle_convert_table set t_display_option = ''show_total'' where t_SPRank in (select obj_rank from @objects where (total_cpu+0.0)/@grand_total_worker_time < 0.05) ; update @sql_handle_convert_table set t_display_option = t_sql_handle_text where t_SPRank in (select obj_rank from @objects where total_cpu_rank <= 5) ; update @sql_handle_convert_table set t_display_option = ''show_total'' where t_SPRank in (select obj_rank from @objects where (total_cpu+0.0)/@grand_total_worker_time < 0.005); update @sql_handle_convert_table set t_display_optionIO = ''show_total'' where t_SPRank in (select obj_rank from @objects where (total_io+0.0)/@grand_total_IO < 0.05); update @sql_handle_convert_table set t_display_optionIO = t_sql_handle_text where t_SPRank in (select obj_rank from @objects where io_rank <= 5) ; update @sql_handle_convert_table set t_display_optionIO = ''show_total'' where t_SPRank in (select obj_rank from @objects where (total_io+0.0)/@grand_total_IO < 0.005); select (s.t_SPRank)%2 as l1 , (dense_rank() over(order by s.t_SPRank,s.row_id))%2 as l2 , s.* , ob.cpu_rank as t_CPURank , ob.read_rank as t_ReadRank , ob.write_rank as t_WriteRank from @sql_handle_convert_table s join @objects ob on (s.t_SPRank = ob.obj_rank) end try begin catch select -100 as l1 , ERROR_NUMBER() as l2 , ERROR_SEVERITY() as row_id , ERROR_STATE() as t_sql_handle , ERROR_MESSAGE() as t_display_option , 1 as t_display_optionIO , 1 as t_sql_handle_text , 1 as t_SPRank , 1 as t_SPRank2 , 1 as t_SQLStatement , 1 as t_execution_count , 1 as t_plan_generation_num , 1 as t_last_execution_time , 1 as t_avg_worker_time , 1 as t_total_worker_time , 1 as t_last_worker_time , 1 as t_min_worker_time , 1 as t_max_worker_time , 1 as t_avg_logical_reads , 1 as t_total_logical_reads , 1 as t_last_logical_reads , 1 as t_min_logical_reads , 1 as t_max_logical_reads , 1 as t_avg_logical_writes , 1 as t_total_logical_writes , 1 as t_last_logical_writes , 1 as t_min_logical_writes , 1 as t_max_logical_writes , 1 as t_avg_IO , 1 as t_total_IO , 1 as t_last_IO , 1 as t_min_IO , 1 as t_max_IO, 1 as t_CPURank, 1 as t_ReadRank, 1 as t_WriteRank end catch',@params=N''
ちなみにSQLserverでは、複数ステートメントのまとまりをバッチと呼びます。(ステートメントは単一のクエリのことです。)
例えば、以下のようなものがあった場合は、
SELECT ~
UPDATE ~
GO
SELECT、UPDATEはそれぞれ1ステートメントで、SELECTからGOまでのひとかたまりが1バッチとなります。
②「パフォーマンス - 平均IO数ごとの上位のクエリ」
これはキャッシュ上に残存するクエリのうち、1回あたりのIO数が多い上位クエリについてのレポートになります。
レポート上には同クエリについてそれぞれIOの総数で見た場合の負荷を同時に見ることができ便利です。
平均IO数が多いが、総IO数があまり多くない場合、全体への負荷は高くないが、単一クエリとしては高負荷である場合がありますので、非効率なクエリ(インデックスが不足しているなど)でないか注意が必要かと思います。
実際に裏でレポート表示に使われているクエリは次のようなものになります。
exec sp_executesql @stmt=N'begin try select top 10 rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no , (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1 , creation_time , last_execution_time , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [LogicalWrites] , execution_count , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO] , case when sql_handle IS NULL then '' '' else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 )) end as query_text , db_name(st.dbid) as database_name , st.objectid as object_id from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st where (total_logical_reads+total_logical_writes ) > 0 order by [AvgIO] desc end try begin catch select -100 AS row_no , 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS AvgCPUTime, 1 AS logicalReads, 1 AS LogicalWrites , ERROR_NUMBER() AS execution_count , ERROR_SEVERITY() AS AggIO , ERROR_STATE() AS AvgIO , ERROR_MESSAGE() AS query_text end catch',@params=N''
レポート形式ではなく、同様の情報と同時に実行プランも取得したい場合は次のとおり。
select top 10 rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ) as row_no , (rank() over (order by (total_logical_reads+total_logical_writes)/(execution_count+0.0) desc,sql_handle,statement_start_offset ))%2 as l1 , creation_time , last_execution_time , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [LogicalWrites] , execution_count , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes+0.0)/execution_count as [AvgIO] , case when sql_handle IS NULL then ' ' else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 )) end as query_text , db_name(st.dbid) as database_name , st.objectid as object_id , qs.sql_handle , qp.query_plan from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp where (total_logical_reads+total_logical_writes ) > 0 order by [AvgIO] desc
③「パフォーマンス - IOの総数ごとの上位のクエリ」
これはキャッシュ上に残存するクエリのうち、IOの総数が多い上位クエリについてのレポートになります。
上記②と同様にそれぞれ平均IO数がどの程度か確認することができます。総IO数が多く、かつ平均IO数も多い場合は、チューニングすると全体に対し大きな効果が得られますので、ここからチューニング対象を見ていくとよいかと思います。
実際に裏でレポート表示に使われているクエリは次のようなものになります。
見ての通り、②のレポートで実行しているものを総IO数にてソートしたものと同様になります。
exec sp_executesql @stmt=N'begin try select top 10 rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ) as row_no , (rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ))%2 as l1 , creation_time , last_execution_time , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [LogicalWrites] , execution_count , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO] , case when sql_handle IS NULL then '' '' else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) ) end as query_text , db_name(st.dbid) as database_name , st.objectid as object_id from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(sql_handle) st where total_logical_reads+total_logical_writes > 0 order by [AggIO] desc end try begin catch select -100 AS row_no , 1 AS l1, 1 AS creation_time, 1 AS last_execution_time, 1 AS total_worker_time, 1 AS Avg_CPU_Time, 1 AS logicalReads, 1 AS LogicalWrites , ERROR_NUMBER() AS execution_count , ERROR_SEVERITY() AS AggIO , ERROR_STATE() AS AvgIO , ERROR_MESSAGE() AS query_text end catch',@params=N''
よって、レポート形式ではなく、同様の情報と同時に実行プランも取得したい場合は次のとおり。
select top 10 rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ) as row_no , (rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ))%2 as l1 , creation_time , last_execution_time , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [LogicalWrites] , execution_count , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO] , case when sql_handle IS NULL then ' ' else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) ) end as query_text , db_name(st.dbid) as database_name , st.objectid as object_id , qs.sql_handle , qp.query_plan from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp where total_logical_reads+total_logical_writes > 0 order by [AggIO] desc
④「パフォーマンス - CPUの平均時間ごとの上位のクエリ」
これはキャッシュ上に残存するクエリのうち、1回あたりのCPU使用量が多い上位クエリについてのレポートになります。
レポート上には同クエリについてそれぞれCPU使用量の総時間で見た場合の負荷を同時に見ることができます。
②と同様に、平均使用時間に比べて総時間が多くない場合は、単一クエリとしてはCPUに対し高負荷である場合がありますので、非効率なクエリでないか注意が必要かと思います。
実際に裏でレポート表示に使われているクエリは②のレポートで実行しているものを平均CPU時間にてソートしたものと同様になります。
よって、レポート形式ではなく、同様の情報と同時に実行プランも取得したい場合は次のとおり。
select top 10 rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ) as row_no , (rank() over(order by (total_worker_time+0.0)/execution_count desc,sql_handle,statement_start_offset ))%2 as l1 , creation_time , last_execution_time , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [LogicalWrites] , execution_count , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO] , case when sql_handle IS NULL then ' ' else ( substring(st.text,(qs.statement_start_offset+2)/2, (case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) ) end as query_text , db_name(st.dbid) as db_name , st.objectid as object_id , qs.sql_handle , qp.query_plan from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp where total_worker_time > 0 order by [AvgCPUTime] desc
⑤「パフォーマンス - CPUの総時間ごとの上位のクエリ」
これはキャッシュ上に残存するクエリのうち、総CPU使用量が多い上位クエリについてのレポートになります。
レポート上には同クエリについてそれぞれ平均CPU使用時間で見た場合の負荷を同時に見ることができます。
③と同様に、総時間が長く、かつ平均使用時間も長い場合、チューニングすると全体のCPU使用効率に対し大きな効果が得られますので、チューニング可否を検討するとよいかと思います。
実際に裏でレポート表示に使われているクエリは②のレポートで実行しているものを総CPU時間にてソートしたものと同様になります。
よって、レポート形式ではなく、同様の情報と同時に実行プランも取得したい場合は次のとおり。
select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no , (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1 , creation_time , last_execution_time , (total_worker_time+0.0)/1000 as total_worker_time , (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime] , total_logical_reads as [LogicalReads] , total_logical_writes as [logicalWrites] , execution_count , total_logical_reads+total_logical_writes as [AggIO] , (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO] , case when sql_handle IS NULL then ' ' else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) ) end as query_text , db_name(st.dbid) as database_name , st.objectid as object_id , qs.sql_handle , qp.query_plan from sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp where total_worker_time > 0 order by total_worker_time desc
これらのレポートは表示後に右クリックでPDF出力も可能であるため、他人に見せる際にも活用できますので、一度使ってみるとよいと思います。
【SQL Server】WHERE句で正規表現的な検索をする
検索をする際に、SQLServerでは正規表現を使うことはできません。
使い勝手は正規表現ほどではありませんが、似たようなものがありますので検証してみました。
まずは、テーブルを作成します。
CREATE TABLE TEXT_TEST ( [id] INT, [text] NVARCHAR(255) )
今回検証に使うテストデータは以下となります。
INSERT INTO TEXT_TEST VALUES (1, N'123'), (2, N'abc'), (3, N'aBc'), (4, N'a c'), (5, N'A C'), (6, N'123'), (7, N'漢字a') (8, N'1bc')
照合順序に左右されたくないため、ここではCOLLATE句でJapanese_BIN2を指定しています。
半角英数字以外を含むレコードを抽出
SELECT * FROM TEXT_TEST WHERE [text] LIKE '%[^-a-zA-Z0-9 ]%' COLLATE Japanese_BIN2
半角英数字カナを含む(半角記号除く)レコードを抽出
SELECT * FROM TEXT_TEST WHERE [text] LIKE '%[a-zA-Z0-9ア-ン ]%' COLLATE Japanese_BIN2
数字を含むレコードを抽出
SELECT * FROM TEXT_TEST WHERE [text] LIKE '%[0-9]%' COLLATE Japanese_BIN2
尚、照合順序についてはJapanese_CS_AS_KS_WSなどがありますが、意味としては以下のような形になります。
CS | 大文字、小文字を区別する |
CI | 大文字、小文字を区別しない |
AS | アクセントを区別する |
AI | アクセントを区別しない |
KS | ひらがな、カタカナを区別する |
KI | ひらがな、カタカナを区別しない |
WS | 全角、半角を区別する |
WI | 全角、半角を区別しない |
BIN2 | バイナリで区別する |
細かい指定や繰り返しの表現は出来ませんが、無いよりはマシですので頭の片隅に入れておくと役立つかも知れません。
【参考】
・パターン
https://technet.microsoft.com/ja-jp/library/ms187489(v=sql.105).aspx
・照合順序
https://msdn.microsoft.com/ja-jp/library/ms143726.aspx
https://blogs.msdn.microsoft.com/jpsql/2016/07/26/1-3/
2017/04/30 以下追記
半角文字(記号含む)を含むレコードを抽出
SELECT * FROM [dbo].[TEXT_TEST] WHERE [text] LIKE '%[ -~。-゚]%' COLLATE Japanese_BIN2
全角文字を含むレコードを抽出
SELECT * FROM [dbo].[TEXT_TEST] WHERE [text] LIKE '%[^ -~。-゚]%' COLLATE Japanese_BIN2
【SQL Server】データの格納ページ位置の確認方法
テーブルに格納されているレコードがどのページにあるのか確認する方法です。
デッドロックの調査や以前説明したラッチ状況を確認する場合などに使えるかと思います。
sys.fn_PhysLocFormatter
今回確認に使うundocumentedなファンクションです。
どのような内容なのかチェックします。
sp_helptext [sys.fn_PhysLocCracker]
上記から、sys.fn_PhysLocCrackerの引数にphysical_locatorを渡して実行すればよいことがわかります。
試しに実行してみます。
SELECT * FROM dbo.EMPLOYEES CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS fPLC ORDER BY fPLC.file_id, fPLC.page_id, fPLC.slot_id
sys.dm_db_database_page_allocations
また、データだけでなくクラスターインデックスなどに存在しているルートや中間ノードがどのページにあるかを知りたい場合は、以下のようなクエリでわかるかと思います。
SELECT [dpa].[page_level] AS [page_level], [dpa].[allocated_page_page_id] AS [page_id], [i].[name] AS [index_name], [dpa].[page_type_desc], [dpa].[previous_page_page_id], [dpa].[next_page_page_id] FROM sys.dm_db_database_page_allocations( DB_ID('sales'), OBJECT_ID('dbo.EMPLOYEES'), 1, NULL, 'DETAILED' ) AS [dpa] INNER JOIN sys.indexes AS [i] ON [dpa].[object_id] = [i].[object_id] AND [dpa].[index_id] = [i].[index_id] WHERE [dpa].[page_level] IS NOT NULL ORDER BY [dpa].[page_level] DESC, [dpa].[allocated_page_page_id]
尚、sys.dm_db_database_page_allocationsの実行の仕方は次のとおりです。
Syntax :
sys.dm_db_database_page_allocations
(@DatabaseId , @TableId , @IndexId , @PartionID , @Mode)
Parameters :
@DatabaseId | database id [not null] |
@TableId | object id |
@IndexId | index id |
@PartionI | partition id |
@Mode | LIMITED or DETAILED |
例えば、以前説明したもの【SQL Server】ロックとラッチのトレース - 小物SEのメモ帳
と同じものを確かめてみると、ラッチを確保していたルート、中間ノード、リーフ(データ)のページと一致することがわかります。
※page_levelが高いほど上位のノードです。
ルートページ:75715
中間1ページ:5615、75716、109086、143394
中間2ページ:5009、5624、5958、6209…
あまり普段使用しませんが、レコードがどのページにどれだけ格納されているか、インデックスのノードがどのページに格納されているのか調べる方法となりますので把握しておいて損はないかと。