Processamento inteligente de consultas em bancos de dados SQLIntelligent query processing in SQL databases

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure nãoAzure Synapse Analytics (SQL DW) nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

A família de recursos de IQP (processamento de consulta inteligente) inclui recursos de amplo impacto que melhoram o desempenho de cargas de trabalho existentes com esforço mínimo de implementação na adoção.The intelligent query processing (IQP) feature family includes features with broad impact that improve the performance of existing workloads with minimal implementation effort to adopt.

Processamento de consulta inteligente

Você pode deixar as cargas de trabalho automaticamente qualificadas para o processamento de consulta inteligente habilitando o nível de compatibilidade do banco de dados aplicável.You can make workloads automatically eligible for intelligent query processing by enabling the applicable database compatibility level for the database. Você pode definir isso usando Transact-SQLTransact-SQL.You can set this using Transact-SQLTransact-SQL. Por exemplo:For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

A tabela a seguir detalha todos os recursos de processamento de consulta inteligente, juntamente com todos os requisitos para o nível de compatibilidade do banco de dados.The following table details all intelligent query processing features, along with any requirement they have for database compatibility level.

Recurso IQPIQP Feature Com suporte no Banco de Dados SQL do AzureSupported in Azure SQL Database Com suporte no SQL ServerSupported in SQL Server DescriçãoDescription
Junções adaptáveis (Modo de Lote)Adaptive Joins (Batch Mode) Sim, no nível de compatibilidade 140Yes, under compatibility level 140 Sim, começando no SQL Server 2017 (14.x)SQL Server 2017 (14.x) no nível de compatibilidade 140Yes, starting in SQL Server 2017 (14.x)SQL Server 2017 (14.x) under compatibility level 140 As junções adaptáveis selecionam automaticamente um tipo de junção durante o tempo de execução com base nas linhas de entrada reais.Adaptive joins dynamically select a join type during runtime based on actual input rows.
Distinção de contagem aproximadaApproximate Count Distinct Sim, versão prévia públicaYes, public preview Sim, começando no SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0Yes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 Forneça o COUNT DISTINCT aproximado para cenários de big data com o benefício de alto desempenho e baixo volume de memória.Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
Modo de Lote no RowstoreBatch Mode on Rowstore Sim, no nível de compatibilidade 150, versão prévia públicaYes, under compatibility level 150, public preview Sim, começando no SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 no nível de compatibilidade 150, versão prévia públicaYes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 under compatibility level 150, public preview Forneça o modo de lote para cargas de trabalho de DW relacionais vinculados à CPU sem exigir índices columnstore.Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
Execução intercaladaInterleaved Execution Sim, no nível de compatibilidade 140Yes, under compatibility level 140 Sim, começando no SQL Server 2017 (14.x)SQL Server 2017 (14.x) no nível de compatibilidade 140Yes, starting in SQL Server 2017 (14.x)SQL Server 2017 (14.x) under compatibility level 140 Use a cardinalidade real da função com valor de tabela de várias instruções encontrada na primeira compilação em vez de uma estimativa fixa.Use the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
Comentários de concessão de memória (Modo de Lote)Memory Grant Feedback (Batch Mode) Sim, no nível de compatibilidade 140Yes, under compatibility level 140 Sim, começando no SQL Server 2017 (14.x)SQL Server 2017 (14.x) no nível de compatibilidade 140Yes, starting in SQL Server 2017 (14.x)SQL Server 2017 (14.x) under compatibility level 140 Se uma consulta de modo de lote tiver operações que são despejadas no disco, aumente a memória para execuções consecutivas.If a batch mode query has operations that spill to disk, add more memory for consecutive executions. Se uma consulta desperdiçar mais de 50% da memória alocada para ela, reduza o lado de concessão de memória para execuções consecutivas.If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
Comentários de concessão de memória (Modo de Lote)Memory Grant Feedback (Row Mode) Sim, no nível de compatibilidade 150, versão prévia públicaYes, under compatibility level 150, public preview Sim, começando no SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 no nível de compatibilidade 150, versão prévia públicaYes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 under compatibility level 150, public preview Se uma consulta de modo de linha tiver operações que são despejadas no disco, aumente a memória para execuções consecutivas.If a row mode query has operations that spill to disk, add more memory for consecutive executions. Se uma consulta desperdiçar mais de 50% da memória alocada para ela, reduza o lado de concessão de memória para execuções consecutivas.If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
Inlining de UDF escalarScalar UDF Inlining NãoNo Sim, começando no SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.1 no nível de compatibilidade 150, versão prévia públicaYes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.1 under compatibility level 150, public preview Os UDFs escalares são transformados em expressões relacionais equivalentes que são "embutidas" na consulta que fez a chamada, geralmente resultando em ganhos significativos de desempenho.Scalar UDFs are transformed into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains.
Compilação Adiada de Variável da TabelaTable Variable Deferred Compilation Sim, no nível de compatibilidade 150, versão prévia públicaYes, under compatibility level 150, public preview Sim, começando no SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 no nível de compatibilidade 150, versão prévia públicaYes, starting in SQL Server 2019 (15.x)SQL Server 2019 (15.x) CTP 2.0 under compatibility level 150, public preview Use a cardinalidade real da variável de tabela encontrada na primeira compilação em vez de uma estimativa fixa.Use the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.

Junções adaptáveis de modo de loteBatch mode Adaptive joins

O recurso de Junções Adaptáveis de modo de lote permite que a escolha de um método de Junção hash ou de Junção de loops aninhados seja adiada até depois que a primeira entrada for verificada, usando um único plano em cache.The batch mode Adaptive Joins feature enables the choice of a Hash Join or Nested Loops Join method to be deferred until after the first input has been scanned, by using a single cached plan. O operador de Junção Adaptável define um limite que é usado para decidir quando mudar para um plano de Loops aninhados.The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. Seu plano, portanto, pode alternar dinamicamente para uma estratégia de junção melhor durante a execução.Your plan can therefore dynamically switch to a better join strategy during execution.

Para obter mais informações, incluindo como desabilitar junções adaptáveis sem alterar o nível de compatibilidade, confira Noções básicas sobre junções adaptáveis.For more information, including how to disable Adaptive joins without changing the compatibility level, see Understanding Adaptive joins.

Comentários de concessão de memória de modo de loteBatch mode memory grant feedback

Um plano de pós-execução da consulta no SQL ServerSQL Server inclui a memória mínima necessária para execução e o tamanho da concessão de memória ideal para que todas as linhas caibam na memória.A query's post-execution plan in SQL ServerSQL Server includes the minimum required memory needed for execution and the ideal memory grant size to have all rows fit in memory. Desempenho é prejudicado quando os tamanhos de concessão de memória são dimensionados incorretamente.Performance suffers when memory grant sizes are incorrectly sized. Concessões excessivas resultam em desperdício de memória e em redução de simultaneidade.Excessive grants result in wasted memory and reduced concurrency. Concessões de memória insuficientes causam despejos dispendiosos no disco.Insufficient memory grants cause expensive spills to disk. Lidando com cargas de trabalho repetitivas, os comentários de concessão de memória de modo de lote recalcula a memória real necessária para uma consulta e atualiza o valor de concessão do plano armazenado em cache.By addressing repeating workloads, batch mode memory grant feedback recalculates the actual memory required for a query and then updates the grant value for the cached plan. Quando uma instrução de consulta idêntica for executada, a consulta usará o tamanho de concessão de memória revisado, reduzindo concessões de memória excessivas que afetam a simultaneidade e corrigindo concessões de memória subestimadas que causam despejos dispendiosos no disco.When an identical query statement is executed, the query uses the revised memory grant size, reducing excessive memory grants that impact concurrency and fixing underestimated memory grants that cause expensive spills to disk. O gráfico a seguir mostra um exemplo de uso dos comentários de concessão de memória adaptável de modo de lote.The following graph shows one example of using batch mode adaptive memory grant feedback. Na primeira execução da consulta, a duração foi de 88 segundos devido à grande quantidade de despejos:For the first execution of the query, duration was 88 seconds due to high spills:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';
SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime and @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Grande quantidade de despejos

Com os comentários de concessão de memória habilitado, na segunda execução, a duração é de 1 segundo (reduzido dos 88 segundos), os despejos são totalmente removidos e a concessão é maior:With memory grant feedback enabled, for the second execution, duration is 1 second (down from 88 seconds), spills are removed entirely, and the grant is higher:

Sem despejos

Dimensionamento de comentários de concessão de memóriaMemory grant feedback sizing

Para uma condição de concessão de memória excessiva, se a memória concedida for mais de duas vezes o tamanho da memória real usada, os comentários de concessão de memória recalcularão a concessão de memória e atualizarão o plano armazenado em cache.For an excessive memory grant condition, if the granted memory is more than two times the size of the actual used memory, memory grant feedback will recalculate the memory grant and update the cached plan. Os planos com concessões de memória abaixo de 1 MB não serão recalculados devido a excedentes.Plans with memory grants under 1 MB will not be recalculated for overages. Para uma condição de concessão de memória de tamanho insuficiente que resulta em um despejo no disco de operadores de modo de lote, os comentários de concessão de memória vão disparar o recálculo da concessão de memória.For an insufficiently sized memory grant condition, that result in a spill to disk for batch mode operators, memory grant feedback will trigger a recalculation of the memory grant. Os eventos de despejo são relatados para comentários de concessão de memória e podem ser apresentados por meio do XEvent spilling_report_to_memory_grant_feedback.Spill events are reported to memory grant feedback and can be surfaced via the spilling_report_to_memory_grant_feedback xEvent. Esse evento retorna a ID do nó do plano e o tamanho dos dados despejados desse nó.This event returns the node ID from the plan and spilled data size of that node.

Comentários de concessão de memória e cenários sensíveis a parâmetroMemory grant feedback and parameter sensitive scenarios

Diferentes valores de parâmetros também podem exigir diferentes planos de consulta para continuarem sendo ideais.Different parameter values may also require different query plans in order to remain optimal. Esse tipo de consulta é definido como "sensível a parâmetro".This type of query is defined as "parameter-sensitive." Para planos sensíveis a parâmetro, os comentários de concessão de memória serão desabilitados em uma consulta se ela tiver requisitos de memória instáveis.For parameter-sensitive plans, memory grant feedback will disable itself on a query if it has unstable memory requirements. O plano é desabilitado após várias execuções da consulta repetidas e isso pode ser observado pelo monitoramento do xEvent memory_grant_feedback_loop_disabled.The plan is disabled after several repeated runs of the query and this can be observed by monitoring the memory_grant_feedback_loop_disabled xEvent. Para obter mais informações sobre a detecção de parâmetro e a sensibilidade de parâmetro, veja o Guia de arquitetura de processamento de consultas.For more information about parameter sniffing and parameter sensitivity, refer to the Query Processing Architecture Guide.

Armazenamento em cache dos comentários de concessão de memóriaMemory grant feedback caching

Os comentários podem ser armazenados no plano em cache para uma única execução.Feedback can be stored in the cached plan for a single execution. No entanto, são as execuções consecutivas dessa instrução que se beneficiam dos ajustes dos comentários de concessão de memória.It is the consecutive executions of that statement, however, that benefit from the memory grant feedback adjustments. Esse recurso aplica-se à execução repetida de instruções.This feature applies to repeated execution of statements. Os comentários de concessão de memória vão alterar somente o plano armazenado em cache.Memory grant feedback will change only the cached plan. No momento, as alterações não são capturadas no Repositório de Consultas.Changes are currently not captured in the Query Store. Os comentários não serão mantidos se o plano for removido do cache.Feedback is not persisted if the plan is evicted from cache. Os comentários também serão perdidos se houver um failover.Feedback will also be lost if there is a failover. Uma instrução que usa OPTION (RECOMPILE) cria um plano e não o armazena em cache.A statement using OPTION (RECOMPILE) creates a new plan and does not cache it. Como ele não é armazenado em cache, nenhum comentário de concessão de memória é produzido e ele não é armazenado para essa compilação e execução.Since it is not cached, no memory grant feedback is produced and it is not stored for that compilation and execution. No entanto, se uma instrução equivalente (ou seja, com o mesmo hash de consulta) que não usou OPTION (RECOMPILE) for armazenada em cache e, em seguida, executada novamente, a instrução consecutiva poderá se beneficiar dos comentários de concessão de memória.However, if an equivalent statement (that is, with the same query hash) that did not use OPTION (RECOMPILE) was cached and then re-executed, the consecutive statement can benefit from memory grant feedback.

Acompanhando a atividade de comentários de concessão de memóriaTracking memory grant feedback activity

