如何衡量查詢在 Azure SQL 數據倉庫上花費的時間

在傳統的 SQL 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 數據倉庫獲取類似的診斷資訊

OPTION ( LABEL ... )使用Azure SQL 數據倉庫支持的語法向查詢添加標籤。然後，您可以通過門戶或使用 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'

結果：

或者在門戶中監控它：

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

引用自：https://dba.stackexchange.com/questions/147772