重いクエリを調べる

#調査用クエリ

動的管理ビューを使用した SQL Azure の監視
「クエリ パフォーマンスの監視」を参照のこと。

調査用クエリ

SELECT TOP 5 query_stats.query_hash AS "Query Hash", 
    SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
    MIN(query_stats.statement_text) AS "Statement Text"
FROM 
    (SELECT QS.*, 
    SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE QS.statement_end_offset END 
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

#以下蛇足
検証で使った材料です。
まずはテーブル。

テーブル

create table test01(
    ymd int not null,
    h int not null,
    m int not null,
    s int not null,
    primary key (ymd,h,m,s)
)

以下のSQLを実行すると60レコードが挿入されます。
（調査用クエリはCPU Timeで調査を行っているため、実データは少量で重いSQLを実行したい）

データ生成

declare @d as datetime
set @d = sysdatetime()
set @d = '2015-1-1'

begin transaction
while @d < '2015-1-1 0:01:00' 
begin
    insert into test02 values(year(@d) * 10000 + month(@d) * 100 + day(@d),
        datepart(hour,@d),datepart(minute,@d),datepart(second,@d));
    set @d = dateadd(second,1,@d)
end
commit transaction

そしてCPU Timeが大きくなるようなSQLです。

計測対象のSQL

select 2,count(*) from test02 a
 inner join test02 b on a.ymd=b.ymd
 inner join test02 c on a.ymd=c.ymd
 inner join test02 d on a.ymd=d.ymd
 inner join test02 e on a.ymd=e.ymd

実行時間が33秒。
なお、実行環境はWindows Azure、SQL DatabaseのBASICを使用しています。

Avg CPU Timeが1708400と出ています。

Azureポータルから確認するとDATA IOが0%でCPUがいっぱい回ってることが分かります。