Você pode acompanhar os eventos de comentários de concessão de memória usando o xEvent memory_grant_updated_by_feedback.You can track memory grant feedback events using the memory_grant_updated_by_feedback xEvent. Este evento acompanha o histórico de contagem de execução atual, o número de vezes que o plano foi atualizado por comentários de concessão de memória, a concessão de memória adicional ideal antes da modificação e a concessão de memória adicional ideal depois que os comentários de concessão de memória modificaram o plano armazenado em cache.This event tracks the current execution count history, the number of times the plan has been updated by memory grant feedback, the ideal additional memory grant before modification and the ideal additional memory grant after memory grant feedback has modified the cached plan.

Comentários de concessão de memória, administrador de recursos e dicas de consultaMemory grant feedback, resource governor and query hints

A memória real concedida cumpre o limite de memória de consulta determinado pela dica de consulta ou pelo administrador de recursos.The actual memory granted honors the query memory limit determined by the resource governor or query hint.

Desabilitar comentários de concessão de memória de modo de lote sem alterar o nível de compatibilidadeDisabling batch mode memory grant feedback without changing the compatibility level

Comentários de concessão de memória podem ser desabilitados no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados como 140 e superior.Memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. Para desabilitar os comentários de concessão de memória em modo de lotes para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:To disable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Quando habilitada, essa configuração aparecerá como habilitada em sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations.

Para reabilitar os comentários de concessão de memória em modo de lotes para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:To re-enable batch mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Também é possível desabilitar os comentários de concessão de memória em modo de lote para uma consulta específica designando DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK como uma dica de consulta USE HINT.You can also disable batch mode memory grant feedback for a specific query by designating DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. Por exemplo:For example:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK')); 

Uma dica de consulta USE HINT tem precedência sobre uma configuração de escopo do banco de dados ou uma configuração de sinalizador de rastreamento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Comentários de concessão de memória do modo de linhaRow mode memory grant feedback

Aplica-se a: SQL ServerSQL Server (começando pelo SQL Server 2019 (15.x)SQL Server 2019 (15.x)) e Banco de dados SQL do AzureAzure SQL Database (versão prévia pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Banco de dados SQL do AzureAzure SQL Database (public preview)

Observação

Feedback de concessão de memória do modo de linha é uma versão prévia pública do recurso.Row mode memory grant feedback is a public preview feature.

Os comentários de concessão de memória de modo de linha expande o recurso de comentários de concessão de memória do modo de lote, ajustando os tamanhos de concessão de memória para operadores de modo de lote e de linha.Row mode memory grant feedback expands on the batch mode memory grant feedback feature by adjusting memory grant sizes for both batch and row mode operators.

Para habilitar a versão prévia pública dos comentários de concessão de memória em modo de linha no Banco de dados SQL do AzureAzure SQL Database, habilite o nível de compatibilidade do banco de dados 150 para o banco de dados ao qual você está conectado ao executar a consulta.To enable the public preview of row mode memory grant feedback in Banco de dados SQL do AzureAzure SQL Database, enable database compatibility level 150 for the database you are connected to when executing the query.

A atividade de comentários de concessão de memória de modo de linha ficará visível por meio do XEvent memory_grant_updated_by_feedback.Row mode memory grant feedback activity will be visible via the memory_grant_updated_by_feedback XEvent.

Começando com comentários de concessão de memória no modo de linha, serão exibidos dois novos atributos de plano de consulta para planos de pós-execução reais: IsMemoryGrantFeedbackAdjusted e LastRequestedMemory, que foram adicionados ao elemento XML MemoryGrantInfo do plano de consulta.Starting with row mode memory grant feedback, two new query plan attributes will be shown for actual post-execution plans: IsMemoryGrantFeedbackAdjusted and LastRequestedMemory, which are added to the MemoryGrantInfo query plan XML element.

LastRequestedMemory mostra a memória concedida em quilobytes (KB) na execução de consulta anterior.LastRequestedMemory shows the granted memory in Kilobytes (KB) from the prior query execution. O atributo IsMemoryGrantFeedbackAdjusted permite que você verifique o estado dos comentários de concessão de memória para a instrução dentro de um plano de execução de consulta real.IsMemoryGrantFeedbackAdjusted attribute allows you to check the state of memory grant feedback for the statement within an actual query execution plan. Os valores apresentados nesse atributo são os seguintes:Values surfaced in this attribute are as follows:

Valor de IsMemoryGrantFeedbackAdjustedIsMemoryGrantFeedbackAdjusted Value DescriçãoDescription
Não: Primeira execuçãoNo: First Execution Os comentários de concessão de memória não ajustam a memória para a primeira compilação e execução associada.Memory grant feedback does not adjust memory for the first compile and associated execution.
Não: Concessão precisaNo: Accurate Grant Se não houver despejo no disco, e a instrução usar pelo menos 50% da memória concedida, os comentários de concessão de memória não serão acionados.If there is no spill to disk and the statement uses at least 50% of the granted memory, then memory grant feedback is not triggered.
Não: Comentários desabilitadosNo: Feedback disabled Se os comentários de concessão de memória forem acionados continuamente e flutuarem entre as operações de aumento de memória e redução de memória, desabilitaremos os comentários de concessão de memória para a instrução.If memory grant feedback is continually triggered and fluctuates between memory-increase and memory-decrease operations, we will disable memory grant feedback for the statement.
Sim: AjusteYes: Adjusting Os comentários de concessão de memória foram aplicados e podem ser ainda mais ajustados para a próxima execução.Memory grant feedback has been applied and may be further adjusted for the next execution.
Sim: EstávelYes: Stable Os comentários de concessão de memória foram aplicados e a memória concedida está estável, ou seja, o que foi concedido para a execução anterior é o mesmo que foi concedido para a execução atual.Memory grant feedback has been applied and granted memory is now stable, meaning that what was last granted for the previous execution is what was granted for the current execution.

Observação

Os atributos do plano de comentários de concessão de memória do modo de linha da versão prévia pública estão visíveis nos planos de execução de consulta gráfica do SQL Server Management StudioSQL Server Management Studio nas versões 17.9 e superiores.The public preview row mode memory grant feedback plan attributes are visible in SQL Server Management StudioSQL Server Management Studio graphical query execution plans in versions 17.9 and higher.

Desabilitar comentários de concessão de memória de modo de linha sem alterar o nível de compatibilidadeDisabling row mode memory grant feedback without changing the compatibility level

Comentários de concessão de memória de modo de linha podem ser desabilitados no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados como 150 e superior.Row mode memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 150 and higher. Para desabilitar os comentários de concessão de memória em modo de linha para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:To disable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Para reabilitar os comentários de concessão de memória em modo de linha para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:To re-enable row mode memory grant feedback for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

Também é possível desabilitar os comentários de concessão de memória em modo de linha para uma consulta específica designando DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK como uma dica de consulta USE HINT.You can also disable row mode memory grant feedback for a specific query by designating DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK as a USE HINT query hint. Por exemplo:For example:

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK')); 

Uma dica de consulta USE HINT tem precedência sobre uma configuração de escopo do banco de dados ou uma configuração de sinalizador de rastreamento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Execução intercalada para MSTVFsInterleaved execution for MSTVFs

Com a execução intercalada, as contagens de linha reais da função são usadas para tornar decisões de plano de consulta downstream mais embasadas.With interleaved execution, the actual row counts from the function are used to make better-informed downstream query plan decisions. Confira mais informações sobre MSTVFs (funções com valor de tabela de várias instruções) em Funções com Valor de Tabela.For more information on multi-statement table-valued functions (MSTVFs), see Table-valued functions.

A execução intercalada altera o limite unidirecional entre as fases de execução e de otimização para a execução de uma única consulta e permite que os planos se adaptem com base nas estimativas de cardinalidade revisadas.Interleaved execution changes the unidirectional boundary between the optimization and execution phases for a single-query execution and enables plans to adapt based on the revised cardinality estimates. Durante a otimização, se encontrarmos uma candidata para execução intercalada, que são atualmente MSTVFs (funções com valor de tabela de várias instruções) , pausaremos a otimização, executaremos a subárvore aplicável, capturaremos as estimativas de cardinalidade precisas e retomaremos a otimização para operações de downstream.During optimization if we encounter a candidate for interleaved execution, which is currently multi-statement table-valued functions (MSTVFs), we will pause optimization, execute the applicable subtree, capture accurate cardinality estimates, and then resume optimization for downstream operations.

As MSTVFs têm uma estimativa de cardinalidade fixa de 100 começando com SQL Server 2014 (12.x)SQL Server 2014 (12.x) e de 1 para versões anteriores SQL ServerSQL Server.MSTVFs have a fixed cardinality guess of 100 starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x), and 1 for earlier SQL ServerSQL Server versions. A execução intercalada ajuda com problemas de desempenho da carga de trabalho causado por essas estimativas de cardinalidade fixas associadas às MSTVFs.Interleaved execution helps workload performance issues that are due to these fixed cardinality estimates associated with MSTVFs. Para obter mais informações sobre MSTVFs, confira Criar funções definidas pelo usuário (Mecanismo de Banco de Dados).For more information on MSTVFs, see Create User-defined Functions (Database Engine).

