PolyBase troubleshooting with dynamic management views

 

Applies To: SQL Server 2016 Preview

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2016) noAzure SQL Database yesAzure SQL Data Warehouse yesParallel 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.

Use the catalog views listed here to manage PolyBase operations.

View

Description

sys.external_tables (Transact-SQL)

Identifies external tables.

sys.external_data_sources (Transact-SQL)

Identifies external data sources.

sys.external_file_formats (Transact-SQL)

Finds external file formats.

-- 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' 


Show: