Procesamiento de consultas inteligente en bases de datos SQLIntelligent query processing in SQL databases

SE APLICA A: síSQL Server síAzure SQL Database noAzure SQL Data Warehouse noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

La familia de características de procesamiento de consultas inteligentes incluye características con un gran impacto que mejoran el rendimiento de las cargas de trabajo existentes con un esfuerzo de implementación mínimo para adoptar.The intelligent query processing (QP) feature family includes features with broad impact that improve the performance of existing workloads with minimal implementation effort to adopt.

Procesamiento de consultas inteligentes

Puede hacer que las cargas de trabajo sean aptas automáticamente para el procesamiento de consultas inteligentes si habilita el nivel de compatibilidad de base de datos pertinente en la base de datos.You can make workloads automatically eligible for intelligent query processing by enabling the applicable database compatibility level for the database. Puede establecerlo con Transact-SQL.You can set this using Transact-SQL. Por ejemplo:For example:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

En la siguiente tabla se detallan todas las características de procesamiento de consultas inteligentes, así como cualquier requisito que tengan en cuanto a nivel de compatibilidad de base de datos.The following table details all intelligent query processing features, along with any requirement they have for database compatibility level.

Característica de procesamiento de consultas inteligentesIQP Feature Compatible con Azure SQL DatabaseSupported in Azure SQL Database Compatible con SQL ServerSupported in SQL Server DescripciónDescription
Combinaciones adaptables (modo por lotes)Adaptive Joins (Batch Mode) Sí, en el nivel de compatibilidad 140Yes, under compatibility level 140 Sí, a partir de SQL Server 2017 en el nivel de compatibilidad 140Yes, starting in SQL Server 2017 under compatibility level 140 Las combinaciones adaptables seleccionan dinámicamente un tipo de combinación en tiempo de ejecución según las filas de entrada reales.Adaptive joins dynamically select a join type during runtime based on actual input rows.
Count Distinct aproximadaApproximate Count Distinct Sí, versión preliminar públicaYes, public preview Sí, a partir de SQL Server 2019 CTP 2.0, versión preliminar públicaYes, starting in SQL Server 2019 CTP 2.0, public preview Proporcione un valor de COUNT DISTINCT aproximado en escenarios de macrodatos, con la ventaja de un alto rendimiento y una baja superficie de memoria.Provide approximate COUNT DISTINCT for big data scenarios with the benefit of high performance and a low memory footprint.
Modo por lotes en el almacén de filasBatch Mode on Rowstore Sí, en el nivel de compatibilidad 150, versión preliminar públicaYes, under compatibility level 150, public preview Sí, a partir de SQL Server 2019 CTP 2.0 en el nivel de compatibilidad 150, versión preliminar públicaYes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview Proporcione el modo por lotes en las cargas de trabajo de almacenamiento de datos relacionales enlazadas a la CPU, sin necesidad de índices de almacén de columnas.Provide batch mode for CPU-bound relational DW workloads without requiring columnstore indexes.
Ejecución intercaladaInterleaved Execution Sí, en el nivel de compatibilidad 140Yes, under compatibility level 140 Sí, a partir de SQL Server 2017 en el nivel de compatibilidad 140Yes, starting in SQL Server 2017 under compatibility level 140 Use la cardinalidad real de la función con valores de tabla y múltiples instrucciones detectada en la primera compilación en lugar de una estimación fija.Use the actual cardinality of the multi-statement table valued function encountered on first compilation instead of a fixed guess.
Comentarios de concesión de memoria (modo por lotes)Memory Grant Feedback (Batch Mode) Sí, en el nivel de compatibilidad 140Yes, under compatibility level 140 Sí, a partir de SQL Server 2017 en el nivel de compatibilidad 140Yes, starting in SQL Server 2017 under compatibility level 140 Si una consulta de modo por lotes tiene operaciones que escriben en disco, agregue más memoria para las ejecuciones consecutivas.If a batch mode query has operations that spill to disk, add more memory for consecutive executions. Si una consulta desperdicia más del 50 % de memoria que tiene asignada, reduzca el lado de concesión de memoria en las ejecuciones consecutivas.If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
Comentarios de concesión de memoria (modo de fila)Memory Grant Feedback (Row Mode) Sí, en el nivel de compatibilidad 150, versión preliminar públicaYes, under compatibility level 150, public preview Sí, a partir de SQL Server 2019 CTP 2.0 en el nivel de compatibilidad 150, versión preliminar públicaYes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview Si una consulta de modo de fila tiene operaciones que escriben en disco, agregue más memoria para las ejecuciones consecutivas.If a row mode query has operations that spill to disk, add more memory for consecutive executions. Si una consulta desperdicia más del 50 % de memoria que tiene asignada, reduzca el lado de concesión de memoria en las ejecuciones consecutivas.If a query wastes > 50% of the memory allocated to it, reduce the memory grant side for consecutive executions.
Inserción de UDF escalarScalar UDF Inlining NoNo Sí, a partir de SQL Server 2019 CTP 2.1 en el nivel de compatibilidad 150, versión preliminar públicaYes, starting in SQL Server 2019 CTP 2.1 under compatibility level 150, public preview Los UDF escalares se transforman en expresiones relacionales equivalentes que se "insertan" en la consulta que realiza la llamada, lo que a menudo supone una notable mejora del rendimiento.Scalar UDFs are transformed into equivalent relational expressions that are "inlined" into the calling query, often resulting in significant performance gains.
Compilación diferida de variables de tablaTable Variable Deferred Compilation Sí, en el nivel de compatibilidad 150, versión preliminar públicaYes, under compatibility level 150, public preview Sí, a partir de SQL Server 2019 CTP 2.0 en el nivel de compatibilidad 150, versión preliminar públicaYes, starting in SQL Server 2019 CTP 2.0 under compatibility level 150, public preview Use la cardinalidad real de la variable de tabla detectada en la primera compilación en lugar de una estimación fija.Use the actual cardinality of the table variable encountered on first compilation instead of a fixed guess.

Combinaciones adaptables de modo de proceso por lotesBatch mode adaptive joins

Con esta característica, su plan puede cambiar de forma dinámica a una mejor estrategia de combinación durante la ejecución mediante un único plan almacenado en caché.With this feature, your plan can dynamically switch to a better join strategy during execution by using a single cached plan.

La característica de combinaciones adaptables del modo por lotes permite elegir un método Combinación hash o combinación de bucles anidados que se aplace hasta después de que se haya examinado la primera entrada.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. El operador de combinaciones adaptables define un umbral que se usa para decidir cuándo cambiar a un plan de bucles anidados.The Adaptive Join operator defines a threshold that is used to decide when to switch to a Nested Loops plan. El plan, por tanto, puede cambiar de forma dinámica para una mejor estrategia de combinación durante la ejecución.Your plan can therefore dynamically switch to a better join strategy during execution. Funcionamiento:Here's how it works:

  • Si el recuento de filas de la entrada de combinación de compilación es lo suficientemente pequeño como para que una combinación de bucles anidados sea una opción más óptima que una combinación hash, el plan cambia a un algoritmo de bucles anidados.If the row count of the build join input is small enough that a nested loop join would be more optimal than a Hash Join, your plan switches to a Nested Loops algorithm.
  • Si la entrada de combinación de compilación supera un umbral de recuento de filas determinado, no se produce ningún cambio y el plan continúa con una combinación hash.If the build join input exceeds a specific row count threshold, no switch occurs and your plan continues with a Hash Join.

La siguiente consulta se usa para mostrar un ejemplo de combinación adaptable:The following query is used to illustrate an Adaptive Join example:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 360;

La consulta devuelve 336 filas.The query returns 336 rows. Al habilitar las estadísticas de consultas activas se ve el siguiente plan:Enabling Live Query Statistics, we see the following plan:

La consulta da lugar a 336 filas

En el plan se ve lo siguiente:In the plan, we see the following:

  1. Hay una exploración de índice de almacén de columnas que se usa para proporcionar filas para la fase de compilación de combinación hash.We have a columnstore index scan used to provide rows for the hash join build phase.
  2. Hay un nuevo operador de combinación adaptable.We have the new Adaptive Join operator. Este operador define un umbral que se usa para decidir cuándo cambiar a un plan de bucle anidado.This operator defines a threshold that is used to decide when to switch to a Nested Loops plan. En el ejemplo, el umbral es 78 filas.For our example, the threshold is 78 rows. Todo lo que tenga > = 78 filas usará una combinación hash.Anything with >= 78 rows will use a Hash Join. Si es inferior al umbral, se usará una combinación de bucle anidado.If less than the threshold, a Nested Loops Join will be used.
  3. Puesto que se devuelven 336 filas, se supera el umbral y la segunda rama representa la fase de sondeo de una operación de combinación hash estándar.Since we return 336 rows, we are exceeding the threshold and so the second branch represents the probe phase of a standard Hash Join operation. Observe que las estadísticas de consultas dinámicas muestran las filas que pasan por los operadores, en este caso "672 de 672".Notice that Live Query Statistics shows rows flowing through the operators - in this case "672 of 672".
  4. La última rama es la búsqueda en índice clúster que usa la combinación de bucle anidado si no se ha superado el umbral.And the last branch is our Clustered Index Seek for use by the nested loop join had the threshold not been exceeded. Observe que se ve "0 de 336" filas mostradas (la rama no se usa).Notice that we see "0 of 336" rows displayed (the branch is unused).

Le escuchamos: Si encuentra algo obsoleto o incorrecto en este artículo, como un paso o un ejemplo de código, indíquenoslo.We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. Puede hacer clic en el botón Esta página situado en la sección Comentarios en la parte inferior de esta página.You can click the This page button in the Feedback section at the bottom of this page. Leemos todos los elementos de comentarios sobre SQL, normalmente el día siguiente.We read every item of feedback about SQL, typically the next day. Gracias.Thanks.

Ahora vamos a comparar el plan con la misma consulta, pero esta vez para un valor Cantidad que solo tiene una fila en la tabla:Now contrast the plan with the same query, but this time for a Quantity value that only has one row in the table:

SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = 361;

La consulta devuelve una fila.The query returns one row. Al habilitar las estadísticas de consultas activas se ve el siguiente plan:Enabling Live Query Statistics we see the following plan:

La consulta da lugar a una fila

En el plan se ve lo siguiente:In the plan, we see the following:

  • Con una fila devuelta, ahora pasan filas por la búsqueda en índice clúster.With one row returned, the Clustered Index Seek now has rows flowing through it.
  • Y puesto que no se ha continuado con la fase de compilación de combinación hash, no pasan filas por la segunda rama.And since the Hash Join build phase did not continue, there are no rows flowing through the second branch.

Ventajas de la combinación adaptableAdaptive Join benefits

La cargas de trabajo con oscilaciones frecuentes entre análisis de entrada de combinación pequeños y grandes son las que más se benefician de esta característica.Workloads with frequent oscillations between small and large join input scans will benefit most from this feature.

Sobrecarga de la combinación adaptableAdaptive Join overhead

Las combinaciones adaptables tienen unos requisitos de memoria superiores a un plan equivalente de combinación de bucle anidado de índice.Adaptive joins introduce a higher memory requirement than an indexed Nested Loops Join equivalent plan. La memoria adicional se solicita como si el bucle anidado fuera una combinación hash.The additional memory is requested as if the Nested Loops was a Hash Join. También hay sobrecarga para la fase de compilación como una operación de detención e inicio frente a una combinación equivalente de transmisión de bucle anidado.There is also overhead for the build phase as a stop-and-go operation versus a Nested Loops streaming equivalent join. Ese costo adicional va acompañado de flexibilidad en escenarios donde los recuentos de filas pueden fluctuar en la entrada de compilación.With that additional cost comes flexibility for scenarios where row counts may fluctuate in the build input.

Almacenamiento en caché y reutilización de combinación adaptableAdaptive Join caching and re-use

Las combinaciones adaptables de modo de proceso por lotes funcionan para la ejecución inicial de una instrucción y, una vez compiladas, las ejecuciones consecutivas seguirán siendo adaptables según el umbral de combinación adaptable compilado y las filas de runtime que pasan a través de la fase de compilación de la entrada externa.Batch mode Adaptive Joins work for the initial execution of a statement, and once compiled, consecutive executions will remain adaptive based on the compiled Adaptive Join threshold and the runtime rows flowing through the build phase of the outer input.

Seguimiento de la actividad de combinación adaptableTracking Adaptive Join activity

El operador de combinación adaptable tiene los siguientes atributos de operador de plan:The Adaptive Join operator has the following plan operator attributes:

Atributo de planPlan attribute DescripciónDescription
AdaptiveThresholdRowsAdaptiveThresholdRows Muestra el uso de umbral para cambiar de una combinación hash a una combinación de bucle anidado.Shows the threshold use to switch from a hash join to nested loop join.
EstimatedJoinTypeEstimatedJoinType El tipo de combinación probable.What the join type is likely to be.
ActualJoinTypeActualJoinType En un plan real, se muestra qué algoritmo de combinación se ha elegido finalmente según el umbral.In an actual plan, shows what join algorithm was ultimately chosen based on the threshold.

El plan estimado muestra la forma del plan de combinación adaptable, junto con un umbral de combinación adaptable definido y un tipo de combinación estimado.The estimated plan shows the Adaptive Join plan shape, along with a defined Adaptive Join threshold and estimated join type.

Combinación adaptable e interoperabilidad del Almacén de consultasAdaptive join and Query Store interoperability

El Almacén de consultas captura y puede aplicar un plan de combinación adaptable de modo de proceso por lotes.Query Store captures and is able to force a batch mode Adaptive Join plan.

Instrucciones aptas de combinación adaptableAdaptive join eligible statements

Algunas condiciones convierten a una combinación lógica en apta como combinación adaptable de modo de proceso por lotes:A few conditions make a logical join eligible for a batch mode Adaptive Join:

  • El nivel de compatibilidad de la base de datos es 140.The database compatibility level is 140.
  • La consulta es una instrucción SELECT (las instrucciones de modificación de datos no son aptas actualmente).The query is a SELECT statement (data modification statements are currently ineligible).
  • La combinación es apta para ejecutarla tanto con una combinación de bucles anidados indexada como con un algoritmo físico de combinación hash.The join is eligible to be executed both by an indexed Nested Loops Join or a Hash Join physical algorithm.
  • La combinación hash usa el modo por lotes, ya sea mediante la presencia de un índice de almacén de columnas en la consulta global o una referencia directa a la tabla con índice de almacén de columnas por parte de la combinación.The Hash Join uses batch mode - either through the presence of a Columnstore index in the query overall or a Columnstore indexed table being referenced directly by the join.
  • Las soluciones alternativas generadas de la combinación de bucles anidados y la combinación hash deben tener el mismo primer elemento secundario (referencia externa).The generated alternative solutions of the Nested Loops Join and Hash Join should have the same first child (outer reference).

Combinaciones adaptables y eficacia del bucle anidadoAdaptive joins and nested loop efficiency

Si una combinación adaptable cambia a una operación de bucles anidados, usa las filas ya leídas por la compilación de combinación hash.If an Adaptive Join switches to a Nested Loops operation, it uses the rows already read by the Hash Join build. El operador no vuelve a leer las filas de la referencia externa.The operator does not re-read the outer reference rows again.

Filas de umbral adaptableAdaptive threshold rows

El gráfico siguiente muestra una intersección de ejemplo entre el costo de una combinación hash y el de una alternativa de combinación de bucles anidados.The following chart shows an example intersection between the cost of a Hash Join versus the cost of a Nested Loops Join alternative. En este punto de intersección, se determina el umbral que a su vez determina el algoritmo real usado para la operación de combinación.At this intersection point, the threshold is determined that in turn determines the actual algorithm used for the join operation.

Umbral de combinación

Deshabilitar las combinaciones adaptables sin cambiar el nivel de compatibilidadDisabling adaptive joins without changing the compatibility level

Las combinaciones adaptables se pueden deshabilitar en el ámbito de base de datos o de instrucción mientras se mantiene el nivel de compatibilidad de base de datos 140 o posterior.Adaptive joins can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher.
Para deshabilitar las combinaciones adaptables para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:To disable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = ON;

Cuando se habilita, esta opción aparecerá como habilitada en sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. Para volver a habilitar las combinaciones adaptables para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:To re-enable adaptive joins for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_ADAPTIVE_JOINS = OFF;

También puede deshabilitar las combinaciones adaptables para una consulta específica si designa DISABLE_BATCH_MODE_ADAPTIVE_JOINS como una sugerencia de consulta USE HINT.You can also disable adaptive joins for a specific query by designating DISABLE_BATCH_MODE_ADAPTIVE_JOINS as a USE HINT query hint. Por ejemplo:For example:

SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
       ON s.CustomerCategoryID = sc.CustomerCategoryID
OPTION (USE HINT('DISABLE_BATCH_MODE_ADAPTIVE_JOINS')); 

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración de ámbito de base de datos o una opción de marca de seguimiento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Comentarios de concesión de memoria de modo de proceso por lotesBatch mode memory grant feedback

El plan posterior a la ejecución de una consulta en SQL ServerSQL Server incluye la memoria mínima necesaria para la ejecución y el tamaño de concesión de memoria ideal para que todas las filas quepan en la memoria.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. El rendimiento se ve afectado si los tamaños de concesión de memoria son incorrectos.Performance suffers when memory grant sizes are incorrectly sized. A su vez, unas concesiones excesivas se traducen en memoria desperdiciada y en simultaneidad reducida.Excessive grants result in wasted memory and reduced concurrency. Las concesiones de memoria insuficientes provocan un costoso desbordamiento en disco.Insufficient memory grants cause expensive spills to disk. Al ocuparse de las cargas de trabajo repetidas, los comentarios de concesión de memoria de modo de proceso por lotes vuelven a calcular la memoria real necesaria para una consulta y luego actualizan el valor de la concesión del plan almacenado en caché.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. Cuando se ejecuta una instrucción de consulta idéntica, la consulta usa el tamaño de concesión de memoria revisado, con lo que se reducen las concesiones de memoria excesivas que afectan a la simultaneidad y se solucionan las concesiones de memoria subestimadas que provocan costosos desbordamientos en 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. El gráfico siguiente muestra un ejemplo de uso de los comentarios de concesión de memoria adaptable de modo de proceso por lotes.The following graph shows one example of using batch mode adaptive memory grant feedback. Para la primera ejecución de la consulta, la duración es de 88 segundos, debido a los grandes desbordamientos: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;

Grandes desbordamientos

Con los comentarios de concesión de memoria habilitados para la segunda ejecución, la duración es de 1 segundo (partiendo de 88 segundos), los desbordamientos se eliminan por completo y la concesión es superior: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:

Sin desbordamientos

Tamaño de los comentarios de concesión de memoriaMemory grant feedback sizing

En el caso de una condición de concesión de memoria excesiva, si la memoria concedida es más de dos veces el tamaño de la memoria usada real, los comentarios de concesión de memoria volverán a calcular la concesión de memoria y actualizarán el plan almacenado en caché.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. Los planes con concesiones de memoria por debajo de 1 MB no se vuelven a calcular para usos por encima del límite.Plans with memory grants under 1 MB will not be recalculated for overages. En el caso de condiciones de concesión de memoria de tamaño insuficiente, que provocan un desbordamiento en disco para operadores de modo de proceso por lotes, los comentarios de concesión de memoria desencadenarán un nuevo cálculo de la concesión de memoria.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. Los eventos de desbordamiento se notifican a los comentarios de concesión de memoria y se pueden mostrar a través del evento de 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. Este evento devuelve el identificador de nodo del plan y el tamaño de los datos desbordados de ese nodo.This event returns the node id from the plan and spilled data size of that node.

Comentarios de concesión de memoria y escenarios confidenciales de parámetrosMemory grant feedback and parameter sensitive scenarios

Los distintos valores de parámetros también pueden necesitar diferentes planes de consulta para seguir siendo óptimos.Different parameter values may also require different query plans in order to remain optimal. Este tipo de consulta se define como "sensible a parámetros".This type of query is defined as "parameter-sensitive." En el caso de los planes sensibles a parámetros, los comentarios de concesión de memoria se deshabilitarán en una consulta si esta tiene requisitos de memoria inestables.For parameter-sensitive plans, memory grant feedback will disable itself on a query if it has unstable memory requirements. El plan se deshabilita después de que se ejecute la consulta de forma repetida y esto puede observarse mediante la supervisión de 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 obtener más información sobre la sensibilidad y el examen de los parámetros, consulte la Guía de arquitectura de procesamiento de consulta.For more information about parameter sniffing and parameter sensitivity, refer to the Query Processing Architecture Guide.

Almacenamiento en caché de los comentarios de concesión de memoriaMemory grant feedback caching

Los comentarios pueden almacenarse en el plan almacenado en caché para una sola ejecución.Feedback can be stored in the cached plan for a single execution. Pero son las ejecuciones consecutivas de esa instrucción las que se benefician de los ajustes de los comentarios de concesión de memoria.It is the consecutive executions of that statement, however, that benefit from the memory grant feedback adjustments. Esta característica se aplica a la ejecución repetida de instrucciones.This feature applies to repeated execution of statements. Los comentarios de concesión de memoria solo cambian el plan almacenado en caché.Memory grant feedback will change only the cached plan. Los cambios no se capturan actualmente en el almacén de consultas.Changes are currently not captured in the Query Store. Si el plan se elimina de la memoria caché, no se conservan los comentarios.Feedback is not persisted if the plan is evicted from cache. Los comentarios también se pierden si se produce una conmutación por error.Feedback will also be lost if there is a failover. Una instrucción con OPTION (RECOMPILE) crea un plan y no lo almacena en caché.A statement using OPTION (RECOMPILE) creates a new plan and does not cache it. Puesto que no se almacena, no se generan comentarios de concesión de memoria y no se almacenan para esa compilación y ejecución.Since it is not cached, no memory grant feedback is produced and it is not stored for that compilation and execution. Pero si una instrucción equivalente (es decir, con el mismo hash de consulta) que no ha usado OPTION (RECOMPILE) se ha almacenado en caché y luego se ha vuelto a ejecutar, la instrucción consecutiva puede beneficiarse de los comentarios de concesión de memoria.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.

Seguimiento de la actividad de los comentarios de concesión de memoriaTracking memory grant feedback activity

Puede realizar un seguimiento de los eventos de comentarios de concesión de memoria mediante el evento de xEvent memory_grant_updated_by_feedback.You can track memory grant feedback events using the memory_grant_updated_by_feedback xEvent. Este evento realiza un seguimiento del historial de recuentos de ejecución actual, del número de veces que los comentarios de concesión de memoria han provocado una actualización del plan, de la concesión de memoria adicional ideal antes de la modificación y de la concesión de memoria adicional ideal después de que los comentarios de concesión de memoria hayan modificado el plan almacenado en caché.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.

Comentarios de concesión de memoria, regulador de recursos y sugerencias de consultaMemory grant feedback, resource governor and query hints

La memoria real concedida respeta el límite de memoria de consulta determinado por el regulador de recursos o la sugerencia de consulta.The actual memory granted honors the query memory limit determined by the resource governor or query hint.

Deshabilitar los comentarios de concesión de memoria en modo de lote sin cambiar el nivel de compatibilidadDisabling batch mode memory grant feedback without changing the compatibility level

Los comentarios de concesión de memoria se pueden deshabilitar en el ámbito de base de datos o de instrucción mientras se mantiene el nivel de compatibilidad de base de datos 140 o posterior.Memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. Para deshabilitar los comentarios de concesión de memoria en modo por lotes para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable: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:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Cuando se habilita, esta opción aparecerá como habilitada en sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations.

Para volver a habilitar los comentarios de concesión de memoria en modo por lotes para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable: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:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

También puede deshabilitar los comentarios de concesión de memoria en modo por lotes para una consulta específica si designa DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK como sugerencia 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 ejemplo:For example:

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

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración de ámbito de base de datos o una opción de marca de seguimiento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Comentarios de concesión de memoria del modo de filaRow mode memory grant feedback

Se aplica a: Base de datos SQL de AzureAzure SQL Database como característica de versión preliminar pública.Applies to: Base de datos SQL de AzureAzure SQL Database as a public preview feature

Nota

Los comentarios de concesión de memoria del modo de fila es una característica en vista previa (GB) pública.Row mode memory grant feedback is a public preview feature.

Los comentarios de concesión de memoria del modo de fila se expanden en la característica de comentarios de concesión de memoria de modo de proceso por lotes al ajustar los tamaños de concesión de memoria tanto para los operadores del modo de proceso por lotes como del modo de fila.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 la versión preliminar pública de los comentarios de concesión de memoria del modo de fila en Base de datos SQL de AzureAzure SQL Database, habilite el nivel 150 de compatibilidad de la base de datos para la base de datos a la que se conecta cuando ejecuta la consulta.To enable the public preview of row mode memory grant feedback in Base de datos SQL de AzureAzure SQL Database, enable database compatibility level 150 for the database you are connected to when executing the query.

La actividad de comentarios de concesión de memoria del modo de fila será visible a través del evento extendido memory_grant_updated_by_feedback.Row mode memory grant feedback activity will be visible via the memory_grant_updated_by_feedback XEvent.

A partir de los comentarios de concesión de memoria del modo de fila, se mostrarán dos atributos nuevos de plan de consulta para los planes reales posteriores a la ejecución: IsMemoryGrantFeedbackAdjusted y LastRequestedMemory, que se agregan al elemento XML de plan de consulta MemoryGrantInfo.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 muestra la memoria concedida en Kilobytes (KB) desde la ejecución de consulta anterior.LastRequestedMemory shows the granted memory in Kilobytes (KB) from the prior query execution. El atributo IsMemoryGrantFeedbackAdjusted permite comprobar el estado de los comentarios de concesión de memoria para la instrucción dentro de un plan de ejecución de consulta real.IsMemoryGrantFeedbackAdjusted attribute allows you to check the state of memory grant feedback for the statement within an actual query execution plan. Los valores que se exponen en este atributo son los siguientes:Values surfaced in this attribute are as follows:

Valor IsMemoryGrantFeedbackAdjustedIsMemoryGrantFeedbackAdjusted Value DescripciónDescription
No: First ExecutionNo: First Execution Los comentarios de concesión de memoria no ajustan la memoria para la primera compilación y la ejecución asociada.Memory grant feedback does not adjust memory for the first compile and associated execution.
No: Accurate GrantNo: Accurate Grant Si no hay ningún desbordamiento al disco y la instrucción usa al menos 50 % de la memoria concedida, no se desencadenan los comentarios de concesión de memoria.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.
No: Feedback disabledNo: Feedback disabled Si los comentarios de concesión de memoria se desencadenan de manera continúa y fluctúan entre operaciones de aumento de memoria y operaciones de disminución de memoria, se deshabilitarán los comentarios de concesión de memoria para la instrucción.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.
Yes: AdjustingYes: Adjusting Se aplicaron los comentarios de concesión de memoria y se pueden seguir ajustando para la próxima ejecución.Memory grant feedback has been applied and may be further adjusted for the next execution.
Yes: StableYes: Stable Se aplicaron los comentarios de concesión de memoria y ahora la memoria concedida es estable, lo que significa que lo último que se concedió para la ejecución anterior es lo que se concedió para la ejecución actual.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.

Nota

Los atributos del plan de comentarios de concesión de memoria del modo de fila en versión preliminar pública son visibles en los planes de ejecución de consultas gráficas de SQL Server Management StudioSQL Server Management Studio en las versiones 17.9 y 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.

Deshabilitar los comentarios de concesión de memoria en modo de fila sin cambiar el nivel de compatibilidadDisabling row mode memory grant feedback without changing the compatibility level

Los comentarios de concesión de memoria en modo de fila se pueden deshabilitar en el ámbito de base de datos o de instrucción mientras se mantiene el nivel de compatibilidad de base de datos 150 o posterior.Row mode memory grant feedback can be disabled at the database or statement scope while still maintaining database compatibility level 150 and higher. Para deshabilitar los comentarios de concesión de memoria en modo de fila para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable: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 volver a habilitar los comentarios de concesión de memoria en modo de fila para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable: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;

También puede deshabilitar los comentarios de concesión de memoria en modo de fila para una consulta específica si designa DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK como sugerencia 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 ejemplo:For example:

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

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración de ámbito de base de datos o una opción de marca de seguimiento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Ejecución intercalada de MSTVFInterleaved execution for MSTVFs

Con la ejecución intercalada se usan los recuentos de filas reales de la función para tomar decisiones fundamentadas sobre los planes de consulta descendentes.With interleaved execution, the actual row counts from the function are used to make better-informed downstream query plan decisions. Para obtener más información sobre las funciones con valores de tabla de múltiples instrucciones (MSTVF), consulte Funciones con valores de tabla.For more information on multi-statement table-valued functions (MSTVFs), see Table-valued functions.

La ejecución intercalada cambia el límite unidireccional entre las fases de optimización y ejecución de una ejecución de una sola consulta y permite que los planes se adapten en función de las estimaciones de cardinalidad 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 la optimización, si se detecta un candidato para la ejecución intercalada, que son actualmente las funciones con valores de tabla de múltiples instrucciones (MSTVF) , se detiene la optimización, se ejecuta el subárbol aplicable, se capturan las estimaciones de cardinalidad precisas y luego se reanuda la optimización de las operaciones de nivel inferior.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.

Las MSTVF tienen una estimación de cardinalidad fija de 100 a partir de SQL Server 2014 (12.x)SQL Server 2014 (12.x), y de 1 en versiones anteriores de 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. La ejecución intercalada ayuda con los problemas de rendimiento de las cargas de trabajo debidos a estas estimaciones de cardinalidad fijas asociadas a las MSTVF.Interleaved execution helps workload performance issues that are due to these fixed cardinality estimates associated with MSTVFs. Para obtener más información sobre las MSTVF, vea Creación de funciones definidas por el usuario (motor de base de datos).For more information on MSTVFs, see Create User-defined Functions (Database Engine).

En la imagen siguiente se muestran los resultados de estadísticas de consultas dinámicas, un subconjunto de un plan de ejecución global que refleja el impacto de las estimaciones de cardinalidad fijas de las MSTVF.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. Puede ver el flujo de filas real frente a las filas estimadas.You can see the actual row flow vs. estimated rows. Hay tres áreas reseñables del plan (el flujo va de derecha a izquierda):There are three noteworthy areas of the plan (flow is from right to left):

  1. El recorrido de tabla de MSTVF tiene una estimación fija de 100 filas.The MSTVF Table Scan has a fixed estimate of 100 rows. Pero en este ejemplo hay 527.597 filas que pasan por este recorrido de tabla de MSTVF, como se muestra en las estadísticas de consultas dinámicas a través de 527597 de 100 reales de estimados, por lo que la estimación fija está considerablemente desviada.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. En el caso de la operación de bucles anidados, se supone que el lado externo de la combinación solo devuelve 100 filas.For the Nested Loops operation, only 100 rows are assumed to be returned by the outer side of the join. Dado el gran número de filas que las MSTVF devuelven en realidad, es probable que lo mejor sea un algoritmo de combinación totalmente 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. En el caso de la operación de coincidencia de hash, observe el pequeño símbolo de advertencia, que en este caso indica un desbordamiento en el disco.For the Hash Match operation, notice the small warning symbol, which in this case is indicating a spill to disk.

Flujo de filas frente a filas estimadas

Compare el plan anterior con el plan real generado con la ejecución intercalada habilitada:Contrast the prior plan with the actual plan generated with interleaved execution enabled:

Plan intercalado

  1. Observe que el recorrido de tabla de MSTVF ahora refleja una estimación de cardinalidad precisa.Notice that the MSTVF table scan now reflects an accurate cardinality estimate. Observe también la reordenación de este recorrido de tabla y las demás operaciones.Also notice the re-ordering of this table scan and the other operations.
  2. Con respecto a los algoritmos de combinación, se ha pasado de una operación de bucle anidado a una operación de coincidencia de hash, que es más adecuada dado el gran número de filas implicadas.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. Observe también que ya no hay advertencias de desbordamiento, dado que se ha concedido más memoria en función del recuento real de filas que pasan desde el recorrido de tabla 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.

Instrucciones aptas de ejecución intercaladaInterleaved execution eligible statements

Las instrucciones que hacen referencia a las MSTVF en la ejecución intercalada de momento deben ser de solo lectura y no formar parte de ninguna operación de modificación de datos.MSTVF referencing statements in interleaved execution must currently be read-only and not part of a data modification operation. Además, las MSTVF no son aptas para la ejecución intercalada si no usan constantes en tiempo de ejecución.Also, MSTVFs are not eligible for interleaved execution if they do not use runtime constants.

Ventajas de la ejecución intercaladaInterleaved execution benefits