A imagem a seguir ilustra uma saída de Estatísticas de consulta dinâmica, um subconjunto de um plano de execução geral que mostra o impacto de estimativas de cardinalidade fixas de MSTVFs.The following image depicts a Live Query Statistics output, a subset of an overall execution plan that shows the impact of fixed cardinality estimates from MSTVFs. Você pode ver o fluxo versus de linhas reais versus as linhas estimadas.You can see the actual row flow vs. estimated rows. Há três áreas notáveis do plano (o fluxo é da direita para esquerda):There are three noteworthy areas of the plan (flow is from right to left):

  1. A verificação de tabela de MSTVF tem uma estimativa fixa de 100 linhas.The MSTVF Table Scan has a fixed estimate of 100 rows. Neste exemplo, no entanto, há 527.597 linhas que passam por essa Verificação de tabela de MSTVF, conforme visto nas Estatísticas de consulta dinâmicas por meio de 527597 de 100 reais das estimadas, portanto a estimativa fixa é significativamente distorcida.For this example, however, there are 527,597 rows flowing through this MSTVF Table Scan, as seen in Live Query Statistics via the 527597 of 100 actual of estimated - so the fixed estimate is significantly skewed.
  2. Para a operação de loops aninhados, apenas 100 linhas são consideradas retornadas pelo lado externo da junção.For the Nested Loops operation, only 100 rows are assumed to be returned by the outer side of the join. Devido ao grande número de linhas que realmente estão sendo retornadas pelo MSTVF, provavelmente melhor usar um algoritmo de junção completamente diferente.Given the high number of rows actually being returned by the MSTVF, you are likely better off with a different join algorithm altogether.
  3. Para a operação de correspondência de hash, observe o pequeno símbolo de aviso, que nesse caso está indicando um despejo no disco.For the Hash Match operation, notice the small warning symbol, which in this case is indicating a spill to disk.

Fluxo de linha versus estimativa de linhas

Compare o plano anterior com o plano real gerado com a execução intercalada habilitada:Contrast the prior plan with the actual plan generated with interleaved execution enabled:

Plano intercalado

  1. Observe que a verificação de tabela de MSTVF agora reflete uma estimativa de cardinalidade precisa.Notice that the MSTVF table scan now reflects an accurate cardinality estimate. Além disso, observe a reordenação dessa verificação de tabela e das outras operações.Also notice the re-ordering of this table scan and the other operations.
  2. E em relação aos algoritmos de junção, mudamos de uma operação de loops aninhados para uma operação de correspondência de hash, que é mais ideal devido ao grande número de linhas envolvidas.And regarding join algorithms, we have switched from a Nested Loop operation to a Hash Match operation instead, which is more optimal given the large number of rows involved.
  3. Além disso, observe que não há mais avisos de despejo necessários, pois estamos concedendo mais memória com base na contagem verdadeira de linhas que passam da verificação da tabela de MSTVF.Also notice that we no longer have spill-warnings, as we're granting more memory based on the true row count flowing from the MSTVF table scan.

Instruções qualificadas para execução intercaladaInterleaved execution eligible statements

A MSTVF que referencia instruções em execução intercalada deve estar somente leitura e não fazer parte de uma operação de modificação de dados.MSTVF referencing statements in interleaved execution must currently be read-only and not part of a data modification operation. Além disso, MSTVFs não serão qualificados para execução intercalada se não usarem constantes em tempo de execução.Also, MSTVFs are not eligible for interleaved execution if they do not use runtime constants.

Benefícios de execução intercaladaInterleaved execution benefits

Em geral, quanto maior a distorção entre o número de linhas real e estimado, juntamente com o número de operações do plano de downstream, maior o impacto no desempenho.In general, the higher the skew between the estimated vs. actual number of rows, coupled with the number of downstream plan operations, the greater the performance impact. Em geral, a execução intercalada beneficia consultas em que:In general, interleaved execution benefits queries where:

  1. Há uma grande distorção entre o número de linhas estimado e real para o conjunto de resultados intermediário (neste caso, o MSTVF).There is a large skew between the estimated vs. actual number of rows for the intermediate result set (in this case, the MSTVF).
  2. E a consulta geral é sensível a uma alteração no tamanho do resultado intermediário.And the overall query is sensitive to a change in the size of the intermediate result. Isso geralmente acontece quando há uma árvore complexa acima dessa subárvore no plano de consulta.This typically happens when there is a complex tree above that subtree in the query plan. Um simples SELECT * de uma MSTVF não se beneficiará da execução intercalada.A simple SELECT * from an MSTVF will not benefit from interleaved execution.

Sobrecarga da execução intercaladaInterleaved execution overhead

A sobrecarga deve ser de mínima a nenhuma.The overhead should be minimal-to-none. As MSTVFs já estavam sendo materializadas antes da introdução da execução intercalada, no entanto a diferença é que, agora estamos permitindo a otimização adiada e, portanto, aproveitando a estimativa de cardinalidade do conjunto de linhas materializadas.MSTVFs were already being materialized prior to the introduction of interleaved execution, however the difference is that now we're now allowing deferred optimization and are then leveraging the cardinality estimate of the materialized row set. Assim como acontece com qualquer plano que afeta as alterações, alguns planos podem ser alterados de modo que com uma cardinalidade melhor da subárvore podemos obter um plano pior para a consulta geral.As with any plan affecting changes, some plans could change such that with better cardinality for the subtree we get a worse plan for the query overall. A mitigação pode incluir a reversão do nível de compatibilidade ou o uso do Repositório de Consultas para forçar a versão não retornada do plano.Mitigation can include reverting the compatibility level or using Query Store to force the non-regressed version of the plan.

Execução intercalada e execuções consecutivasInterleaved execution and consecutive executions

Depois que um plano de execução intercalada é armazenado em cache, o plano com as estimativas revisadas na primeira execução é usado para as próximas consecutivas sem instanciar novamente a execução intercalada.Once an interleaved execution plan is cached, the plan with the revised estimates on the first execution is used for consecutive executions without re-instantiating interleaved execution.

Controlando a atividade de execução intercaladaTracking interleaved execution activity

Você pode ver os atributos de uso no plano de execução de consulta real:You can see usage attributes in the actual query execution plan:

Atributo de Plano de execuçãoExecution Plan attribute DescriçãoDescription
ContainsInterleavedExecutionCandidatesContainsInterleavedExecutionCandidates Aplica-se ao nó QueryPlan.Applies to the QueryPlan node. Quando é true, significa que o plano contém candidatos a execução intercalada.When true, means the plan contains interleaved execution candidates.
IsInterleavedExecutedIsInterleavedExecuted Atributo do elemento RuntimeInformation em RelOp para o nó TVF.Attribute of the RuntimeInformation element under the RelOp for the TVF node. Quando true, significa que a operação foi materializada como parte de uma operação de execução intercalada.When true, means the operation was materialized as part of an interleaved execution operation.

Você também pode controlar as ocorrências de execução intercalada por meio dos xEvents a seguir:You can also track interleaved execution occurrences via the following xEvents:

xEventxEvent DescriçãoDescription
interleaved_exec_statusinterleaved_exec_status Esse evento é disparado quando a execução intercalada está ocorrendo.This event fires when interleaved execution is occurring.
interleaved_exec_stats_updateinterleaved_exec_stats_update Esse evento descreve as estimativas de cardinalidade atualizadas por execução intercalada.This event describes the cardinality estimates updated by interleaved execution.
Interleaved_exec_disabled_reasonInterleaved_exec_disabled_reason Esse evento é disparado quando uma consulta com uma possível candidata para execução intercalada, na verdade, não obtém a execução intercalada.This event fires when a query with a possible candidate for interleaved execution does not actually get interleaved execution.

Uma consulta deve ser executada para permitir que a execução intercalada revise as estimativas de cardinalidade de MSTVF.A query must be executed in order to allow interleaved execution to revise MSTVF cardinality estimates. No entanto, o plano de execução estimada ainda mostra quando há candidatas para execução intercalada por meio do atributo ContainsInterleavedExecutionCandidates do plano de execução.However, the estimated execution plan still shows when there are interleaved execution candidates via the ContainsInterleavedExecutionCandidates showplan attribute.

Armazenando em cache de execução intercaladaInterleaved execution caching

Se um plano é limpo ou removido do cache, após a execução da consulta há uma nova compilação que usa a execução intercalada.If a plan is cleared or evicted from cache, upon query execution there is a fresh compilation that uses interleaved execution. Uma instrução que usar OPTION (RECOMPILE) criará um plano usando a execução intercalada e não a armazenará em cache.A statement using OPTION (RECOMPILE) will create a new plan using interleaved execution and not cache it.

Interoperabilidade entre execução intercalada e repositório de consultasInterleaved execution and query store interoperability

Os planos que usam execução intercalada podem ser forçados.Plans using interleaved execution can be forced. O plano é a versão que tem as estimativas de cardinalidade corrigidas com base na execução inicial.The plan is the version that has corrected cardinality estimates based on initial execution.

Desabilitar execução intercalada sem alterar o nível de compatibilidadeDisabling interleaved execution without changing the compatibility level

A execução intercalada pode ser desabilitada no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados como 140 e superior.Interleaved execution can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. Para desabilitar a execução intercalada para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:To disable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = OFF;

Quando habilitada, essa configuração aparecerá como habilitada em sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. Para reabilitar a execução intercalada para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:To re-enable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET INTERLEAVED_EXECUTION_TVF = ON;

Também é possível desabilitar a execução intercalada para uma consulta específica designando DISABLE_INTERLEAVED_EXECUTION_TVF como uma dica de consulta USE HINT.You can also disable interleaved execution for a specific query by designating DISABLE_INTERLEAVED_EXECUTION_TVF as a USE HINT query hint. Por exemplo:For example:

SELECT [fo].[Order Key], [fo].[Quantity], [foo].[OutlierEventQuantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Fact].[WhatIfOutlierEventQuantity]('Mild Recession',
                            '1-01-2013',
                            '10-15-2014') AS [foo] ON [fo].[Order Key] = [foo].[Order Key]
                            AND [fo].[City Key] = [foo].[City Key]
                            AND [fo].[Customer Key] = [foo].[Customer Key]
                            AND [fo].[Stock Item Key] = [foo].[Stock Item Key]
                            AND [fo].[Order Date Key] = [foo].[Order Date Key]
                            AND [fo].[Picked Date Key] = [foo].[Picked Date Key]
                            AND [fo].[Salesperson Key] = [foo].[Salesperson Key]
                            AND [fo].[Picker Key] = [foo].[Picker Key]
OPTION (USE HINT('DISABLE_INTERLEAVED_EXECUTION_TVF'));

Uma dica de consulta USE HINT tem precedência sobre uma configuração de escopo do banco de dados ou uma configuração de sinalizador de rastreamento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Compilação adiada de variável da tabelaTable variable deferred compilation

Aplica-se a: SQL ServerSQL Server (começando pelo SQL Server 2019 (15.x)SQL Server 2019 (15.x)) e Banco de dados SQL do AzureAzure SQL Database (versão prévia pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Banco de dados SQL do AzureAzure SQL Database (public preview)

A compilação adiada de variável da tabela melhora a qualidade do plano e o desempenho geral para consultas que fazem referência a variáveis de tabela.Table variable deferred compilation improves plan quality and overall performance for queries that reference table variables. Durante a otimização e compilação inicial, esse recurso propaga estimativas de cardinalidade com base nas contagens reais de linha de variável de tabela.During optimization and initial compilation, this feature propagates cardinality estimates that are based on actual table variable row counts. Essas informações de contagem de linha precisas otimizam as operações de plano de downstream.This accurate row count information optimizes downstream plan operations.

A compilação adiada de variável de tabela adia a compilação de uma instrução que faz referência a uma variável de tabela até a primeira execução real da instrução.Table variable deferred compilation defers compilation of a statement that references a table variable until the first actual run of the statement. Esse comportamento de compilação adiada é igual ao das tabelas temporárias.This deferred compilation behavior is the same as that of temporary tables. Essa alteração resulta no uso de cardinalidade real em vez da estimativa original de uma linha.This change results in the use of actual cardinality instead of the original one-row guess.

Você pode ativar a versão prévia pública da compilação adiada de variável de tabela no Banco de Dados SQL do Azure.You can enable the public preview of table variable deferred compilation in Azure SQL Database. Para isso, habilite o nível de compatibilidade 150 do banco de dados ao qual você está conectado ao executar a consulta.To do that, enable compatibility level 150 for the database you're connected to when you run the query.

Para saber mais, veja Compilação adiada de variável da tabela.For more information, see Table variable deferred compilation.

Embutimento de UDF escalarScalar UDF inlining

Aplica-se a: SQL ServerSQL Server (começando pelo SQL Server 2019 (15.x)SQL Server 2019 (15.x)) e Banco de dados SQL do AzureAzure SQL Database (versão prévia pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Banco de dados SQL do AzureAzure SQL Database (public preview)

O inlining da UDF escalar transforma automaticamente UDFs escalares em expressões relacionais.Scalar UDF inlining automatically transforms scalar UDFs into relational expressions. Ele as incorpora à chamada da consulta SQL.It embeds them in the calling SQL query. Essa transformação melhora o desempenho de cargas de trabalho que aproveitam as UDFs escalares.This transformation improves the performance of workloads that take advantage of scalar UDFs. O inlining da UDF escalar facilita a otimização baseada em custo de operações das UDFs.Scalar UDF inlining facilitates cost-based optimization of operations inside UDFs. Os resultados são eficientes, orientados para conjunto e paralelos, em vez de planos de execução ineficientes, iterativos e seriais.The results are efficient, set-oriented, and parallel instead of inefficient, iterative, serial execution plans. Esse recurso é habilitado por padrão no nível de compatibilidade do banco de dados 150.This feature is enabled by default under database compatibility level 150.

Para saber mais, confira Scalar UDF Inlining (Embutimento de UDF escalar).For more information, see Scalar UDF inlining.

Processamento de consulta aproximadaApproximate query processing

Aplica-se a: SQL ServerSQL Server (começando pelo SQL Server 2019 (15.x)SQL Server 2019 (15.x)) e Banco de dados SQL do AzureAzure SQL Database (versão prévia pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Banco de dados SQL do AzureAzure SQL Database (public preview)

O processamento de consulta aproximado é uma nova família de recursos.Approximate query processing is a new feature family. Ele agrega grandes conjuntos de dados nos quais a capacidade de resposta é mais importante do que a precisão absoluta.It aggregates across large datasets where responsiveness is more critical than absolute precision. Um exemplo é o cálculo de COUNT(DISTINCT()) entre 10 bilhões de linhas para a exibição em um painel.An example is calculating a COUNT(DISTINCT()) across 10 billion rows, for display on a dashboard. Nesse caso, o que é importante não é a precisão absoluta, mas a capacidade de resposta que é essencial.In this case, absolute precision isn't important, but responsiveness is critical. A nova função de agregação APPROX_COUNT_DISTINCT retorna o número aproximado de valores não nulos exclusivos em um grupo.The new APPROX_COUNT_DISTINCT aggregate function returns the approximate number of unique non-null values in a group.

Para saber mais, confira APPROX_COUNT_DISTINCT (Transact-SQL).For more information, see APPROX_COUNT_DISTINCT (Transact-SQL).

Modo de Lote no RowstoreBatch mode on rowstore

Aplica-se a: SQL ServerSQL Server (começando pelo SQL Server 2019 (15.x)SQL Server 2019 (15.x)) e Banco de dados SQL do AzureAzure SQL Database (versão prévia pública)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)), Banco de dados SQL do AzureAzure SQL Database (public preview)

O modo de lote em rowstore permite que a execução em modo de lote para cargas de trabalho analíticas sem a necessidade de índices columnstore.Batch mode on rowstore enables batch mode execution for analytic workloads without requiring columnstore indexes. Esse recurso dá suporte a filtros de bitmap e à execução do modo de lote para em disco heaps e índices de árvore B.This feature supports batch mode execution and bitmap filters for on-disk heaps and B-tree indexes. O modo de lote em rowstore habilita o suporte para todos os operadores habilitados para o modo de lote existente.Batch mode on rowstore enables support for all existing batch mode-enabled operators.

Plano de fundoBackground

SQL Server 2012 (11.x)SQL Server 2012 (11.x) introduziu um novo recurso para acelerar as cargas de trabalho analíticas: os índices columnstore.introduced a new feature to accelerate analytical workloads: columnstore indexes. Expandimos os casos de uso e melhoramos o desempenho de índices columnstore em todas as versões subsequentes.We expanded the use cases and improved the performance of columnstore indexes in each subsequent release. Até agora, apresentamos e documentamos todos esses recursos como um único recurso.Until now, we surfaced and documented all these capabilities as a single feature. Você pode criar índices columnstore em suas tabelas.You create columnstore indexes on your tables. E sua carga de trabalho analítica será bem mais rápida.And your analytical workload goes faster. No entanto, há dois conjuntos de tecnologias relacionados mas distintos:However, there are two related but distinct sets of technologies:

  • Com os índices columnstore, as consultas analíticas acessam apenas os dados que elas precisam das colunas.With columnstore indexes, analytical queries access only the data in the columns they need. A compactação de página no formato columnstore também é mais eficiente do que a dos índices rowstore tradicionais.Page compression in the columnstore format is also more effective than compression in traditional rowstore indexes.
  • Com o processamento em modo de lote, os operadores de consulta processam dados com mais eficiência.With batch mode processing, query operators process data more efficiently. Eles funcionam em um lote de linhas em vez de uma linha por vez.They work on a batch of rows instead of one row at a time. Diversos outros aprimoramentos de escalabilidade estão ligados ao processamento do modo de lote.A number of other scalability improvements are tied to batch mode processing. Confira mais informações sobre o modo de lote em Modos de execução.For more information on batch mode, see Execution modes.

Os dois conjuntos de recursos funcionam juntos para melhorar a utilização de CPU e E/S (entrada/saída):The two sets of features work together to improve input/output (I/O) and CPU use:

  • ao usar índices columnstore, mais dos seus dados se encaixam na memória.By using columnstore indexes, more of your data fits in memory. Isso reduz a necessidade de E/S.That reduces the need for I/O.
  • O processamento de modo de lote usa a CPU com mais eficiência.Batch mode processing uses CPU more efficiently.

As duas tecnologias tiram proveito um do outro sempre que possível.The two technologies take advantage of each other whenever possible. Por exemplo, agregações de modo de lote podem ser avaliadas como parte de uma verificação de índice columnstore.For example, batch mode aggregates can be evaluated as part of a columnstore index scan. Também processamos os dados de columnstore compactados usando a codificação de tamanho de execução com muito mais eficiência com junções e agregações de modos de lotes.We also process columnstore data that's compressed by using run-length encoding much more efficiently with batch mode joins and batch mode aggregates.

Os dois recursos podem ser utilizados de forma independente:The two features are independently usable:

  • Você obtém planos de modo de linha que usam índices columnstore.You get row mode plans that use columnstore indexes.
  • Você obtém planos de modo de linha que usam somente índices rowstore.You get batch mode plans that use only rowstore indexes.

Geralmente é possível obter os melhores resultados ao usar os dois recursos juntos.You usually get the best results when you use the two features together. Portanto, até agora, o otimizador de consulta do SQL Server considerou o processamento de modo de lote só para consultas que envolvem pelo menos uma tabela com um índice columnstore.So until now, the SQL Server query optimizer considered batch mode processing only for queries that involve at least one table with a columnstore index.

Os índices columnstore não são uma boa opção para alguns aplicativos.Columnstore indexes aren't a good option for some applications. Pode ser que o aplicativo use outro recurso que não tenha suporte com índices columnstore.An application might use some other feature that isn't supported with columnstore indexes. Por exemplo, as modificações no local não são compatíveis com a compactação de columnstore.For example, in-place modifications aren't compatible with columnstore compression. Portanto, os gatilhos não têm suporte em tabelas com índices columnstore clusterizados.So triggers aren't supported on tables with clustered columnstore indexes. E mais importante, índices columnstore adicionam uma sobrecarga para as instruções DELETE e UPDATE.More important, columnstore indexes add overhead for DELETE and UPDATE statements.

Para algumas cargas de trabalho transacionais analíticas híbridas, a sobrecarga relacionada a aspectos transacionais da carga de trabalho superam os benefícios dos índices columnstore.For some hybrid transactional-analytical workloads, the overhead on a workload's transactional aspects outweighs the benefits of columnstore indexes. Tais cenários podem melhorar o uso de CPU do processamento de modo de lote.Such scenarios can improve CPU use from batch mode processing alone. É por isso que o modo de lote no recurso de rowstore considera o modo de lote para todas as consultas.That's why the batch mode on rowstore feature considers batch mode for all queries. Não importa quais índices estão envolvidos.It doesn't matter which indexes are involved.

Cargas de trabalho que podem se beneficiar com o modo de lote no rowstoreWorkloads that might benefit from batch mode on rowstore

As seguintes cargas de trabalho podem se beneficiar do modo de lote no rowstore:The following workloads might benefit from batch mode on rowstore:

  • Uma parte significativa da carga de trabalho consiste em consultas analíticas.A significant part of the workload consists of analytical queries. Geralmente essas consultas têm operadores como junções ou agregações que processam centenas de milhares de linhas ou mais.Usually, these queries have operators like joins or aggregates that process hundreds of thousands of rows or more.
  • A carga de trabalho está associada à CPU.The workload is CPU bound. Se o gargalo for em E/S, ainda assim é recomendável que você considere um índice columnstore, se possível.If the bottleneck is I/O, we still recommend that you consider a columnstore index, if possible.
  • Criar um índice columnstore adiciona muita sobrecarga à parte transacional da carga de trabalho.Creating a columnstore index adds too much overhead to the transactional part of your workload. Ou a criação de um índice columnstore não é viável porque seu aplicativo depende de um recurso que ainda não tem suporte com índices columnstore.Or, creating a columnstore index isn't feasible because your application depends on a feature that's not yet supported with columnstore indexes.

Observação

O modo de lote em rowstore só pode ajudar a reduzir o consumo de CPU.Batch mode on rowstore helps only by reducing CPU consumption. Se o gargalo for relacionado à E/S e os dados ainda não estiverem armazenados em cache (cache "frio"), o modo de lote em rowstore não melhorará o tempo decorrido.If your bottleneck is I/O related, and data isn't already cached ("cold" cache), batch mode on rowstore won't improve elapsed time. Da mesma forma, se não houver memória suficiente no computador para armazenar em cache todos os dados, será improvável que ocorra uma melhoria de desempenho.Similarly, if there isn't enough memory on the machine to cache all the data, a performance improvement is unlikely.

O que muda com o modo de lote no rowstore?What changes with batch mode on rowstore?

Além de mover para o nível de compatibilidade 150, não é necessário mudar nada no seu lado para habilitar o modo de lote no rowstore para cargas de trabalho candidatas.Other than moving to compatibility level 150, you don't have to change anything on your side to enable batch mode on rowstore for candidate workloads.

Mesmo se uma consulta não envolver nenhuma tabela com um índice columnstore, o processador de consultas já usa heurística para decidir se deve considerar o modo de lote.Even if a query doesn't involve any table with a columnstore index, the query processor now uses heuristics to decide whether to consider batch mode. A heurística consiste destas verificações:The heuristics consist of these checks:

  1. Uma verificação inicial dos tamanhos de tabela, operadores usados e cardinalidades estimadas na consulta de entrada.An initial check of table sizes, operators used, and estimated cardinalities in the input query.
  2. Pontos de verificação adicionais à medida que o otimizador descobre planos novos e mais baratos para a consulta.Additional checkpoints, as the optimizer discovers new, cheaper plans for the query. Se esses planos alternativos não usarem o modo de lote de forma significativa, o otimizador parará de explorar as alternativas de modo de lote.If these alternative plans don't make significant use of batch mode, the optimizer stops exploring batch mode alternatives.

Se o modo de lote em rowstore for usado, você verá o modo de execução real como o modo de lote no plano de consulta.If batch mode on rowstore is used, you see the actual run mode as batch mode in the query plan. O operador de verificação usa o modo de lote em heaps em discos e índices de árvore B.The scan operator uses batch mode for on-disk heaps and B-tree indexes. Essa verificação do modo de lote pode avaliar os filtros de bitmap do modo de lote.This batch mode scan can evaluate batch mode bitmap filters. Você também poderá ver outros operadores de modo de lote no plano.You might also see other batch mode operators in the plan. Os exemplos são junções hash, agregações baseadas em hash, classificações, agregações de janela, filtros, concatenações e operadores escalares de computação.Examples are hash joins, hash-based aggregates, sorts, window aggregates, filters, concatenation, and compute scalar operators.

RemarksRemarks

Planos de consulta nem sempre usam o modo de lote.Query plans don't always use batch mode. O otimizador de consulta pode decidir que o modo de lote não é útil para a consulta.The Query Optimizer might decide that batch mode isn't beneficial for the query.

O espaço de pesquisa do otimizador de consulta está sendo alterado.The Query Optimizer's search space is changing. Portanto, se você receber um plano de modo de linha, poderia não ser o mesmo que o plano que você obtém em um nível de compatibilidade mais baixo.So if you get a row mode plan, it might not be the same as the plan you get in a lower compatibility level. E se você receber um plano de modo de lote, poderia não ser o mesmo que o plano que você obtém com um índice columnstore.And if you get a batch mode plan, it might not be the same as the plan you get with a columnstore index.

Os planos também podem alterar as consultas que combinam os índices columnstore e rowstore devido à verificação de rowstore do novo modo de lote.Plans might also change for queries that mix columnstore and rowstore indexes because of the new batch mode rowstore scan.

Existem limitações atuais para o novo modo de lote na verificação de rowstore:There are current limitations for the new batch mode on rowstore scan:

  • Ele não será iniciado para tabelas OLTP na memória nem para índices diferentes de heaps em disco e árvores B.It won't kick in for in-memory OLTP tables or for any index other than on-disk heaps and B-trees.
  • Ele também não será iniciado ao buscar ou filtrar uma coluna LOB (de objeto grande).It also won't kick in if a large object (LOB) column is fetched or filtered. Essa limitação inclui conjuntos de colunas esparsas e colunas XML.This limitation includes sparse column sets and XML columns.

Há consultas em que o modo de lote não é usado para pares em índices columnstore.There are queries that batch mode isn't used for even with columnstore indexes. Os exemplos são consultas que envolvem cursores.Examples are queries that involve cursors. Essas mesmas exclusões também se estendem ao modo de lote em rowstore.These same exclusions also extend to batch mode on rowstore.

Configurar o modo de lote no rowstoreConfigure batch mode on rowstore

A configuração no escopo do banco de dados BATCH_MODE_ON_ROWSTORE é ativada por padrão.The BATCH_MODE_ON_ROWSTORE database scoped configuration is on by default. Ela desabilita o modo de lote em rowstore sem exigir uma alteração no nível de compatibilidade do banco de dados:It disables batch mode on rowstore without requiring a change in database compatibility level:

-- Disabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = OFF;

-- Enabling batch mode on rowstore
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_ON_ROWSTORE = ON;

você pode desabilitar o modo de lote em rowstore por meio de configuração do escopo do banco de dados.You can disable batch mode on rowstore via database scoped configuration. Mas você ainda pode substituir a configuração no nível da consulta usando a dica de consulta ALLOW_BATCH_MODE.But you can still override the setting at the query level by using the ALLOW_BATCH_MODE query hint. O exemplo a seguir habilita o modo de lote no rowstore, mesmo com o recurso desabilitado por meio de configuração com escopo do banco de dados:The following example enables batch mode on rowstore even with the feature disabled via database scoped configuration:

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('ALLOW_BATCH_MODE'));

