PolyBase troubleshooting with dynamic management views

Applies To: SQL Server 2016 Preview

THIS TOPIC APPLIES TO:SQL Server (starting with 2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

With PolyBase, SQL Server 2016 introduces a set of new DMVs for trouble-shooting PolyBase queries. The DMVs with the prefix ‘_distributed’ capture all operations related to the distributed execution of PolyBase queries. The DMVs with the prefix ‘_external’ capture operations external to SQL Server or involve consuming data from the external data source.

Catalog views

Use the catalog views listed here to manage PolyBase operations.

Dynamic Management Views

Examples

-- PolyBase trouble-Shooting scenarios
-- Pick up the query that took longest time
select 
    execution_id,
       st.text,
       dr.total_elapsed_time
FROM sys.dm_exec_distributed_requests  dr
     cross apply sys.dm_exec_sql_text(sql_handle) st
order by total_elapsed_time desc

--  Get the execution steps for the query based on the DSQL Plan
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 =  'QIDXX' order by total_elapsed_time desc

-- Get the DMS steps for the DMS Move  
select execution_id, step_index, dms_step_index, status, type, bytes_processed, total_elapsed_time
from sys.dm_exec_dms_workers where execution_id = 'QIDXX' order by total_elapsed_time desc  

-- Get the information about the external DMS operations
select * from sys.dm_exec_external_work where execution_id = 'QIDXX' order by total_elapsed_time desc 

-- Get the information about MR jobs executed during the Hadoop push-down. It contains a row for each map-reduce 
-- job that is pushed down to Hadoop as part of running a PolyBase query against an external table
select * from sys.dm_exec_external_operations

-- Get information about the scale out cluster
select * from sys.dm_exec_compute_nodes

--shows IS_External which is the only way to tell that this is an external table.
SELECT name, type, IS_External FROM sys.tables WHERE name='bands' 

See Also

PolyBase T-SQL fundamentals
PolyBase troubleshooting with dynamic management views