読者です 読者をやめる 読者になる 読者になる

小物SEのメモ帳

IT関係の試したことや調べたことの備忘録

【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)

f:id:utiowa:20170319040949p:plain
f:id:utiowa:20170319041329p:plain

また、実行プランなしでCPU時間や実行時間等が知りたい場合、上記クエリのほかにレポートに出力も可能ですので、下記記事をご確認ください。
memorandom-nishi.hatenablog.jp

利用状況モニターから取得する

実際に現在実行されているものや比較的負荷が高いものが対象にあります。DMVで取得するものを利用状況モニターから取得しているだけですので、こちらも過去記事をご確認いただければと思います。
f:id:utiowa:20170319035855p:plain
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

f:id:utiowa:20170319035918p:plain
f:id:utiowa:20170319035937p:plain

また、クエリ実行時にSSMSの「推定実行プランの表示」をクリックしても同様の結果が取得できます。
f:id:utiowa:20170319035932p:plain

グラフィカルな実行プランの場合、各アイコンやノードにマウスカーソルを合わせると詳細情報として、CPU・IOのコストや予測行数などを確認できます。
SSMSがインストールされている環境であれば視覚的に確認できてよいかと思います。
f:id:utiowa:20170319040005p:plain
f:id:utiowa:20170319040013p:plain


テキスト形式で実行プランを取得したい場合、実行プランのみ取得するものと、詳細情報も併せて取得するものの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

f:id:utiowa:20170319040120p:plain

実行プランと詳細情報の場合は次のように実行します。

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

f:id:utiowa:20170319040150p:plain

情報としては上記グラフィカルな実行プランではマウスカーソルにて確認できる情報がまとめて確認できるような形となります。テキスト形式で保存すれば、環境に依存せず他人と情報を共有できて便利かと思います。

実際の実行プランを取得する

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

f:id:utiowa:20170319040224p:plain
f:id:utiowa:20170319040229p:plain

推定の実行プランと比較すると、各アイコンやノードにマウスカーソルを合わせた際に実際の行数や実行回数などが追加で取得できていることが確認できます。

f:id:utiowa:20170319040244p:plain
f:id:utiowa:20170319040248p:plain


テキスト形式で実際の実行プランで確認したい場合は次のように確認します。

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

f:id:utiowa:20170319040314p:plain
こちらも推定の実行プランに比べ、実際の行数や実行回数が追加されていることがわかるかと思います。


また、実行プランの取得ではありませんが、実行して分析する際には次のようにクエリがどのページを読み取ったかや構文解析時間、クエリ実行時間も合わせて取得しておくと解析時に便利かと思います。

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

f:id:utiowa:20170321010011p:plain
f:id:utiowa:20170321010017p:plain


上記のほかにトレースして実行計画やパフォーマンス情報を確認する方法はありますが、それは次回記載します。

【SQL Server】CPU使用率、IO負荷の高いクエリレポート

クエリのパフォーマンス状況の確認方法として、DMVを組み合わせた「CPU使用率の高いクエリ」や「IO負荷の高いクエリ」を取得するクエリを事前に準備しているシステム管理者も多いかと思いますが、グラフィカルなレポートという形で取得するのも視覚的に確認できてよいかと思いますので、出力方法の説明です。
※デメリットとして、レポート出力ではSQLハンドルは出力できているものの、実行プランは別途取得する必要があります。

特に次のレポートは比較的利用価値が高いのではないでしょうか。
「パフォーマンス - バッチ実行の統計」
「パフォーマンス - 平均IO数ごとの上位のクエリ」
「パフォーマンス - IOの総数ごとの上位のクエリ」
「パフォーマンス - CPUの平均時間ごとの上位のクエリ」
「パフォーマンス - CPUの総時間ごとの上位のクエリ」

尚、これらのレポートは出力時に裏でSQLを実行し、動的管理ビューからデータ取得をしていますので、キャッシュ上に残存しているデータのみがレポート対象となります。

レポート出力の手順

左ペインの「オブジェクト エクスプローラー」のインスタンス名を選択し右クリック、「レポート(P)」-「標準レポート」を押下して、表示したいグラフをクリックします。
f:id:utiowa:20170314002607p:plain

レポート内容

①「パフォーマンス - バッチ実行の統計」
f:id:utiowa:20170314002619p:plain

これはバッチごとの平均・総CPU、論理読み取り数、論理書き込み数についてのレポートになります。

f:id:utiowa:20170314002630p:plain
平均はすべてデフォルト表示で、それぞれのプラス表示をクリックするとトータルの取得値が確認できます。

キャッシュ上に残存しているデータのうち、バッチ単位で高負荷なものを確認することができます。
またバッチ番号のプラス表示をクリックするとステートメント毎の情報が確認できます。

実際に裏でレポート表示に使われているクエリは次のようなものになります。

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数ごとの上位のクエリ」
f:id:utiowa:20170314002710p:plain

これはキャッシュ上に残存するクエリのうち、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の総数ごとの上位のクエリ」
f:id:utiowa:20170314002954p:plain

これはキャッシュ上に残存するクエリのうち、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の平均時間ごとの上位のクエリ」
f:id:utiowa:20170314002744p:plain

これはキャッシュ上に残存するクエリのうち、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の総時間ごとの上位のクエリ」
f:id:utiowa:20170314002800p:plain

これはキャッシュ上に残存するクエリのうち、総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

f:id:utiowa:20170309013017p:plain

半角英数字カナを含む(半角記号除く)レコードを抽出

SELECT * FROM TEXT_TEST
	WHERE [text] LIKE '%[a-zA-Z0-9ア-ン ]%'
	COLLATE Japanese_BIN2

f:id:utiowa:20170309013026p:plain

数字を含むレコードを抽出

SELECT * FROM TEXT_TEST
	WHERE [text] LIKE '%[0-9]%'
	COLLATE Japanese_BIN2

f:id:utiowa:20170309013032p:plain


尚、照合順序については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]

f:id:utiowa:20170226171639p:plain

上記から、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

f:id:utiowa:20170226171736p:plain

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]

f:id:utiowa:20170226172058p:plain

尚、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のメモ帳
と同じものを確かめてみると、ラッチを確保していたルート、中間ノード、リーフ(データ)のページと一致することがわかります。
f:id:utiowa:20170226172408p:plain
f:id:utiowa:20170226173432p:plain
※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
拡張イベントによるトレース

拡張イベントを作成できたら、セッションを開始します。
f:id:utiowa:20170219170926p:plain

セッション開始後、状況をリアルタイムで確認するために、「ライブデータの監視(L)」を選択します。
f:id:utiowa:20170219170939p:plain

対象のセッションで今回試したいクエリを実行してみます。
f:id:utiowa:20170219170951p:plain

先ほど「ライブデータの監視(L)」で開いたウィンドウに結果が表示されているかと思います。
f:id:utiowa:20170219171009p:plain

クエリ実行後は拡張イベントによる監視を停止します。
f:id:utiowa:20170219171000p:plain

トレース結果の確認

拡張イベントによるトレース結果から処理の流れを結果を見てみます。
①OBJECTに対するインテント共有ロック獲得
②ページ:75715に対する共有ページラッチ獲得
③ページ:5615に対する共有ページラッチ獲得
④ページ:75715に対する共有ページラッチ解放
⑤ページ:5009に対する共有ページラッチ獲得
⑥ページ:5615に対する共有ページラッチ解放
⑦ページ:5171に対する共有ページラッチ獲得
⑧ページ:5009に対する共有ページラッチ解放
⑨ページ:5171に対するインテント共有ロック獲得
⑩ページ:5171に対するインテント共有ロック解放
⑪ページ:5171に対する共有ページラッチ解放
⑫OBJECTに対するインテント共有ロック解放

実行プランは下記のようなシンプルなものです。
f:id:utiowa:20170219171139p:plain

今回のクエリはClustered Index Seekのみであり、このクラスターインデックスの奥行は4であるためページラッチの動きは以下のようなイメージになるかと思います。
f:id:utiowa:20170219171306p:plain
f:id:utiowa:20170219171311p:plain
f:id:utiowa:20170219171317p:plain
f:id:utiowa:20170219171324p:plain

まずはロックを獲得し、その次にインデックスのルートページからラッチを獲得。次のノードのページからラッチを獲得できたら、前のラッチを解放するということを繰り返して、目的のページまで到達するようなイメージでしょうか。

更新処理や複雑なクエリだとロックやラッチはもっと複雑になるかと思いますが、簡単な処理で処理構造を理解しておくことは重要かと思います。

【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

f:id:utiowa:20170218015250p:plain

ヒストグラムとして取得できる情報は以下となります。

カラム名 説明
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)
        )

f:id:utiowa:20170218015305p:plain
f:id:utiowa:20170218015310p:plain

同様に本統計情報の列情報である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)
        )

f:id:utiowa:20170218015326p:plain
f:id:utiowa:20170218015332p:plain

同じ結果が出力できていることがわかります。
尚、今回は統計情報のサンプリングが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
予測行数について

試しに用意したテーブルにてヒストグラムを確認します。
f:id:utiowa:20170218015845p:plain

この状態でcolumn3が'10005743'と'10005744'であるものを検索する場合の推定の実行プランを見てみます。

◆column3が'10005744'を検索
f:id:utiowa:20170218020051p:plain

予測行数がヒストグラムのEQ_ROWSと一致していることがわかります。

f:id:utiowa:20170218020254p:plain

◆column3が'10005743'を検索
f:id:utiowa:20170218020349p:plain

'10005743'はRANGE_HI_KEYとは一致していません。この値は、今回の統計情報のヒストグラム区間内に含まれている状態ですので、そのヒストグラム区間に含まれる重複する列値を持つ行の平均数としてAVG_RANGE_ROWS(RANGE_ROWS / DISTINCT_RANGE_ROWS)が、実行プランの予測行数として表示されています。
f:id:utiowa:20170218021009p:plain


予測行数と実際の行数に乖離があると適切な実行プランでクエリを実行することができませんので、パフォーマンスが大きく低下する恐れがありますので注意が必要です。

【SQL Server】【Python】pyodbcでSQL Serverのテーブルからデータを取得する

CSVなどをデータ分析や可視化する際に個人的によくPythonのPandasを利用するのですが、SQLServer2016内に格納してあるデータを直接参照し、集計やグラフ作成する必要がありましたのでやってみました。
Python Anacondaを事前にインストール済みです。

事前にpyodbcをインストール
pip install pyodbc

f:id:utiowa:20170212222804p:plain

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)

f:id:utiowa:20170212223058p:plain

ストアドプロシージャを実行
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)

f:id:utiowa:20170212223158p:plain

ストアドプロシージャ(引数あり)を実行
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)

f:id:utiowa:20170212223401p:plain


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)

f:id:utiowa:20170212223920p:plain

集計から可視化まで
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)) 

f:id:utiowa:20170212225116p:plain