Come capire se si è verificato un pushdown esterno

Questo articolo descrive come determinare se le query PolyBase traggono vantaggio dal pushdown alla fonte di dati esterna. Per altre informazioni sul pushdown esterno, vedere calcoli pushdown in PolyBase.

La mia query beneficia del pushdown esterno?

L'elaborazione a discesa migliora le prestazioni delle query su origini dati esterne. Alcune attività di calcolo vengono delegate all'origine dati esterna anziché essere portate in SQL Server. In particolare nei casi di filtraggio e pushdown dei join, il carico di lavoro sull'istanza SQL Server può essere notevolmente ridotto.

Il calcolo pushdown di PolyBase può migliorare significativamente le prestazioni della query. Se una query PolyBase viene eseguita lentamente, è necessario determinare se si sta eseguendo il pushdown della query PolyBase.

Esistono tre diversi scenari in cui è possibile osservare il pushdown nel piano di esecuzione:

• Abbassamento del predicato di filtro
• Join pushdown
• Abbassamento dell'aggregazione

Nota

Esistono limitazioni su ciò che è possibile delegare a origini dati esterne con calcoli PolyBase di pushdown:

• Alcune funzioni T-SQL possono impedire il pushdown. Per ulteriori informazioni, vedere funzionalità e limitazioni di PolyBase.
• Per un elenco delle funzioni T-SQL che altrimenti possono essere spostate verso il basso, vedere calcoli pushdown in PolyBase.

Sono state introdotte due nuove funzionalità di SQL Server 2019 (15.x) per consentire agli amministratori di determinare se viene eseguito il push di una query PolyBase nell'origine dati esterna:

• Visualizza il piano di esecuzione stimato con il flag di traccia 6408
• Visualizzare il read_command nella visualizzazione a gestione dinamica sys.dm_exec_external_work

Questo articolo fornisce informazioni dettagliate su come usare ognuno di questi due casi d'uso, per ognuno di tre scenari di pushdown.

Usare TF6408

Per impostazione predefinita, il piano di esecuzione stimato non espone il piano di query remoto e viene visualizzato solo l'oggetto operatore di query remota. Ad esempio, un piano di esecuzione stimato di SQL Server Management Studio (SSMS):

In alternativa, in Azure Data Studio:

A partire da SQL Server 2019 (15.x), è possibile abilitare un nuovo flag di traccia 6408 a livello globale usando DBCC TRACEON. Per esempio:

DBCC TRACEON (6408, -1);  

Questo flag di traccia funziona esclusivamente con i piani di esecuzione stimati e non ha alcun effetto sui piani di esecuzione reali. Questo flag di traccia espone informazioni sull'operatore Remote Query che indica cosa avviene durante la fase di Remote Query.

I piani di esecuzione vengono letti da destra a sinistra, come indicato dalla direzione delle frecce. Se un operatore è a destra di un altro operatore, si dice che sia "dopo" di esso. Se un operatore si trova a sinistra di un altro operatore, si dice che lo "preceda".

• In SSMS evidenziare la query e selezionare Visualizza piano di esecuzione stimato sulla barra degli strumenti oppure usare CTRL+L.
• In Azure Data Studio evidenziare la query e selezionare Spiegare. Quindi, considera i seguenti scenari per determinare se si è verificato il pushdown.

Ognuno degli esempi seguenti include l'output di SSMS e Azure Data Studio.

Pushdown del predicato di filtro (visualizzazione con piano di esecuzione)

Si consideri la query seguente, che usa un predicato di filtro nella clausola WHERE:

SELECT *
FROM [Person].[BusinessEntity] AS be
WHERE be.BusinessEntityID = 17907;

Se si verifica il pushdown del predicato di filtro, l'operatore di filtro è prima dell'operatore esterno. Quando l'operatore di filtro è prima dell'operatore esterno, il filtraggio si è verificato prima di essere selezionato nuovamente dall'origine dati esterna, il che indica che è stato eseguito il push del predicato del filtro.

Con pushdown del predicato di filtro (visualizzazione con piano di esecuzione)

Con il flag di traccia 6408 abilitato, vengono ora visualizzate informazioni aggiuntive nell'output del piano di esecuzione stimato. L'output varia tra SSMS e Azure Data Studio.

In SSMS il piano di query remoto viene visualizzato nel piano di esecuzione stimato come Query 2 (sp_execute_memo_node_1) e corrisponde all'operatore Remote Query in Query 1. Per esempio:

