Tip: Review the resource/workload class and allocation percentage and SQL text. Are they suitable for the query? If not, consider increasing them. Are there any errors from the follwoing query:
select * From sys.dm_pdw_errors
order by create_time desc;
Full SQL text is truncated: Check the command2 column on sys.dm_pdw_exec_requests as it holds nvarchar(max) type.
Verify the distributed query plan: If the query has multiple movements, this is an indication of a nonoptimal table distribution.
We can first verify the distribution either via sys.dm_pdw_request_steps or checking the estimated execution plan in SSMS.
Look for movements in the DSQL as BroadcastMoveOperation, ShuffleMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation:
Warning, this should not be done in production as this can take a lot of resources and cause sporadic behavior when executing in SSMS. Also consider executing in sqlcmd. Please also note this is the actual execution plan. All deletes, inserts, etc., will be processed.
Check statistiscs on system: Have you just updated data, are your queries taking longer to execute?
At times there may be additional data an organization is interested in capturing, or to review historical trends, Log Analytics and this custom API call will empower you to do so.
Query not executing: If the query is not executing or even parsing, query syntax details need to be examined.
Click to view techniques to confirm why the query is not executing.
When queries have been migrated from other machines, we need to examine the overall query behavior especially when they are migrated from legacy data warehouses.
↓
Force optimizer plans with query hints: Has all else failed, do you need to take matters into your own hands?
Click to view options on forcing the optimizer to invoke a specific plan:
