Se pueden producir concesiones elevadas de CPU o memoria con consultas que usan bucles anidados optimizados o ordenación por lotes

Este artículo le ayuda a resolver el problema en el que se produce un uso elevado de cpu al ejecutar consultas en SQL Server.

Se aplica a: SQL Server
Número de KB original: 2009160

Síntomas

Cuando opera Microsoft SQL Server que tiene una carga de trabajo muy simultánea, es posible que observe algunos problemas de rendimiento en las consultas. Este comportamiento puede mostrarse como solicitudes de concesión de memoria medias a altas o de uso extremo a CPU.

También puede experimentar otros efectos secundarios, como condiciones de OOM, presión de memoria para la expulsión de caché del plan o esperas inesperadas RESOURCE_SEMAPHORE .

Además, puede observar que los planes de consulta para las consultas que consumen una gran cantidad de CPU o memorias tienen el atributo OPTIMIZED para un operador de combinación de bucles anidados establecido en True.

Causa

Este problema puede producirse en algunos casos en los que el procesador de consultas de SQL Server introduce una operación de ordenación opcional para mejorar el rendimiento. Esta operación se conoce como "Bucle anidado optimizado" o "Ordenación por lotes" y el optimizador de consultas determina cuándo introducir mejor estos operadores. En raras ocasiones, la consulta toca solo unas pocas filas, pero el costo de configuración de la operación de ordenación es tan significativo que el costo del bucle anidado optimizado supera sus ventajas. Por lo tanto, en esos casos puede observar un rendimiento más lento en comparación con lo esperado.

Solución

Marca de seguimiento 2340

Para corregir el problema, use la marca de seguimiento 2340 para deshabilitar la optimización. La marca de seguimiento 2340 indica al procesador de consultas que no use una operación de ordenación (ordenación por lotes) para combinaciones de bucle anidadas optimizadas al generar un plan de consulta. Esto afecta a toda la instancia.

Antes de habilitar esta marca de seguimiento, puede probar las aplicaciones exhaustivamente para asegurarse de obtener las ventajas de rendimiento esperadas al deshabilitar esta optimización. Esto se debe a que la optimización de ordenación puede ser útil cuando hay un gran aumento en el número de filas tocadas por el plan.

Para obtener más información, vea DBCC TRACEON - Marcas de seguimiento (Transact-SQL).

Modificación del código para usar la sugerencia de DISABLE_OPTIMIZED_NESTED_LOOP

Como alternativa, aplique la siguiente DISABLE_OPTIMIZED_NESTED_LOOP sugerencia de consulta para deshabilitar la optimización en el nivel de consulta.

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

Para obtener más información, consulte DISABLE_OPTIMIZED_NESTED_LOOP.

Más información

Opciones de inicio del servicio de motor de base de datos

Se aplica a

• SQL Server 2005 a SQL Server 2019