In Azure Data Studio l'esecuzione di query remote viene invece rappresentata come un singolo piano di query. Per esempio:

Senza eseguire il pushdown del predicato di filtro (visualizzazione con piano di esecuzione)

Se il pushdown del predicato di filtro non si verifica, il filtro sarà posizionato dopo l'operatore esterno.

Piano di esecuzione stimato da SSMS:

Piano di esecuzione stimato di Azure Data Studio:

Propagazione di JOIN

Si consideri la query seguente che usa l'operatore JOIN per due tabelle esterne nella stessa origine dati esterna:

SELECT be.BusinessEntityID, bea.AddressID
FROM [Person].[BusinessEntity] AS be
INNER JOIN [Person].[BusinessEntityAddress] AS bea
ON be.BusinessEntityID = bea.BusinessEntityID;

Se il JOIN viene trasferito all'origine dati esterna, l'operatore Join sarà posizionato prima dell'operatore esterno. In questo esempio, sia [BusinessEntity] che [BusinessEntityAddress] sono tabelle esterne.

Con l'ottimizzazione con spostamento in basso del join (visualizzazione con piano di esecuzione)

Piano di esecuzione stimato da SSMS:

Piano di esecuzione stimato di Azure Data Studio:

Senza eseguire il pushdown del join (visualizzazione con piano di esecuzione)

Se il JOIN non viene spostato nell'origine dati esterna, l'operatore JOIN si troverà dopo l'operatore esterno. In SSMS l'operatore esterno si trova nel piano di query per sp_execute_memo_node, che si trova nell'operatore Remote Query in Query 1. In Azure Data Studio l'operatore Join si trova dopo gli operatori esterni.

Piano di esecuzione stimato da SSMS:

Piano di esecuzione stimato di Azure Data Studio:

Pushdown dell'aggregazione (visualizzazione con piano di esecuzione)

Si consideri la query seguente, che usa una funzione di aggregazione:

SELECT SUM([Quantity]) as Quant
FROM [AdventureWorks2022].[Production].[ProductInventory];

Con pushdown dell'aggregazione (visualizzazione con piano di esecuzione)

Se si verifica il pushdown dell'aggregazione, l'operatore di aggregazione è prima dell'operatore esterno. Quando l'operatore di aggregazione è prima dell'operatore esterno, l'aggregazione si è verificata prima di tornare selezionata dall'origine dati esterna, a indicare che l'aggregazione è stata spostata verso il basso.

Piano di esecuzione stimato da SSMS:

Piano di esecuzione stimato di Azure Data Studio:

Senza implementazione diretta dell'aggregazione (visualizzazione con piano di esecuzione)

Se il pushdown dell'aggregazione non si verifica, l'operatore di aggregazione seguirà l'operatore esterno.

Piano di esecuzione stimato da SSMS:

Piano di esecuzione stimato di Azure Data Studio:

Utilizzare DMV

In SQL Server 2019 (15.x) e versioni successive, la colonna read_command di sys.dm_exec_external_work DMV mostra la query inviata all'origine dati esterna. In questo modo è possibile determinare se si sta verificando il pushdown, ma non espone il piano di esecuzione. La visualizzazione della query remota non richiede TF6408.

Nota

Per Hadoop e l'archiviazione di Azure, il read_command restituisce sempre NULL.

È possibile eseguire la query seguente e usare il start_time/end_time e read_command per identificare la query da analizzare:

SELECT execution_id, start_time, end_time, read_command
FROM sys.dm_exec_external_work
ORDER BY execution_id desc;

Nota

Una limitazione del metodo sys.dm_exec_external_work è che il campo read_command nella DMV è limitato a 4000 caratteri. Se la query è sufficientemente lunga, il read_command potrebbe essere troncato prima di visualizzare la funzione WHERE/JOIN/aggregation nel read_command.

Pushdown del predicato di filtro (visualizzazione con DMV)

Si consideri la query usata nell'esempio di predicato di filtro precedente:

SELECT *
FROM [Person].[BusinessEntity] be
WHERE be.BusinessEntityID = 17907;

Con pushdown del filtro (visualizzazione con DMV)

È possibile stabilire se il pushdown del predicato del filtro si sta verificando controllando il read_command nella DMV. Verrà visualizzato un esempio simile al seguente:

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[rowguid] AS [rowguid], 
  [T1_1].[ModifiedDate] AS [ModifiedDate] FROM 
  (SELECT [T2_1].[BusinessEntityID] AS [BusinessEntityID], [T2_1].[rowguid] AS [rowguid], 
    [T2_1].[ModifiedDate] AS [ModifiedDate] 
FROM [AdventureWorks2022].[Person].[BusinessEntity] AS T2_1 
WHERE ([T2_1].[BusinessEntityID] = CAST ((17907) AS INT))) AS T1_1;

