4

In traditional SQL Server I can get CPU and Elapsed Time by setting the following

set statistics time on

When I try that on Azure SQL Data Warehouse I get the following error

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

What options do I have for getting similar diagnostic information from SQL Data Warehouse

  • 1
    statistics time is not supported. You can use query store. – Kin Shah Aug 24 '16 at 16:28
  • @Kin: That looks to me like an answer to the question. – Andriy M Aug 24 '16 at 16:36
1

Add a label to your query using the OPTION ( LABEL ... ) syntax which is supported in Azure SQL Data Warehouse. You can then monitor it either via the portal or using the DMV sys.dm_pdw_exec_requests, for example

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'

Results: DMV

Or monitor it in the portal:

Portal

0

set statistics time on is not supported (set statistics IO on is supported !)

enter image description here

You should use Query Store to measure query performance for Azure databases.

Note: Query store is not supported (Thanks @wBob !)

The only reliable way is to use below query that I got from the portal

enter image description here

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
  • I followed the link and it says to access the Query Store in the Azure portal. It Says "From left-hand side menu, under support and troubleshooting, select Query Performance Insight”. I dont see that as an option when I look at my Data Warehouse. It is there for my regular Azure SQL DBs – leemicw Aug 24 '16 at 18:38
  • Sadly Query Store is not supported in Azure SQL Data Warehouse at this time. It is supported in Azure SQL Database, as per here. – wBob Aug 24 '16 at 18:56
  • 1
    @wBob I have updated my answer. I figured that out... sad that its supported in Azure DB and not in warehouse :-) – Kin Shah Aug 24 '16 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.