Também é possível desabilitar o modo de lote em rowstore para uma consulta específica usando a dica de consulta DISALLOW_BATCH_MODE.You can also disable batch mode on rowstore for a specific query by using the DISALLOW_BATCH_MODE query hint. Consulte o seguinte exemplo:See the following example:

SELECT [Tax Rate], [Lineage Key], [Salesperson Key], SUM(Quantity) AS SUM_QTY, SUM([Unit Price]) AS SUM_BASE_PRICE, COUNT(*) AS COUNT_ORDER
FROM Fact.OrderHistoryExtended
WHERE [Order Date Key]<=DATEADD(dd, -73, '2015-11-13')
GROUP BY [Tax Rate], [Lineage Key], [Salesperson Key]
ORDER BY [Tax Rate], [Lineage Key], [Salesperson Key]
OPTION(RECOMPILE, USE HINT('DISALLOW_BATCH_MODE'));

Confira tambémSee also

Central de desempenho do Mecanismo de Banco de Dados do SQL Server e do Banco de Dados SQL do Azure Performance Center for SQL Server Database Engine and Azure SQL Database
Guia de arquitetura de processamento de consultas Query processing architecture guide
Referência de operadores físicos e lógicos do plano de execução Showplan logical and physical operators reference
Junções Joins
Demonstrar o processamento de consulta adaptável Demonstrating Adaptive Query Processing
Demonstrar o processamento de consulta inteligenteDemonstrating Intelligent Query Processing