Table of Contents

1. Best practices when Creating Tables

When creating table in Dedicated SQL Pool, choose the correct Distribution Column and Index for best query performance. Follow Best Practices in creating tables in Azure Synapse Analytics

Remember to create and Update Stats on your new tables Table statistics for dedicated SQL pool in Azure Synapse Analytics

2. Identify slow-running QID and analyze compilation

To best determine what is causing any given query’s slow performance, you will need to identify an example long-running query:

SELECT total_elapsed_time/60000 as minutes, s.login_name, 
    r.request_id, r.status, r.[label], r.resource_class, [submit_time], 
    command, r.request_id, start_time, end_time, 
    total_elapsed_time, result_cache_hit
FROM sys.dm_pdw_exec_requests r 
JOIN sys.dm_pdw_exec_sessions s 
on r.session_id = s.session_id
where r.session_id <> session_id()  
    AND r.status not in ('Completed','Failed','Cancelled') 
order by total_elapsed_time desc;

See Monitor query execution for additional context.

3. Analyze the distributed query plan

You may need to analyze all of the steps of the distributed plan. Run the following statement to retrieve these details:

select * from sys.dm_pdw_request_steps where request_id = 'QID####' order by step_index;

As you review each step of the distributed plan, evaluate following conditions and complete the recommended mitigation if applicable:

4. Analyze a specific query plan

Using the output of the query from the Analyze the distributed query plan section, identify the longest-running (by total_elapsed_time) step of the query plan. Once identified, note the location_type of the step and then choose the appropriate query below to update with request_id (QID) and step_index and then execute the query:

• If location_type in (‘Compute’, ‘Control’)

select * from sys.dm_pdw_sql_requests where request_id = 'QID####' and step_index = #;

• If location_type = ‘DMS’ (DMS = Data Movement Service)

select * from sys.dm_pdw_dms_workers where request_id = 'QID####' and step_index = #;

a. Analyze distributed query plan

The output from these queries show information about the execution of the step’s query on each distribution. Though there will be variations, the durations of each distribution should be generally similar. If, however, you observe a small number of distributions taking significantly more time than the other distributions, consider the following possible causes and mitigations:

b. Use EXPLAIN WITH RECOMMENDATION

You can also review the query plan before executing a query using Explain [With_Recommendation] command following: Explain (T-SQL) and How to read execution plan

5. Analyze impacts of concurrent workload

If you eliminated all previous criteria, it’s possible that your query is from a a server or client system resource bottleneck. Be sure to revisit the anti-patterns section above and ensure you are following our recommended best practices .

However, one final possibility to consider is that your overall concurrent workload is constraining shared resources such as IO, network IO, CPU, memory, etc based on your current resource configuration. Perceived poor performance can be the result of either an undersized dedicated SQL pool or client.