【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'１２３'), (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…
あまり普段使用しませんが、レコードがどのページにどれだけ格納されているか、インデックスのノードがどのページに格納されているのか調べる方法となりますので把握しておいて損はないかと。
【SQL Server】ロックとラッチのトレース
SQL Serverの待機イベントの一つであるロックとラッチがどのような順番で獲得され、解放されるのかトレースしてみました。
拡張イベントの設定
トレースするにあたって拡張イベントを使います。
今回試すSQLはセッション63にて実行するので、フィルタしてあります。
CREATE EVENT SESSION [Lock_and_Latch_Mon] ON SERVER ADD EVENT sqlserver.latch_acquired( ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text) WHERE ([package0].[equal_uint64]([class],(28)) AND [sqlserver].[session_id]=(63))), ADD EVENT sqlserver.latch_released( ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text) WHERE ([package0].[equal_uint64]([class],(28)) AND [sqlserver].[session_id]=(63))), ADD EVENT sqlserver.lock_acquired( ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text) WHERE ([sqlserver].[session_id]=(63))), ADD EVENT sqlserver.lock_released( ACTION(sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text) WHERE ([sqlserver].[session_id]=(63))) ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL13.INS_NISHI2016\MSSQL\Log\Lock_and_Latch_Mon.xel') WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO
拡張イベントによるトレース
拡張イベントを作成できたら、セッションを開始します。
セッション開始後、状況をリアルタイムで確認するために、「ライブデータの監視(L)」を選択します。
対象のセッションで今回試したいクエリを実行してみます。
先ほど「ライブデータの監視(L)」で開いたウィンドウに結果が表示されているかと思います。
クエリ実行後は拡張イベントによる監視を停止します。
トレース結果の確認
拡張イベントによるトレース結果から処理の流れを結果を見てみます。
①OBJECTに対するインテント共有ロック獲得
②ページ：75715に対する共有ページラッチ獲得
③ページ：5615に対する共有ページラッチ獲得
④ページ：75715に対する共有ページラッチ解放
⑤ページ：5009に対する共有ページラッチ獲得
⑥ページ：5615に対する共有ページラッチ解放
⑦ページ：5171に対する共有ページラッチ獲得
⑧ページ：5009に対する共有ページラッチ解放
⑨ページ：5171に対するインテント共有ロック獲得
⑩ページ：5171に対するインテント共有ロック解放
⑪ページ：5171に対する共有ページラッチ解放
⑫OBJECTに対するインテント共有ロック解放
実行プランは下記のようなシンプルなものです。
今回のクエリはClustered Index Seekのみであり、このクラスターインデックスの奥行は4であるためページラッチの動きは以下のようなイメージになるかと思います。
まずはロックを獲得し、その次にインデックスのルートページからラッチを獲得。次のノードのページからラッチを獲得できたら、前のラッチを解放するということを繰り返して、目的のページまで到達するようなイメージでしょうか。
更新処理や複雑なクエリだとロックやラッチはもっと複雑になるかと思いますが、簡単な処理で処理構造を理解しておくことは重要かと思います。
【SQL Server】統計情報のヒストグラムと実行プランの予測行数
SQL Serverでは実行プランの決定における情報のうちの一つとして統計情報があります。
その統計情報のうちデータの分布を表すヒストグラムについて説明します。
統計情報は下記クエリで取得できます。
-- 引数は1つ目がテーブル名、2つ目が統計名 -- 結果セットをまとめて取得 DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH NO_INFOMSGS -- ヒストグラムのみ個別に結果セットを取得 DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH HISTOGRAM,NO_INFOMSGS
ヒストグラムとして取得できる情報は以下となります。
| カラム名 | 説明 |
| RANGE_HI_KEY | ヒストグラム区間の上限の列値 |
| RANGE_ROWS | ヒストグラム区間内 (上限は除く) に列値がある行の予測数 |
| EQ_ROWS | ヒストグラム区間の上限と列値が等しい行の予測数 |
| DISTINCT_RANGE_ROWS | ヒストグラム区間内 (上限は除く) にある個別の列値を持つ行の予測数 |
| AVG_RANGE_ROWS | ヒストグラム区間内 (上限は除く) にある重複する列値を持つ行の平均数 (DISTINCT_RANGE_ROWS > 0 の場合 RANGE_ROWS / DISTINCT_RANGE_ROWS) |
※他の結果セットとして取得したものについては詳しくはMSDNを参照してください。
https://msdn.microsoft.com/ja-jp/library/ms174384.aspx
ヒストグラムについて
SQLServerが統計情報として取得して認識している列情報のデータ分布がここからわかります。
例えばDISTINCT_RANGE_ROWSでは、頻度ヒストグラムとしての算出結果が取得されています。
試しに、DBCCコマンドで得られたDISTINCT_RANGE_ROWSを棒グラフでPythonでプロットすると次のような結果が得られます。
import pyodbc import pandas as pd import seaborn as sns import matplotlib.pyplot as plt pd.set_option('line_width', 100) conn = pyodbc.connect( r'DRIVER={ODBC Driver 13 for SQL Server};' r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;' r'DATABASE=sales;' r'UID=sa;' r'PWD=system' ) query = r'DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH HISTOGRAM' df = pd.read_sql( query ,conn ) # create graph(BAR) df.plot( x = ['RANGE_HI_KEY'], y = ['DISTINCT_RANGE_ROWS'], kind = 'bar', width = 1, alpha = 0.5, figsize = (10, 5) )
同様に本統計情報の列情報であるEMPLOYEE_IDについて実際の分布を同じビンでヒストグラムのグラフをプロットすると次のようになります。
import pyodbc import pandas as pd import seaborn as sns import matplotlib.pyplot as plt pd.set_option('line_width', 100) conn = pyodbc.connect( r'DRIVER={ODBC Driver 13 for SQL Server};' r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;' r'DATABASE=sales;' r'UID=sa;' r'PWD=system' ) query = r'SELECT EMPLOYEE_ID FROM EMPLOYEES' df1 = pd.read_sql( query ,conn ) # create graph(HISTOGRAM) df1.plot( y = ['EMPLOYEE_ID'], kind = 'hist', bins = [100,115,135,159,182,205,230,242,250,276,285,293,315,355,379,407,419,443,503,539,559,619,671,699,711,747,771,795,815,823,831,903,959,999,1071,1135,1151,1175,1199,1223,1295,1367,1375,1383,1407,1447,1487,1559,1575,1595,1619,1643,1667,1691,1715,1739,1763,1787,1811,1835,1911,1975,2039,2103,2167,2231,2295,2359,2423,2487,2551,2615,2679,2743,2807,2812,2813], alpha = 0.5, figsize = (10, 5) )
同じ結果が出力できていることがわかります。
尚、今回は統計情報のサンプリングが100％であるため一致しています。統計情報としてサンプリングしている割合が少ない場合、実際の分布とSQLServerの認識している分布が異なるため予測行数と実際の行数に乖離がでることになります。
サンプリングの件数や割合を確認するためには上記のDBCCコマンドか、もしくは上記DBCCコマンドをJOINしたコマンドで確認できます。
-- Rows Sampledがサンプリング数 DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH STAT_HEADER,NO_INFOMSGS -- SAMPLE_PCTがサンプリングの割合 DBCC SHOW_STATISTICS (EMPLOYEES, EMP_EMP_ID_PK) WITH STAT_HEADER JOIN DENSITY_VECTOR,NO_INFOMSGS
統計情報の更新はサンプリング数を明示的に指定して行うこともできますが、対象の件数の多い場合にサンプリングの割合を多くするとその分更新に時間がかかりますので注意が必要です。
UPDATE STATISTICS EMPLOYEES(EMP_EMP_ID_PK) WITH SAMPLE 20 PERCENT
予測行数について
試しに用意したテーブルにてヒストグラムを確認します。
この状態でcolumn3が'10005743'と'10005744'であるものを検索する場合の推定の実行プランを見てみます。
◆column3が'10005744'を検索
予測行数がヒストグラムのEQ_ROWSと一致していることがわかります。
◆column3が'10005743'を検索
'10005743'はRANGE_HI_KEYとは一致していません。この値は、今回の統計情報のヒストグラム区間内に含まれている状態ですので、そのヒストグラム区間に含まれる重複する列値を持つ行の平均数としてAVG_RANGE_ROWS(RANGE_ROWS / DISTINCT_RANGE_ROWS)が、実行プランの予測行数として表示されています。
予測行数と実際の行数に乖離があると適切な実行プランでクエリを実行することができませんので、パフォーマンスが大きく低下する恐れがありますので注意が必要です。
【SQL Server】【Python】pyodbcでSQL Serverのテーブルからデータを取得する
CSVなどをデータ分析や可視化する際に個人的によくPythonのPandasを利用するのですが、SQLServer2016内に格納してあるデータを直接参照し、集計やグラフ作成する必要がありましたのでやってみました。
※Python Anacondaを事前にインストール済みです。
事前にpyodbcをインストール
pip install pyodbc
SQLを実行
import pyodbc import pandas as pd conn = pyodbc.connect( r'DRIVER={ODBC Driver 13 for SQL Server};' r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;' r'DATABASE=sales;' r'UID=sa;' r'PWD=system' ) df = pd.read_sql( '''SELECT * FROM [dbo].[EMPLOYEES]''' ,conn ,index_col = 'HIRE_DATE' ,parse_dates = 'HIRE_DATE' ) print df.head(10)
ストアドプロシージャを実行
import pyodbc import pandas as pd conn = pyodbc.connect( r'DRIVER={ODBC Driver 13 for SQL Server};' r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;' r'DATABASE=sales;' r'UID=sa;' r'PWD=system' ) query = 'EXEC sp_test' df1 = pd.read_sql( query ,conn ,index_col = 'HIRE_DATE' ,parse_dates = 'HIRE_DATE' ) print df1.head(10)
ストアドプロシージャ（引数あり）を実行
import pyodbc import pandas as pd conn = pyodbc.connect( r'DRIVER={ODBC Driver 13 for SQL Server};' r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;' r'DATABASE=sales;' r'UID=sa;' r'PWD=system' ) query = 'EXEC sp_test2 @Param = 2' df2 = pd.read_sql( query ,conn ,index_col = 'HIRE_DATE' ,parse_dates = 'HIRE_DATE' ) print df2.head(10)
resampleを使用して歯抜けなく1年ごとの各値の平均をとる等、SQLだけでやるより簡単に行えて便利です。
import pyodbc import pandas as pd pd.set_option('line_width', 100) conn = pyodbc.connect( r'DRIVER={ODBC Driver 13 for SQL Server};' r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;' r'DATABASE=sales;' r'UID=sa;' r'PWD=system' ) query = 'EXEC sp_test2 @Param = 2' df2 = pd.read_sql( query ,conn ,index_col = 'HIRE_DATE' ,parse_dates = 'HIRE_DATE' ) df_mean = df2.resample('1A', loffset = '1A').mean().fillna(0) print df_mean.head(10)
集計から可視化まで
import pyodbc import pandas as pd import seaborn as sns pd.set_option('line_width', 100) conn = pyodbc.connect( r'DRIVER={ODBC Driver 13 for SQL Server};' r'SERVER=YUUSUKE-VAIO\INS_NISHI2016;' r'DATABASE=sales;' r'UID=sa;' r'PWD=system' ) query = 'EXEC sp_test2 @Param = 2' df2 = pd.read_sql( query ,conn ,index_col = 'HIRE_DATE' ,parse_dates = 'HIRE_DATE' ) df_mean = df2.resample('1A', loffset = '1A').mean().fillna(0) # create graph(SALARY) df_mean.plot.bar( x= [df_mean.index], y= [r'SALARY'], alpha=0.5, figsize=(10,5))