En general, cuanta mayor sea la distorsión entre el número de filas real y el estimado, además del número de operaciones de nivel inferior del plan, mayor será el impacto sobre el rendimiento.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. En general, la ejecución intercalada beneficia a las consultas donde:In general, interleaved execution benefits queries where:

  1. Hay una gran distorsión entre el número real de filas y el estimado del conjunto de resultados intermedio (en este caso, las 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. Y la consulta global es sensible a un cambio en el tamaño del resultado intermedio.And the overall query is sensitive to a change in the size of the intermediate result. Esto suele suceder cuando hay un árbol complejo por encima de ese subárbol en el plan de consulta.This typically happens when there is a complex tree above that subtree in the query plan. Un simple instrucción SELECT * de una MSTVF no se beneficiará de la ejecución intercalada.A simple SELECT * from an MSTVF will not benefit from interleaved execution.

Sobrecarga de la ejecución intercaladaInterleaved execution overhead

La sobrecarga debe ser de mínima a ninguna.The overhead should be minimal-to-none. Las MSTVF ya se estaban materializando antes de la introducción de la ejecución intercalada; la diferencia es que ahora se permite la optimización diferida y luego se aprovecha la estimación de cardinalidad del conjunto de filas materializado.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. Al igual que cualquier plan que afecte a los cambios, algunos planes podrían cambiar de modo que con la mejor cardinalidad del subárbol se obtuviera un plan peor para la consulta en general.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. La mitigación puede incluir la reversión del nivel de compatibilidad o el uso del Almacén de consultas para aplicar la versión no revertida del plan.Mitigation can include reverting the compatibility level or using Query Store to force the non-regressed version of the plan.

Ejecución intercalada y ejecuciones consecutivasInterleaved execution and consecutive executions

Una vez que se almacena en caché un plan de ejecución intercalada, el plan con las estimaciones revisadas en la primera ejecución se usa para las ejecuciones consecutivas sin volver a crear una instancia de ejecución 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.

Seguimiento de la actividad de ejecución intercaladaTracking interleaved execution activity

Puede ver los atributos de uso en el plan de ejecución de consulta real:You can see usage attributes in the actual query execution plan:

Atributo del plan de ejecuciónExecution Plan attribute DescripciónDescription
ContainsInterleavedExecutionCandidatesContainsInterleavedExecutionCandidates Se aplica al nodo QueryPlan.Applies to the QueryPlan node. Si true significa que el plan contiene candidatos de ejecución intercalada.When true, means the plan contains interleaved execution candidates.
IsInterleavedExecutedIsInterleavedExecuted Atributo del elemento RuntimeInformation bajo el elemento RelOp del nodo de TVF.Attribute of the RuntimeInformation element under the RelOp for the TVF node. Si es true, significa que la operación se ha materializado como parte de una operación de ejecución intercalada.When true, means the operation was materialized as part of an interleaved execution operation.

También puede realizar el seguimiento de repeticiones de ejecución intercalada mediante los siguientes eventos de xEvents:You can also track interleaved execution occurrences via the following xEvents:

xEventxEvent DescripciónDescription
interleaved_exec_statusinterleaved_exec_status Este evento se desencadena cuando se está produciendo la ejecución intercalada.This event fires when interleaved execution is occurring.
interleaved_exec_stats_updateinterleaved_exec_stats_update Este evento describe las estimaciones de cardinalidad actualizadas por la ejecución intercalada.This event describes the cardinality estimates updated by interleaved execution.
Interleaved_exec_disabled_reasonInterleaved_exec_disabled_reason Este evento se desencadena cuando una consulta con un posible candidato para la ejecución intercalada no consigue realmente la ejecución intercalada.This event fires when a query with a possible candidate for interleaved execution does not actually get interleaved execution.

Se debe ejecutar una consulta para permitir que la ejecución intercalada revise las estimaciones de cardinalidad de MSTVF.A query must be executed in order to allow interleaved execution to revise MSTVF cardinality estimates. Pero el plan de ejecución estimado aún muestra cuándo hay candidatos de ejecución intercalada a través del atributo del plan de presentación de ContainsInterleavedExecutionCandidates.However, the estimated execution plan still shows when there are interleaved execution candidates via the ContainsInterleavedExecutionCandidates showplan attribute.

Almacenamiento en caché de la ejecución intercaladaInterleaved execution caching

Si un plan se borra o se elimina de la memoria caché, al ejecutarse la consulta se genera una nueva compilación que usa la ejecución intercalada.If a plan is cleared or evicted from cache, upon query execution there is a fresh compilation that uses interleaved execution. Una instrucción con OPTION (RECOMPILE) creará un plan con ejecución intercalada y no lo almacenará en la memoria caché.A statement using OPTION (RECOMPILE) will create a new plan using interleaved execution and not cache it.

Ejecución intercalada e interoperabilidad del Almacén de consultasInterleaved execution and query store interoperability

Los planes con ejecución intercalada se pueden aplicar.Plans using interleaved execution can be forced. El plan es la versión que ha corregido las estimaciones de cardinalidad basándose en la ejecución inicial.The plan is the version that has corrected cardinality estimates based on initial execution.

Deshabilitar la ejecución intercalada sin cambiar el nivel de compatibilidadDisabling interleaved execution without changing the compatibility level

La ejecución intercalada se puede deshabilitar en el ámbito de base de datos o de instrucción mientras se mantiene el nivel de compatibilidad de base de datos 140 o posterior.Interleaved execution can be disabled at the database or statement scope while still maintaining database compatibility level 140 and higher. Para deshabilitar la ejecución intercalada para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:To disable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = ON;

Cuando se habilita, esta opción aparecerá como habilitada en sys.database_scoped_configurations.When enabled, this setting will appear as enabled in sys.database_scoped_configurations. Para volver a habilitar la ejecución intercalada para todas las ejecuciones de consultas que se originan en la base de datos, ejecute lo siguiente en el contexto de la base de datos aplicable:To re-enable interleaved execution for all query executions originating from the database, execute the following within the context of the applicable database:

ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_INTERLEAVED_EXECUTION_TVF = OFF;

También puede deshabilitar la ejecución intercalada para una consulta específica si designa DISABLE_INTERLEAVED_EXECUTION_TVF como una sugerencia 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 ejemplo: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'));

Una sugerencia de consulta USE HINT tiene prioridad sobre una configuración de ámbito de base de datos o una opción de marca de seguimiento.A USE HINT query hint takes precedence over a database scoped configuration or trace flag setting.

Compilación diferida de variables de tablaTable variable deferred compilation

Nota

La compilación diferida de variables de tabla es una característica en vista previa pública.Table variable deferred compilation is a public preview feature.

La compilación diferida de variables de tabla mejora la calidad del plan y el rendimiento general de las consultas que hacen referencia a las variables de tabla.Table variable deferred compilation improves plan quality and overall performance for queries that reference table variables. Durante la optimización y la compilación inicial, esta característica propaga las estimaciones de cardinalidad que se basan en los recuentos de filas de variables de tabla reales.During optimization and initial compilation, this feature propagates cardinality estimates that are based on actual table variable row counts. Esta información precisa del recuento de filas optimiza las operaciones del plan de bajada.This accurate row count information optimizes downstream plan operations.

La compilación diferida de variables de tabla aplaza la compilación de una instrucción que hace referencia a una variable de tabla hasta la primera ejecución real de la instrucción.Table variable deferred compilation defers compilation of a statement that references a table variable until the first actual run of the statement. Este comportamiento de compilación diferida es el mismo que el de las tablas temporales.This deferred compilation behavior is the same as that of temporary tables. Este cambio se traduce en el uso de la cardinalidad real en lugar de la estimación de una fila original.This change results in the use of actual cardinality instead of the original one-row guess.

Puede habilitar la versión preliminar pública de la compilación diferida de variables de tabla en Azure SQL Database.You can enable the public preview of table variable deferred compilation in Azure SQL Database. Para ello, habilite el nivel de compatibilidad 150 para la base de datos a la que se conecta al ejecutar la consulta.To do that, enable compatibility level 150 for the database you're connected to when you run the query.

Para obtener más información, consulte Compilación diferida de variables de tabla.For more information, see Table variable deferred compilation.

Inserción de UDF escalarScalar UDF inlining

Nota

La inserción de la función definida por el usuario (UDF) escalar es una característica en versión preliminar pública.Scalar user-defined function (UDF) inlining is a public preview feature.

La inserción de UDF escalar transforma automáticamente las UDF escalares en expresiones relacionales.Scalar UDF inlining automatically transforms scalar UDFs into relational expressions. Las inserta en la consulta SQL de llamada.It embeds them in the calling SQL query. Esta transformación mejora el rendimiento de las cargas de trabajo que aprovechan las UDF escalares.This transformation improves the performance of workloads that take advantage of scalar UDFs. La inserción de UDF escalar facilita la optimización basada en costos de las operaciones dentro de las UDF.Scalar UDF inlining facilitates cost-based optimization of operations inside UDFs. Los resultados son eficaces, orientados a conjuntos y paralelos en lugar de tratarse de planes de ejecución seriales, iterativos e ineficaces.The results are efficient, set-oriented, and parallel instead of inefficient, iterative, serial execution plans. Esta característica está habilitada de forma predeterminada en el nivel de compatibilidad de base de datos 150.This feature is enabled by default under database compatibility level 150.

Para obtener más información, vea Inserción de UDF escalares.For more information, see Scalar UDF inlining.

Procesamiento de consultas aproximadoApproximate query processing

Nota

APPROX_COUNT_DISTINCT es una característica en versión preliminar pública.APPROX_COUNT_DISTINCT is a public preview feature.

El procesamiento de consultas aproximado es una nueva familia de características.Approximate query processing is a new feature family. Agrega conjuntos de datos de gran tamaño en los que la capacidad de respuesta resulta más fundamental que la precisión absoluta.It aggregates across large datasets where responsiveness is more critical than absolute precision. Un ejemplo es calcular el valor COUNT(DISTINCT()) entre 10 mil millones de filas para mostrar en un panel.An example is calculating a COUNT(DISTINCT()) across 10 billion rows, for display on a dashboard. En este caso, la precisión absoluta no es importante, pero la capacidad de respuesta es fundamental.In this case, absolute precision isn't important, but responsiveness is critical. La función de agregado APPROX_COUNT_DISTINCT nueva devuelve el número aproximado de valores no nulos únicos de un grupo.The new APPROX_COUNT_DISTINCT aggregate function returns the approximate number of unique non-null values in a group.

Para más información, consulte APPROX_COUNT_DISTINCT (Transact-SQL).For more information, see APPROX_COUNT_DISTINCT (Transact-SQL).

Modo por lotes en el almacén de filasBatch mode on rowstore

Nota

El modo por lotes en el almacén de filas es una característica en versión preliminar pública.Batch mode on rowstore is a public preview feature.

El modo por lotes en el almacén de filas permite la ejecución en modo por lotes de las cargas de trabajo de análisis sin necesidad de índices de almacén de columnas.Batch mode on rowstore enables batch mode execution for analytic workloads without requiring columnstore indexes. Esta característica admite la ejecución en modo por lotes y filtros de mapa de bits para montones en disco e índices de árbol B.This feature supports batch mode execution and bitmap filters for on-disk heaps and B-tree indexes. El modo por lotes en el almacén de filas permite la compatibilidad con todos operadores habilitados para el modo por lotes existentes.Batch mode on rowstore enables support for all existing batch mode-enabled operators.

Información previaBackground

SQL Server 2012 (11.x)SQL Server 2012 (11.x) introdujo una nueva característica para acelerar las cargas de trabajo de análisis: los índices de almacén de columnas.introduced a new feature to accelerate analytical workloads: columnstore indexes. Ampliamos los casos de uso y mejoramos el rendimiento de los índices de almacén de columnas en las versiones posteriores.We expanded the use cases and improved the performance of columnstore indexes in each subsequent release. Hasta ahora, se han descubierto y documentado todas estas funciones como una sola característica.Until now, we surfaced and documented all these capabilities as a single feature. Crea los índices de almacén de columnas en sus tablas.You create columnstore indexes on your tables. Y su carga de trabajo de análisis va más rápido.And your analytical workload goes faster. Sin embargo, hay dos conjuntos diferentes de tecnologías, aunque guardan relación:However, there are two related but distinct sets of technologies:

  • Con los índices de almacén de columnas, las consultas analíticas tienen acceso solo a los datos de las columnas que necesitan.With columnstore indexes, analytical queries access only the data in the columns they need. La compresión de página en formato de almacén de columnas también es más eficaz que la compresión en los índices de almacén de filas tradicionales.Page compression in the columnstore format is also more effective than compression in traditional rowstore indexes.
  • Con el procesamiento de modo por lotes, los operadores de consulta procesan los datos con mayor eficacia.With batch mode processing, query operators process data more efficiently. Funcionan en un lote de filas en lugar de una fila cada vez.They work on a batch of rows instead of one row at a time. Hay más mejoras de escalabilidad relacionadas con el proceso en modo por lotes.A number of other scalability improvements are tied to batch mode processing. Para obtener más información sobre el modo por lotes, consulte Modos de ejecución.For more information on batch mode, see Execution modes.

Los dos conjuntos de características funcionan conjuntamente para mejorar la utilización de entrada/salida (E/S) y CPU:The two sets of features work together to improve input/output (I/O) and CPU use:

  • Mediante el uso de índices de almacén de columnas, más datos suyos caben en la memoria.By using columnstore indexes, more of your data fits in memory. Eso reduce la necesidad de E/S.That reduces the need for I/O.
  • El proceso en modo por lotes utiliza la CPU de manera más eficaz.Batch mode processing uses CPU more efficiently.

Las dos tecnologías se apoyan entre sí siempre que es posible.The two technologies take advantage of each other whenever possible. Por ejemplo, es posible evaluar agregados del modo por lotes como parte de una exploración del índice de almacén de columnas.For example, batch mode aggregates can be evaluated as part of a columnstore index scan. También se procesan los datos de un almacén de columnas comprimidos con codificación run-length de forma mucho más eficiente con combinaciones del modo por lotes y los agregados de modo por 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.

Las dos características se pueden usar de forma independiente:The two features are independently usable:

  • Obtiene planes de modo de fila que usan índices de almacén de columnas.You get row mode plans that use columnstore indexes.
  • Obtiene planes de modo por lotes que usan índices de almacén de filas.You get batch mode plans that use only rowstore indexes.

Normalmente obtiene los mejores resultados al usar las dos características conjuntamente.You usually get the best results when you use the two features together. Así pues, hasta ahora, el optimizador de consultas de SQL Server ha tenido en cuenta el procesamiento de modo por lotes solo para aquellas consultas que implican al menos una tabla con un índice de almacén de columnas.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.

Los índices de almacén de columnas no son una buena opción para algunas aplicaciones.Columnstore indexes aren't a good option for some applications. Una aplicación podría usar cualquier otra característica no compatible con los índices de almacén de columnas.An application might use some other feature that isn't supported with columnstore indexes. Por ejemplo, las modificaciones en contexto no son compatibles con la compresión del almacén de columnas.For example, in-place modifications aren't compatible with columnstore compression. De este modo, los desencadenadores no se admiten en tablas con índices de almacén de columnas en clúster.So triggers aren't supported on tables with clustered columnstore indexes. Y, lo que es más importante, los índices de almacén de columnas agregan sobrecarga para las instrucciones DELETE y UPDATE.More important, columnstore indexes add overhead for DELETE and UPDATE statements.

Para algunas cargas de trabajo híbridas transaccionales y analíticas, la sobrecarga que añaden a los aspectos transaccionales de una carga de trabajo es superior que las ventajas de los índices de almacén de columnas.For some hybrid transactional-analytical workloads, the overhead on a workload's transactional aspects outweighs the benefits of columnstore indexes. Estos escenarios pueden mejorar el uso de CPU desde el procesamiento de modo por lotes solamente.Such scenarios can improve CPU use from batch mode processing alone. Por eso, el modo por lotes en la característica de almacén de filas tiene en cuenta el modo por lotes para todas las consultas.That's why the batch mode on rowstore feature considers batch mode for all queries. No importa qué índices están implicados.It doesn't matter which indexes are involved.

Cargas de trabajo que pueden beneficiarse del modo por lotes en el almacén de filasWorkloads that might benefit from batch mode on rowstore

Las siguientes cargas de trabajo pueden beneficiarse del modo por lotes en el almacén de filas:The following workloads might benefit from batch mode on rowstore:

  • una parte significativa de la carga de trabajo consta de consultas analíticas.A significant part of the workload consists of analytical queries. Normalmente, estas consultas tienen operadores como combinaciones o agregados que procesan cientos de miles de filas o más.Usually, these queries have operators like joins or aggregates that process hundreds of thousands of rows or more.
  • La carga de trabajo está enlazada a la CPU.The workload is CPU bound. Si el cuello de botella es E/S, seguimos recomendando que tenga en cuenta un índice de almacén de columnas, si es posible.If the bottleneck is I/O, we still recommend that you consider a columnstore index, if possible.
  • La creación de un índice de almacén de columnas agrega demasiada sobrecarga al elemento transaccional de su carga de trabajo.Creating a columnstore index adds too much overhead to the transactional part of your workload. O bien, la creación de un índice de almacén de columnas no sería factible porque la aplicación depende de una característica que no es compatible aún con los índices de almacén de columnas.Or, creating a columnstore index isn't feasible because your application depends on a feature that's not yet supported with columnstore indexes.

Nota

El modo por lotes en el almacén de filas solo sirve para reducir el consumo de CPU.Batch mode on rowstore helps only by reducing CPU consumption. Si el cuello de botella está relacionado con la E/S y los datos ya no están almacenados en caché (caché "en frío"), el modo por lotes en el almacén de filas no mejorará el tiempo transcurrido.If your bottleneck is I/O related, and data isn't already cached ("cold" cache), batch mode on rowstore won't improve elapsed time. De forma similar, si no hay suficiente memoria en el equipo para almacenar en caché todos los datos, es poco probable que mejore el rendimiento.Similarly, if there isn't enough memory on the machine to cache all the data, a performance improvement is unlikely.

¿Qué cambios se producirán con el modo por lotes en el almacén de filas?What changes with batch mode on rowstore?

Aparte de pasar al nivel de compatibilidad 150, no es necesario que haga cambios para habilitar el modo por lotes en el almacén de filas para las cargas de trabajo 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.

Incluso si una consulta no implica ninguna tabla con un índice de almacén de columnas, el procesador de consultas ahora usa la heurística para decidir si se va a tener en cuenta el modo por lotes.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. La heurística consiste en estas comprobaciones:The heuristics consist of these checks:

  1. Una comprobación inicial de tamaños de tablas, operadores utilizados y cardinalidades estimadas en la consulta de entrada.An initial check of table sizes, operators used, and estimated cardinalities in the input query.
  2. Puntos de control adicionales, a medida que el optimizador detecta planes nuevos y más baratos para la consulta.Additional checkpoints, as the optimizer discovers new, cheaper plans for the query. Si estos planes alternativos no hacen un uso considerable del modo por lotes, el optimizador dejará explorar alternativas al modo por lotes.If these alternative plans don't make significant use of batch mode, the optimizer stops exploring batch mode alternatives.

Si se usa el modo por lotes en el almacén de filas, verá el modo de ejecución real como modo por lotes en el plan de consulta.If batch mode on rowstore is used, you see the actual run mode as batch mode in the query plan. El operador de examen usa el modo por lotes para montones en disco e índices de árbol B.The scan operator uses batch mode for on-disk heaps and B-tree indexes. Esta exploración del modo por lotes puede evaluar los filtros de mapa de bits del modo por lotes.This batch mode scan can evaluate batch mode bitmap filters. También podría ver otros operadores del modo por lotes en el plan.You might also see other batch mode operators in the plan. Entre los ejemplos se incluyen combinaciones hash, agregados basados en hash, ordenaciones, agregados de ventana, filtros, concatenación y operadores Compute Scalar.Examples are hash joins, hash-based aggregates, sorts, window aggregates, filters, concatenation, and compute scalar operators.

NotasRemarks

  • Los planes de consulta no siempre usan el modo por lotes.Query plans don't always use batch mode. El optimizador de consultas puede decidir que el modo por lotes no es beneficioso para la consulta.The query optimizer might decide that batch mode isn't beneficial for the query.
  • El espacio de búsqueda del optimizador de consultas está cambiando.The query optimizer's search space is changing. Así pues, si obtiene un plan de modo de fila, podría no ser igual al plan obtenido en un nivel de compatibilidad más bajo.So if you get a row mode plan, it might not be the same as the plan you get in a lower compatibility level. Y, si obtiene un plan de modo por lotes, podría no ser igual al plan obtenido con un índice de almacén de columnas.And if you get a batch mode plan, it might not be the same as the plan you get with a columnstore index.
  • Los planes también pueden cambiar para las consultas que combinan los índices de almacén de columnas y de almacén de filas como consecuencia de una nueva exploración del almacén de filas en modo por lotes.Plans might also change for queries that mix columnstore and rowstore indexes because of the new batch mode rowstore scan.
  • Existen limitaciones actuales para el nuevo examen de modo por lotes en el almacén de filas:There are current limitations for the new batch mode on rowstore scan:
    • no se iniciará para tablas OLTP en memoria, ni para los índices que no sean de montones en disco y árboles B.It won't kick in for in-memory OLTP tables or for any index other than on-disk heaps and B-trees.
    • Tampoco se iniciará si se captura o se filtra una columna de objetos de gran tamaño (LOB).It also won't kick in if a large object (LOB) column is fetched or filtered. Esta limitación incluye conjuntos de columnas dispersas y columnas XML.This limitation includes sparse column sets and XML columns.
  • Hay consultas para las que no se usa el modo por lotes incluso con índices de almacén de columnas.There are queries that batch mode isn't used for even with columnstore indexes. Entre los ejemplos se incluyen consultas que implican cursores.Examples are queries that involve cursors. Estas mismas exclusiones también se amplían al modo por lotes en el almacén de filas.These same exclusions also extend to batch mode on rowstore.

Configuración del modo por lotes en el almacén de filasConfigure batch mode on rowstore

La configuración de ámbito de base de datos BATCH_MODE_ON_ROWSTORE está activada de forma predeterminada.The BATCH_MODE_ON_ROWSTORE database scoped configuration is on by default. Deshabilita el modo por lotes en el almacén de filas sin necesidad de un cambio en el nivel de compatibilidad de la base de datos: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;

puede deshabilitar el modo por lotes en el almacén de filas a través de la configuración de ámbito de base de datos.You can disable batch mode on rowstore via database scoped configuration. Pero aún puede invalidar la configuración en el nivel de consulta con la sugerencia de consulta ALLOW_BATCH_MODE.But you can still override the setting at the query level by using the ALLOW_BATCH_MODE query hint. El ejemplo siguiente habilita el modo por lotes en el almacén de filas incluso con la característica deshabilitada a través de la configuración de ámbito de base de datos: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'));

También puede deshabilitar el modo por lotes en el almacén de filas para una consulta específica mediante el uso de la sugerencia 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. Vea el ejemplo siguiente: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'));

Vea tambiénSee also

Performance Center for SQL Server Database Engine and Azure SQL Database (Centro de rendimiento para el motor de base de datos SQL Server y Azure SQL Database)Performance Center for SQL Server Database Engine and Azure SQL Database
Guía de arquitectura de procesamiento de consultas Query processing architecture guide
Referencia de operadores lógicos y físicos del plan de presentación Showplan logical and physical operators reference
Combinaciones Joins
Demostración del procesamiento de consultas adaptable Demonstrating adaptive query processing
Demonstrating Intelligent QP (Demostración de QP inteligente)Demonstrating intelligent QP