Finding Progress of Longest Internal DMS Step

Correct Answer: B.

There are specific details already identified as part of the process.

They are the execution ID of longest running polybase query, longest running step and location_type.

The task is to identify the execution process.

First, we need to identify the location_type, since the question has already been given as DMS.

We have to find the Statement from the option which includes that.

In Option B, it is precisely selecting from sys.dm_exec_dms_workers.

Option A is incorrect: It is for finding the progress of the SQL step.

This should be run when it is Head or Compute.

Option B is correct: It will give the expected results.

Option C is incorrect: This is for finding the information about external DMS steps.

But the question precisely says it's internal.

Option D is incorrect: This can be used to find the longest running step of the distributed query plan.

To find the execution progress of the longest internal DMS step, we need to query the Dynamic Management Views (DMV) available in Microsoft SQL Server. Based on the given information, the execution ID and location_type of the longest running step have been identified. Now, we need to find the execution progress of this step.

Option A:

sqlCopy code
SELECT execution_id, step_index, total_elapsed_time, distribution_id, status, row_count, command  FROM sys.dm_exec_distributed_sql_requests  WHERE execution_id = 'QID7864' and step_index = 1; 

This query is looking at the sys.dm_exec_distributed_sql_requests DMV and filtering on the execution_id and step_index. The step_index value of 1 indicates that this is the first step in the query. This query may not be useful for finding progress of the longest internal DMS step.

Option B:

sqlCopy code
SELECT execution_id, step_index,type, bytes_processed, dms_step_index, status,total_elapsed_time FROM sys.dm_exec_dms_workers  WHERE execution_id = 'QID7864'  ORDER BY total_elapsed_time DESC; 

This query is looking at the sys.dm_exec_dms_workers DMV and filtering on the execution_id. The dms_step_index column indicates that this query is specifically looking for DMS steps. The total_elapsed_time column indicates the elapsed time for the step, which can help to determine progress. This query looks like a good option for finding the progress of the longest internal DMS step.

Option C:

sqlCopy code
SELECT execution_id, step_index, dms_step_index, compute_node_id, type, input_name, length, total_elapsed_time, status  FROM sys.dm_exec_external_work WHERE execution_id = 'QID7864' and step_index = 7  ORDER BY total_elapsed_time DESC; 

This query is looking at the sys.dm_exec_external_work DMV and filtering on the execution_id and step_index. The step_index value of 7 indicates that this is the seventh step in the query. This query may not be useful for finding progress of the longest internal DMS step.

Option D:

sqlCopy code
SELECT execution_id, step_index, operation_type, distribution_type, location_type, status, total_elapsed_time, command  FROM sys.dm_exec_distributed_request_steps  WHERE execution_id = 'QID7864'  ORDER BY total_elapsed_time DESC; 

This query is looking at the sys.dm_exec_distributed_request_steps DMV and filtering on the execution_id. The location_type column indicates the type of location involved in the step. This query may not be useful for finding progress of the longest internal DMS step.

In conclusion, option B seems to be the best solution for finding progress of the longest internal DMS step as it specifically looks for DMS steps and provides the elapsed time for the step.