EXPLAIN (Transact-SQL)
Aplica-se a: 
Azure Synapse Analytics (apenas pool de SQL dedicado)
Retorna o plano de consulta de uma instrução SQL do Azure Synapse Analytics sem executar a instrução. Use EXPLAIN para visualizar quais operações exigirão a movimentação de dados e para exibir os custos estimados das operações de consulta. WITH RECOMMENDATIONS aplica-se ao Azure Synapse Analytics.
Sintaxe
EXPLAIN [WITH_RECOMMENDATIONS] SQL_statement
[;]
Observação
Não há suporte a essa sintaxe para o pool de SQL sem servidor no Azure Synapse Analytics.
Argumentos
SQL_statement
A instrução SQL na qual EXPLAIN será executado. SQL_statement pode ser qualquer um destes comandos: SELECT, INSERT, UPDATE, DELETE, CREATE TABLE AS SELECT, CREATE REMOTE TABLE.
WITH_RECOMMENDATIONS
Retornar ao plano de consulta com recomendações para otimizar o desempenho da instrução SQL.
Permissões
Requer a permissão SHOWPLAN e a permissão para executar SQL_statement. Confira Permissões: GRANT, DENY, REVOKE (Azure Synapse Analytics, Parallel Data Warehouse).
Valor retornado
O valor retornado do comando EXPLAIN é um documento XML. Este documento XML lista todas as operações no plano de consulta para determinada consulta, cada uma delimitada pela marcação <dsql_operation>. O valor retornado é do tipo nvarchar(max) .
O plano de consulta retornado descreve instruções SQL sequenciais; Quando a consulta é executada, ela pode envolver operações paralelizadas, portanto, algumas das instruções sequenciais mostradas podem ser executadas ao mesmo tempo.
A estrutura do XML de valor retornado tem esta aparência:
\<?xml version="1.0" encoding="utf-8"?>
<dsql_query>
<sql>. . .</sql>
<params />
<dsql_operations>
<dsql_operation>
. . .
</dsql_operation>
[ . . . n ]
<dsql_operations>
</dsql_query>
As marcas XML contêm estas informações:
| Marcação XML | Resumo, atributos e conteúdo |
|---|---|
| <dsql_query> | Elemento de nível superior/documento. |
| <sql> | Duplica a SQL_statement. |
| <params> | Essa marcação não é usada no momento. |
| <materialized_view_candidates> (versão prévia) | Contém a instrução CREATE da exibição materializada recomendada para aprimorar o desempenho da instrução SQL. |
| <dsql_operations> | Resume e contém as etapas de consulta e inclui as informações de custo da consulta. Também contém todos os blocos de <dsql_operation>. Essa marcação contém as informações de contagem da consulta inteira:<dsql_operations total_cost=total_cost total_number_operations=total_number_operations>total_cost é o tempo total estimado para que a consulta seja executada, em ms. total_number_operations é o número total de operações para a consulta. Uma operação que será colocada em paralelo e executada em vários nós é considerada uma única operação. |
| <dsql_operation> | Descreve uma única operação no plano de consulta. A marca <dsql_operation> contém o tipo de operação como um atributo:<dsql_operation operation_type=operation_type>operation_type é um dos valores encontrados em sys.dm_pdw_request_steps (Transact-SQL). O conteúdo no bloco \<dsql_operation> depende do tipo de operação.Confira a tabela a seguir. |
| Tipo de operação | Conteúdo | Exemplo |
|---|---|---|
| BROADCAST_MOVE, DISTRIBUTE_REPLICATED_TABLE_MOVE, MASTER_TABLE_MOVE, PARTITION_MOVE, SHUFFLE_MOVE e TRIM_MOVE | Elemento <operation_cost> com esses atributos. Os valores refletem somente a operação local:- cost é o custo do operador local e mostra o tempo estimado para que a operação seja executada, em ms. - accumulative_cost é a soma de todas as operações vistas no plano, incluindo a soma dos valores de operações paralelas, em ms. - average_rowsize é o tamanho médio da linha estimado (em bytes) das linhas recuperadas e passadas durante a operação. - output_rows é a cardinalidade da saída (nó) e mostra o número de linhas de saída. <location>: os nós ou as distribuições em que a operação ocorrerá. As opções são: "Control", "ComputeNode", "AllComputeNodes", "AllDistributions", "SubsetDistributions", "Distribution" e "SubsetNodes".<source_statement>: os dados de origem para a movimentação em ordem aleatória.<destination_table>: a tabela temporária interna para a qual os dados serão movidos.<shuffle_columns>: (Aplicável somente para operações SHUFFLE_MOVE). Uma ou mais colunas que serão usadas como as colunas de distribuição para a tabela temporária. |
<operation_cost cost="40" accumulative_cost="40" average_rowsize = "50" output_rows="100"/><location distribution="AllDistributions" /><source_statement type="statement">SELECT [TableAlias_3b77ee1d8ccf4a94ba644118b355db9d].[dist_date] FROM [qatest].[dbo].[flyers] [TableAlias_3b77ee1d8ccf4a94ba644118b355db9d] </source_statement><destination_table>Q_[TEMP_ID_259]_[PARTITION_ID]</destination_table><shuffle_columns>dist_date;</shuffle_columns><shuffle_columns>Email;Date;</shuffle_columns> |
| MetaDataCreate_Operation | <source_table>: a tabela de origem da operação.<destination_table>: a tabela de destino da operação. |
<source_table>databases</source_table><destination_table>MetaDataCreateLandingTempTable</destination_table> |
| ATIVADO | <location>: confira <location>.<sql_operation>: identifica o comando SQL que será executado em um nó. |
<location permanent="false" distribution="AllDistributions">Compute</location><sql_operation type="statement">CREATE TABLE [tempdb].[dbo]. [Q_[TEMP_ID_259]]_ [PARTITION_ID]]]([dist_date] DATE) WITH (DISTRIBUTION = HASH([dist_date]),) </sql_operation> |
| RemoteOnOperation | <DestinationCatalog>: o catálogo de destino.<DestinationSchema>: O esquema de destino em DestinationCatalog.<DestinationTableName>: O nome da tabela de destino ou "TableName".<DestinationDatasource>: o nome da fonte de dados de destino.<Username> e <Password>: Esses campos indicam que um nome de usuário e senha para o destino podem ser necessários.<CreateStatement>: a instrução de criação de tabela do banco de dados de destino. |
<DestinationCatalog>master</DestinationCatalog><DestinationSchema>dbo</DestinationSchema><DestinationTableName>TableName</DestinationTableName><DestinationDatasource>DestDataSource</DestinationDatasource><Username>...</Username><Password>...</Password><CreateStatement>CREATE TABLE [master].[dbo].[TableName] ([col1] BIGINT) ON [PRIMARY] WITH(DATA_COMPRESSION=PAGE);</CreateStatement> |
| RETURN | <resultset>: o identificador do conjunto de resultados. |
<resultset>RS_19</resultset> |
| RND_ID | <identifier>: o identificador do objeto criado. |
<identifier>TEMP_ID_260</identifier> |
Limitações
EXPLAIN pode ser aplicado a consultas otimizáveis, que são consultas que podem ser melhoradas ou modificadas com base nos resultados de um comando EXPLAIN. Os comandos EXPLAIN com suporte são fornecidos na seção anterior. A tentativa de usar EXPLAIN com um tipo de consulta não compatível retornará um erro ou uma duplicação da consulta.
Não há compatibilidade com EXPLAIN em uma transação de usuário.
Exemplos
O exemplo a seguir mostra um comando EXPLAIN executado em uma instrução SELECT e o resultado XML.
Enviando uma instrução EXPLAIN
O comando enviado para este exemplo é:
-- Uses AdventureWorks
EXPLAIN
SELECT CAST (AVG(YearlyIncome) AS int) AS AverageIncome,
CAST(AVG(FIS.SalesAmount) AS int) AS AverageSales,
G.StateProvinceName, T.SalesTerritoryGroup
FROM dbo.DimGeography AS G
JOIN dbo.DimSalesTerritory AS T
ON G.SalesTerritoryKey = T.SalesTerritoryKey
JOIN dbo.DimCustomer AS C
ON G.GeographyKey = C.GeographyKey
JOIN dbo.FactInternetSales AS FIS
ON C.CustomerKey = FIS.CustomerKey
WHERE T.SalesTerritoryGroup IN ('North America', 'Pacific')
AND Gender = 'F'
GROUP BY G.StateProvinceName, T.SalesTerritoryGroup
ORDER BY AVG(YearlyIncome) DESC;
GO
Depois de executar a instrução usando a opção EXPLAIN, a guia de mensagem apresentará uma única linha intitulada explain começando com o texto XML \<?xml version="1.0" encoding="utf-8"?>. Selecione XML para abrir o texto inteiro em um janela de XML. Para entender melhor os comentários a seguir, você deve ativar a exibição de números de linha no SSDT.
Ativar os números de linha
Com a saída aparecendo no SSDT na guia explain, no menu FERRAMENTAS, selecione Opções.
Expanda a seção Editor de Texto, expanda o XML e selecione Geral.
Na área Exibição, marque Números de linha.
Selecione OK.
Exemplo de saída de EXPLAIN
O resultado XML do comando EXPLAIN com números de linha ativados é:
1 \<?xml version="1.0" encoding="utf-8"?>
2 <dsql_query>
3 <sql>SELECT CAST (AVG(YearlyIncome) AS int) AS AverageIncome,
4 CAST(AVG(FIS.SalesAmount) AS int) AS AverageSales,
5 G.StateProvinceName, T.SalesTerritoryGroup
6 FROM dbo.DimGeography AS G
7 JOIN dbo.DimSalesTerritory AS T
8 ON G.SalesTerritoryKey = T.SalesTerritoryKey
9 JOIN dbo.DimCustomer AS C
10 ON G.GeographyKey = C.GeographyKey
11 JOIN dbo.FactInternetSales AS FIS
12 ON C.CustomerKey = FIS.CustomerKey
13 WHERE T.SalesTerritoryGroup IN ('North America', 'Pacific')
14 AND Gender = 'F'
15 GROUP BY G.StateProvinceName, T.SalesTerritoryGroup
16 ORDER BY AVG(YearlyIncome) DESC</sql>
17 <dsql_operations total_cost="0.926237696" total_number_operations="9">
18 <dsql_operation operation_type="RND_ID">
19 <identifier>TEMP_ID_16893</identifier>
20 </dsql_operation>
21 <dsql_operation operation_type="ON">
22 <location permanent="false" distribution="AllComputeNodes" />
23 <sql_operations>
24 <sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_16893] ([CustomerKey] INT NOT NULL, [GeographyKey] INT, [YearlyIncome] MONEY ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
25 </sql_operations>
26 </dsql_operation>
27 <dsql_operation operation_type="BROADCAST_MOVE">
28 <operation_cost cost="0.121431552" accumulative_cost="0.121431552" average_rowsize="16" output_rows="31.6228" />
29 <source_statement>SELECT [T1_1].[CustomerKey] AS [CustomerKey],
30 [T1_1].[GeographyKey] AS [GeographyKey],
31 [T1_1].[YearlyIncome] AS [YearlyIncome]
32 FROM (SELECT [T2_1].[CustomerKey] AS [CustomerKey],
33 [T2_1].[GeographyKey] AS [GeographyKey],
34 [T2_1].[YearlyIncome] AS [YearlyIncome]
35 FROM [AdventureWorksPDW2012].[dbo].[DimCustomer] AS T2_1
36 WHERE ([T2_1].[Gender] = CAST (N'F' COLLATE Latin1_General_100_CI_AS_KS_WS AS NVARCHAR (1)) COLLATE Latin1_General_100_CI_AS_KS_WS)) AS T1_1</source_statement>
37 <destination_table>[TEMP_ID_16893]</destination_table>
38 </dsql_operation>
39 <dsql_operation operation_type="RND_ID">
40 <identifier>TEMP_ID_16894</identifier>
41 </dsql_operation>
42 <dsql_operation operation_type="ON">
43 <location permanent="false" distribution="AllDistributions" />
44 <sql_operations>
45 <sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_16894] ([StateProvinceName] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_KS_WS, [SalesTerritoryGroup] NVARCHAR(50) COLLATE Latin1_General_100_CI_AS_KS_WS NOT NULL, [col] BIGINT, [col1] MONEY NOT NULL, [col2] BIGINT, [col3] MONEY NOT NULL ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
46 </sql_operations>
47 </dsql_operation>
48 <dsql_operation operation_type="SHUFFLE_MOVE">
49 <operation_cost cost="0.804806144" accumulative_cost="0.926237696" average_rowsize="232" output_rows="108.406" />
50 <source_statement>SELECT [T1_1].[StateProvinceName] AS [StateProvinceName],
51 [T1_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
52 [T1_1].[col2] AS [col],
53 [T1_1].[col] AS [col1],
54 [T1_1].[col3] AS [col2],
55 [T1_1].[col1] AS [col3]
56 FROM (SELECT ISNULL([T2_1].[col1], CONVERT (MONEY, 0.00, 0)) AS [col],
57 ISNULL([T2_1].[col3], CONVERT (MONEY, 0.00, 0)) AS [col1],
58 [T2_1].[StateProvinceName] AS [StateProvinceName],
59 [T2_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
60 [T2_1].[col] AS [col2],
61 [T2_1].[col2] AS [col3]
62 FROM (SELECT COUNT_BIG([T3_2].[YearlyIncome]) AS [col],
63 SUM([T3_2].[YearlyIncome]) AS [col1],
64 COUNT_BIG(CAST ((0) AS INT)) AS [col2],
65 SUM([T3_2].[SalesAmount]) AS [col3],
66 [T3_2].[StateProvinceName] AS [StateProvinceName],
67 [T3_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
68 FROM (SELECT [T4_1].[SalesTerritoryKey] AS [SalesTerritoryKey],
69 [T4_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
70 FROM [AdventureWorksPDW2012].[dbo].[DimSalesTerritory] AS T4_1
71 WHERE (([T4_1].[SalesTerritoryGroup] = CAST (N'North America' COLLATE Latin1_General_100_CI_AS_KS_WS AS NVARCHAR (13)) COLLATE Latin1_General_100_CI_AS_KS_WS)
72 OR ([T4_1].[SalesTerritoryGroup] = CAST (N'Pacific' COLLATE Latin1_General_100_CI_AS_KS_WS AS NVARCHAR (7)) COLLATE Latin1_General_100_CI_AS_KS_WS))) AS T3_1
73 INNER JOIN
74 (SELECT [T4_1].[SalesTerritoryKey] AS [SalesTerritoryKey],
75 [T4_2].[YearlyIncome] AS [YearlyIncome],
76 [T4_2].[SalesAmount] AS [SalesAmount],
77 [T4_1].[StateProvinceName] AS [StateProvinceName]
78 FROM [AdventureWorksPDW2012].[dbo].[DimGeography] AS T4_1
79 INNER JOIN
80 (SELECT [T5_2].[GeographyKey] AS [GeographyKey],
81 [T5_2].[YearlyIncome] AS [YearlyIncome],
82 [T5_1].[SalesAmount] AS [SalesAmount]
83 FROM [AdventureWorksPDW2012].[dbo].[FactInternetSales] AS T5_1
84 INNER JOIN
85 [tempdb].[dbo].[TEMP_ID_16893] AS T5_2
86 ON ([T5_1].[CustomerKey] = [T5_2].[CustomerKey])) AS T4_2
87 ON ([T4_2].[GeographyKey] = [T4_1].[GeographyKey])) AS T3_2
88 ON ([T3_1].[SalesTerritoryKey] = [T3_2].[SalesTerritoryKey])
89 GROUP BY [T3_2].[StateProvinceName], [T3_1].[SalesTerritoryGroup]) AS T2_1) AS T1_1</source_statement>
90 <destination_table>[TEMP_ID_16894]</destination_table>
91 <shuffle_columns>StateProvinceName;</shuffle_columns>
92 </dsql_operation>
93 <dsql_operation operation_type="ON">
94 <location permanent="false" distribution="AllComputeNodes" />
95 <sql_operations>
96 <sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_16893]</sql_operation>
97 </sql_operations>
98 </dsql_operation>
99 <dsql_operation operation_type="RETURN">
100 <location distribution="AllDistributions" />
101 <select>SELECT [T1_1].[col] AS [col],
102 [T1_1].[col1] AS [col1],
103 [T1_1].[StateProvinceName] AS [StateProvinceName],
104 [T1_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
105 [T1_1].[col2] AS [col2]
106 FROM (SELECT CONVERT (INT, [T2_1].[col], 0) AS [col],
107 CONVERT (INT, [T2_1].[col1], 0) AS [col1],
108 [T2_1].[StateProvinceName] AS [StateProvinceName],
109 [T2_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup],
110 [T2_1].[col] AS [col2]
111 FROM (SELECT CASE
112 WHEN ([T3_1].[col] = CAST ((0) AS BIGINT)) THEN CAST (NULL AS MONEY)
113 ELSE ([T3_1].[col1] / CONVERT (MONEY, [T3_1].[col], 0))
114 END AS [col],
115 CASE
116 WHEN ([T3_1].[col2] = CAST ((0) AS BIGINT)) THEN CAST (NULL AS MONEY)
117 ELSE ([T3_1].[col3] / CONVERT (MONEY, [T3_1].[col2], 0))
118 END AS [col1],
119 [T3_1].[StateProvinceName] AS [StateProvinceName],
120 [T3_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
121 FROM (SELECT ISNULL([T4_1].[col], CONVERT (BIGINT, 0, 0)) AS [col],
122 ISNULL([T4_1].[col1], CONVERT (MONEY, 0.00, 0)) AS [col1],
123 ISNULL([T4_1].[col2], CONVERT (BIGINT, 0, 0)) AS [col2],
124 ISNULL([T4_1].[col3], CONVERT (MONEY, 0.00, 0)) AS [col3],
125 [T4_1].[StateProvinceName] AS [StateProvinceName],
126 [T4_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
127 FROM (SELECT SUM([T5_1].[col]) AS [col],
128 SUM([T5_1].[col1]) AS [col1],
129 SUM([T5_1].[col2]) AS [col2],
130 SUM([T5_1].[col3]) AS [col3],
131 [T5_1].[StateProvinceName] AS [StateProvinceName],
132 [T5_1].[SalesTerritoryGroup] AS [SalesTerritoryGroup]
133 FROM [tempdb].[dbo].[TEMP_ID_16894] AS T5_1
134 GROUP BY [T5_1].[StateProvinceName], [T5_1].[SalesTerritoryGroup]) AS T4_1) AS T3_1) AS T2_1) AS T1_1
135 ORDER BY [T1_1].[col2] DESC</select>
136 </dsql_operation>
137 <dsql_operation operation_type="ON">
138 <location permanent="false" distribution="AllDistributions" />
139 <sql_operations>
140 <sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_16894]</sql_operation>
141 </sql_operations>
142 </dsql_operation>
143 </dsql_operations>
144 </dsql_query>
Significado da saída de EXPLAIN
A saída no bloco de código anterior contém 144 linhas numeradas. Sua saída dessa consulta pode ser um pouco diferente. A lista a seguir descreve as seções significativas.
As linhas 3 a 16 fornecem uma descrição da consulta que está sendo analisada.
A linha 17 especifica que o número total de operações será 9. Encontre o início de cada operação procurando as palavras dsql_operation.
A linha 18 inicia a operação 1. As linhas 18 e 19 indicam que uma operação RND_ID criará um número de ID aleatório que será usado para uma descrição do objeto. O objeto descrito na amostra de saída é TEMP_ID_16893. O seu número será diferente.
A linha 20 inicia a operação 2. Linhas 21 a 25: em todos os nós de computação, criam uma tabela temporária denominada
TEMP_ID_16893.A linha 26 inicia a operação 3. Linhas 27 a 37: movem os dados para
TEMP_ID_16893usando uma difusão. A consulta enviada a cada nó de computação é fornecida. A linha 37 especifica que a tabela de destino éTEMP_ID_16893.A linha 38 inicia a operação 4. Linhas 39 a 40: criam uma ID aleatória para uma tabela. TEMP_ID_16894 é o número da ID no exemplo. O seu número será diferente.
A linha 41 inicia a operação 5. Linhas 42 a 46: em todos os nós, criam uma tabela temporária denominada
TEMP_ID_16894.A linha 47 inicia a operação 6. Linhas 48 a 91: movem os dados de várias tabelas (incluindo a
TEMP_ID_16893) para a tabelaTEMP_ID_16893, usando uma operação de movimentação em ordem aleatória. A consulta enviada a cada nó de computação é fornecida. A linha 90 especifica a tabela de destino comoTEMP_ID_16894. A linha 91 especifica as colunas.A linha 92 inicia a operação 7. Linhas 93 a 97: em todos os nós de computação, removem a tabela temporária
TEMP_ID_16893.A linha 98 inicia a operação 8. Linhas 99 a 135: retornam resultados para o cliente. Usa a consulta fornecida para obter os resultados.
A linha 136 inicia a operação 9. Linhas 137 a 140: Em todos os nós, removem a tabela temporária
TEMP_ID_16894.
Envio de uma instrução EXPLAIN WITH_RECOMMENDATIONS
EXPLAIN WITH_RECOMMENDATIONS
select count(*)
from ((select distinct c_last_name, c_first_name, d_date
from store_sales, date_dim, customer
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
except
(select distinct c_last_name, c_first_name, d_date
from catalog_sales, date_dim, customer
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
) top_customers
Exemplo de saída para EXPLAIN WITH_RECOMMENDATIONS
A seguinte amostra de saída inclui a criação da exibição materializada recomendada chamada View1.
<?xml version="1.0" encoding="utf-8"?>
<dsql_query number_nodes="1" number_distributions="8" number_distributions_per_node="8">
<sql>select count(*)
from ((select distinct c_last_name, c_first_name, d_date
from store_sales, date_dim, customer
where store_sales.ss_sold_date_sk = date_dim.d_date_sk
and store_sales.ss_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
except
(select distinct c_last_name, c_first_name, d_date
from catalog_sales, date_dim, customer
where catalog_sales.cs_sold_date_sk = date_dim.d_date_sk
and catalog_sales.cs_bill_customer_sk = customer.c_customer_sk
and d_month_seq between 1194 and 1194+11)
) top_customers</sql>
<materialized_view_candidates>
<materialized_view_candidates with_constants="False">CREATE MATERIALIZED VIEW View1 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2],
[tpcds10].[dbo].[date_dim].[d_month_seq] AS [Expr3]
FROM [dbo].[store_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[store_sales].[ss_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[store_sales].[ss_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date],
[tpcds10].[dbo].[date_dim].[d_month_seq]</materialized_view_candidates>
<materialized_view_candidates with_constants="False">CREATE MATERIALIZED VIEW View2 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2],
[tpcds10].[dbo].[date_dim].[d_month_seq] AS [Expr3]
FROM [dbo].[catalog_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[catalog_sales].[cs_bill_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[catalog_sales].[cs_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date],
[tpcds10].[dbo].[date_dim].[d_month_seq]</materialized_view_candidates>
<materialized_view_candidates with_constants="True">CREATE MATERIALIZED VIEW View3 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2]
FROM [dbo].[store_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[store_sales].[ss_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[store_sales].[ss_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]>=(1194))
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]<=(1205))
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date]</materialized_view_candidates>
<materialized_view_candidates with_constants="True">CREATE MATERIALIZED VIEW View4 WITH (DISTRIBUTION = HASH([Expr0])) AS
SELECT [tpcds10].[dbo].[customer].[c_last_name] AS [Expr0],
[tpcds10].[dbo].[customer].[c_first_name] AS [Expr1],
[tpcds10].[dbo].[date_dim].[d_date] AS [Expr2]
FROM [dbo].[catalog_sales],
[dbo].[date_dim],
[dbo].[customer]
WHERE ([tpcds10].[dbo].[catalog_sales].[cs_bill_customer_sk]=[tpcds10].[dbo].[customer].[c_customer_sk])
AND ([tpcds10].[dbo].[catalog_sales].[cs_sold_date_sk]=[tpcds10].[dbo].[date_dim].[d_date_sk])
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]>=(1194))
AND ([tpcds10].[dbo].[date_dim].[d_month_seq]<=(1205))
GROUP BY [tpcds10].[dbo].[customer].[c_last_name],
[tpcds10].[dbo].[customer].[c_first_name],
[tpcds10].[dbo].[date_dim].[d_date]</materialized_view_candidates>
</materialized_view_candidates>
<dsql_operations total_cost="3472197.35650704" total_number_operations="28">
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_1</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_1] ([c_customer_sk] INT NOT NULL, [c_first_name] CHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [c_last_name] CHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="842400" accumulative_cost="842400" average_rowsize="54" output_rows="65000000" GroupNumber="44" />
<source_statement>SELECT [T1_1].[c_customer_sk] AS [c_customer_sk],
[T1_1].[c_first_name] AS [c_first_name],
[T1_1].[c_last_name] AS [c_last_name]
FROM [tpcds10].[dbo].[customer] AS T1_1</source_statement>
<destination_table>[TEMP_ID_1]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_2</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_2] ([d_date_sk] INT NOT NULL, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="0.62729352" accumulative_cost="842400.62729352" average_rowsize="7" output_rows="373.389" GroupNumber="43" />
<source_statement>SELECT [T1_1].[d_date_sk] AS [d_date_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_1].[d_date_sk] AS [d_date_sk],
[T2_1].[d_date] AS [d_date]
FROM [tpcds10].[dbo].[date_dim] AS T2_1
WHERE (([T2_1].[d_month_seq] >= CAST ((1194) AS INT))
AND ([T2_1].[d_month_seq] <= CAST ((1205) AS INT)))) AS T1_1</source_statement>
<destination_table>[TEMP_ID_2]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_3</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_3] ([cs_bill_customer_sk] INT, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="SHUFFLE_MOVE">
<operation_cost cost="610362.9" accumulative_cost="1452763.52729352" average_rowsize="7" output_rows="2906490000" GroupNumber="57" />
<source_statement>SELECT [T1_1].[cs_bill_customer_sk] AS [cs_bill_customer_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_2].[cs_bill_customer_sk] AS [cs_bill_customer_sk],
[T2_1].[d_date] AS [d_date]
FROM [tempdb].[dbo].[TEMP_ID_2] AS T2_1
INNER JOIN
[tpcds10].[dbo].[catalog_sales] AS T2_2
ON ([T2_2].[cs_sold_date_sk] = [T2_1].[d_date_sk])) AS T1_1</source_statement>
<destination_table>[TEMP_ID_3]</destination_table>
<shuffle_columns>d_date;</shuffle_columns>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_2]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_4</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_4] ([c_customer_sk] INT NOT NULL, [c_first_name] CHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS, [c_last_name] CHAR(30) COLLATE SQL_Latin1_General_CP1_CI_AS ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="842400" accumulative_cost="2295163.52729352" average_rowsize="54" output_rows="65000000" GroupNumber="36" />
<source_statement>SELECT [T1_1].[c_customer_sk] AS [c_customer_sk],
[T1_1].[c_first_name] AS [c_first_name],
[T1_1].[c_last_name] AS [c_last_name]
FROM [tpcds10].[dbo].[customer] AS T1_1</source_statement>
<destination_table>[TEMP_ID_4]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_5</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_5] ([d_date_sk] INT NOT NULL, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="BROADCAST_MOVE">
<operation_cost cost="0.62729352" accumulative_cost="2295164.15458704" average_rowsize="7" output_rows="373.389" GroupNumber="35" />
<source_statement>SELECT [T1_1].[d_date_sk] AS [d_date_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_1].[d_date_sk] AS [d_date_sk],
[T2_1].[d_date] AS [d_date]
FROM [tpcds10].[dbo].[date_dim] AS T2_1
WHERE (([T2_1].[d_month_seq] >= CAST ((1194) AS INT))
AND ([T2_1].[d_month_seq] <= CAST ((1205) AS INT)))) AS T1_1</source_statement>
<destination_table>[TEMP_ID_5]</destination_table>
</dsql_operation>
<dsql_operation operation_type="RND_ID">
<identifier>TEMP_ID_6</identifier>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[dbo].[TEMP_ID_6] ([ss_customer_sk] INT, [d_date] DATE ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="SHUFFLE_MOVE">
<operation_cost cost="1177033.2" accumulative_cost="3472197.35458704" average_rowsize="7" output_rows="5604920000" GroupNumber="54" />
<source_statement>SELECT [T1_1].[ss_customer_sk] AS [ss_customer_sk],
[T1_1].[d_date] AS [d_date]
FROM (SELECT [T2_2].[ss_customer_sk] AS [ss_customer_sk],
[T2_1].[d_date] AS [d_date]
FROM [tempdb].[dbo].[TEMP_ID_5] AS T2_1
INNER JOIN
[tpcds10].[dbo].[store_sales] AS T2_2
ON ([T2_2].[ss_sold_date_sk] = [T2_1].[d_date_sk])) AS T1_1</source_statement>
<destination_table>[TEMP_ID_6]</destination_table>
<shuffle_columns>d_date;</shuffle_columns>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_5]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="Control" />
<sql_operations>
<sql_operation type="statement">CREATE TABLE [tempdb].[QTables].[QTable_87367172aa554f06b73cf3ed97e5b985] ([col] BIGINT ) WITH(DATA_COMPRESSION=PAGE);</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="PARTITION_MOVE">
<operation_cost cost="0.00192" accumulative_cost="3472197.35650704" average_rowsize="8" output_rows="1" GroupNumber="66" />
<location distribution="AllDistributions" />
<source_statement>SELECT [T1_1].[col] AS [col]
FROM (SELECT COUNT_BIG(CAST ((0) AS INT)) AS [col]
FROM (SELECT 0 AS [col]
FROM [tempdb].[dbo].[TEMP_ID_4] AS T3_1
INNER JOIN
[tempdb].[dbo].[TEMP_ID_6] AS T3_2
ON ([T3_2].[ss_customer_sk] = [T3_1].[c_customer_sk])
GROUP BY [T3_1].[c_last_name], [T3_1].[c_first_name], [T3_2].[d_date]
HAVING NOT EXISTS (SELECT 1 AS C1
FROM [tempdb].[dbo].[TEMP_ID_1] AS T4_1
INNER JOIN
[tempdb].[dbo].[TEMP_ID_3] AS T4_2
ON ([T4_2].[cs_bill_customer_sk] = [T4_1].[c_customer_sk])
GROUP BY [T4_1].[c_last_name], [T4_1].[c_first_name], [T4_2].[d_date]
HAVING (([T3_1].[c_last_name] = [T4_1].[c_last_name]
OR ([T3_1].[c_last_name] IS NULL
AND [T4_1].[c_last_name] IS NULL))
AND ([T3_1].[c_first_name] = [T4_1].[c_first_name]
OR ([T3_1].[c_first_name] IS NULL
AND [T4_1].[c_first_name] IS NULL))
AND ([T3_2].[d_date] = [T4_2].[d_date]
OR ([T3_2].[d_date] IS NULL
AND [T4_2].[d_date] IS NULL))))) AS T2_1
GROUP BY [T2_1].[col]) AS T1_1</source_statement>
<destination>Control</destination>
<destination_table>[QTable_87367172aa554f06b73cf3ed97e5b985]</destination_table>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_6]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_4]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllDistributions" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_3]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="AllComputeNodes" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[dbo].[TEMP_ID_1]</sql_operation>
</sql_operations>
</dsql_operation>
<dsql_operation operation_type="RETURN">
<location distribution="Control" />
<select>SELECT [T1_1].[col] AS [col]
FROM (SELECT CONVERT (INT, [T2_1].[col], 0) AS [col]
FROM (SELECT ISNULL([T3_1].[col], CONVERT (BIGINT, 0, 0)) AS [col]
FROM (SELECT SUM([T4_1].[col]) AS [col]
FROM [tempdb].[QTables].[QTable_87367172aa554f06b73cf3ed97e5b985] AS T4_1) AS T3_1) AS T2_1) AS T1_1</select>
</dsql_operation>
<dsql_operation operation_type="ON">
<location permanent="false" distribution="Control" />
<sql_operations>
<sql_operation type="statement">DROP TABLE [tempdb].[QTables].[QTable_87367172aa554f06b73cf3ed97e5b985]</sql_operation>
</sql_operations>
</dsql_operation>
</dsql_operations>
</dsql_query>
Conteúdo relacionado
- sys.pdw_materialized_view_column_distribution_properties (Transact-SQL)
- sys.pdw_materialized_view_distribution_properties (Transact-SQL)
- sys.pdw_materialized_view_mappings (Transact-SQL)
- Exibições de catálogo do Azure Synapse Analytics e do PDW (Analytics Platform System)
- Exibições do sistema com suporte no Azure Synapse Analytics
- Instruções T-SQL com suporte no Azure Synapse Analytics
- CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)
- ALTER MATERIALIZED VIEW (Transact-SQL)
- DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD (Transact-SQL)
- Monitorar sua carga de trabalho do pool de SQL dedicado do Azure Synapse Analytics usando DMVs
- Exibir o plano de execução estimado