La clausola WHERE si trova nel comando inviato all'origine dati esterna, il che significa che il predicato di filtro viene valutato nell'origine dati esterna. Il filtro sul set di dati si è verificato nell'origine dati esterna e solo il set di dati filtrato è stato recuperato da PolyBase.

Senza pushdown del filtro (visualizzazione con DMV)

Se il pushdown non si verifica, vedrai qualcosa di simile a questo:

SELECT "BusinessEntityID","rowguid","ModifiedDate" FROM "AdventureWorks2022"."Person"."BusinessEntity"

Non esiste alcuna clausola WHERE nel comando inviato all'origine dati esterna, pertanto il predicato del filtro non viene inserito. Il filtro sull'intero set di dati si è verificato sul lato SQL Server, dopo il recupero del set di dati da PolyBase.

Pushdown di JOIN (visualizzazione con DMV)

Si consideri la query usata nell'esempio JOIN precedente:

SELECT be.BusinessEntityID, bea.AddressID
FROM [Person].[BusinessEntity] be
INNER JOIN [Person].[BusinessEntityAddress] bea ON be.BusinessEntityID = bea.BusinessEntityID;

Con il pushdown del join (visualizzazione DMV)

Se viene eseguito il push di JOIN nell'origine dati esterna, verrà visualizzato un risultato simile al seguente:

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[AddressID] AS [AddressID] 
FROM (SELECT [T2_2].[BusinessEntityID] AS [BusinessEntityID], [T2_1].[AddressID] AS [AddressID] 
FROM [AdventureWorks2022].[Person].[BusinessEntityAddress] AS T2_1 
INNER JOIN  [AdventureWorks2022].[Person].[BusinessEntity] AS T2_2  
ON ([T2_1].[BusinessEntityID] = [T2_2].[BusinessEntityID])) AS T1_1;

La clausola JOIN è nel comando inviato all'origine dati esterna, quindi viene eseguito il push di JOIN. Il join sul set di dati si è verificato nell'origine dati esterna e solo il set di dati corrispondente alla condizione di join è stato recuperato da PolyBase.

Senza push-down di join (visualizzazione con DMV)

Se il pushdown del join non si verifica, si noterà che sono presenti due query diverse eseguite sull'origine dati esterna:

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID], [T1_1].[AddressID] AS [AddressID] 
FROM [AdventureWorks2022].[Person].[BusinessEntityAddress] AS T1_1;

SELECT [T1_1].[BusinessEntityID] AS [BusinessEntityID] FROM [AdventureWorks2022].[Person].[BusinessEntity] AS T1_1;

Il join dei due set di dati si è verificato sul lato SQL Server, dopo che entrambi i set di dati vengono recuperati da PolyBase.

Ottimizzazione dell'aggregazione (visualizzazione con viste di gestione dinamica)

Si consideri la query seguente, che usa una funzione di aggregazione:

SELECT SUM([Quantity]) as Quant
FROM [AdventureWorks2022].[Production].[ProductInventory];

Con l'ottimizzazione dell'aggregazione (visualizzazione tramite DMV)

Se si verifica il pushdown dell'aggregazione, nella read_commandviene visualizzata la funzione di aggregazione . Per esempio:

SELECT [T1_1].[col] AS [col] FROM (SELECT SUM([T2_1].[Quantity]) AS [col] 
FROM [AdventureWorks2022].[Production].[ProductInventory] AS T2_1) AS T1_1

La funzione di aggregazione è nel comando inviato all'origine dati esterna, quindi viene eseguito il push dell'aggregazione. L'aggregazione si è verificata nell'origine dati esterna e solo il set di dati aggregato è stato recuperato da PolyBase.

Senza eseguire il pushdown dell'aggregazione (visualizzazione con DMV)

Se il pushdown dell'aggregazione non si verifica, non verrà visualizzata la funzione di aggregazione nel read_command. Per esempio:

SELECT "Quantity" FROM "AdventureWorks2022"."Production"."ProductInventory"

L'aggregazione è stata eseguita in SQL Server, dopo che il set di dati non raggruppato è stato recuperato da PolyBase.

Passaggi successivi

• risoluzione dei problemi di PolyBase
• Errori di PolyBase e possibili soluzioni