You are setting up a solution to monitor the longest running polybase queries and their progress using Dynamic Management Views (DMV). You already identified the execution ID and location_type of the longest running step. They are QID7864 and DMS (Data Movement Service) respectively. Now you have to find the execution progress of this. Which of the options is the best solution for finding progress of the longest internal DMS step?

Answer options:

A.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;
B.SELECT execution_id, step_index,type, bytes_processed, dms_step_index, status,total_elapsed_timeFROM sys.dm_exec_dms_workers WHERE execution_id = `QID7864` ORDER BY total_elapsed_time DESC;
C.SELECT execution_id, step_index, dms_step_index, compute_node_id, type, input_name, length, total_elapsed_time, status FROM sys.dm_exec_external_workWHERE execution_id = `QID7864` and step_index = 7 ORDER BY total_elapsed_time DESC;
D.SELECT execution_id, step_index, operation_type, distribution_type, location_type, status, otal_elapsed_time, command FROM sys.dm_exec_distributed_request_steps WHERE execution_id = `QID7864` ORDER BY total_elapsed_time DESC;

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.
Reference: To know more, please refer to the docs below: 

https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-troubleshooting?view=sql-server-ver15#to-monitor-polybase-queries-using-dmvs