如何度量查询在Azure SQL数据仓库上所需的时间

Question

在传统的Server中，我可以通过设置以下方法获得CPU和运行时间

set statistics time on

当我在Azure SQL数据仓库上尝试时，我会得到以下错误

Msg 103010, Level 16, State 1, Line 19
Parse error at line: 1, column: 5: Incorrect syntax near 'statistics'.

从SQL数据仓库获得类似的诊断信息，我有哪些选择？

Answer 1

使用中支持的OPTION ( LABEL ... )语法向查询添加一个标签。然后，您可以通过门户或使用DMV sys.dm_pdw_exec_requests监视它，例如

SELECT *
FROM dbo.yourBillionRowTable
OPTION ( LABEL = 'Your Unique Query Label 042' )


-- In a separate window...
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [label] =  'Your Unique Query Label 042'

结果：

或者在门户中监视它：

Answer 2

不支持set statistics time on (支持set statistics IO on !)

您应该使用用于度量Azure数据库查询性能的查询存储。

注：不支持查询存储 (谢谢@wBob !)

唯一可靠的方法是使用我从门户获得的以下查询

select top 50
            (case when requests.status = 'Completed' then 100
            when progress.total_steps = 0 then 0
            else 100 * progress.completed_steps / progress.total_steps end) as progress_percent,
            requests.status, 
            requests.request_id, 
            sessions.login_name, 
            requests.start_time, 
            requests.end_time, 
            requests.total_elapsed_time, 
            requests.command,             
            errors.details,
            requests.session_id,
            (case when requests.resource_class is NULL then 'N/A'
            else requests.resource_class end) as resource_class,
            (case when resource_waits.concurrency_slots_used is NULL then 'N/A'
            else cast(resource_waits.concurrency_slots_used as varchar(10)) end) as concurrency_slots_used

            from sys.dm_pdw_exec_requests AS requests

            join sys.dm_pdw_exec_sessions AS sessions
                    on (requests.session_id = sessions.session_id)
            left join sys.dm_pdw_errors AS errors
                on (requests.error_id = errors.error_id)
            left join sys.dm_pdw_resource_waits AS resource_waits
                on (requests.resource_class = resource_waits.resource_class)
            outer apply (
                select count (steps.request_id) as total_steps,
                    sum (case when steps.status = 'Complete' then 1 else 0 end ) as completed_steps
                from sys.dm_pdw_request_steps steps where steps.request_id = requests.request_id
            ) progress

            cross apply (
                    select count (*) as is_batch
                    from sys.dm_pdw_exec_requests inner_requests
                    where inner_requests.session_id = requests.session_id
                            and inner_requests.request_id != requests.request_id
                            and inner_requests.start_time >= requests.start_time
                            and (inner_requests.end_time <= requests.end_time
                                    or (inner_requests.end_time is null and requests.end_time is null)
                            )
            ) batch

            where requests.start_time >= DATEADD(hour, -24, GETDATE())
                    and batch.is_batch = 0

            ORDER BY requests.total_elapsed_time DESC, requests.start_time DESC