Pueden producirse concesiones elevadas de CPU o memoria con consultas que usan un bucle anidado optimizado o una ordenación por lotes

  • Artículo
  • Tiempo de lectura: 2 minutos

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

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

Síntomas

Cuando se opera Microsoft SQL Server que tiene una carga de trabajo altamente simultánea, es posible que observe algunos problemas de rendimiento en las consultas. Este comportamiento puede mostrar como solicitudes de concesión de memoria extremas o de uso medio o alto de CPU.

También puede experimentar otros efectos secundarios, como condiciones de OOM, presión de memoria para planear el desalojo de caché o RESOURCE_SEMAPHORE esperas inesperadas.

Además, puede observar que los planes de consulta para consultas que consumen 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 SQL Server procesador de consultas 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 instalació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 que se espera.

Solución

Marca de seguimiento 2340

Para solucionar 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 que obtiene 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 que se tocan por el plan.

Para obtener más información, vea DBCC TRACEON - Trace Flags (Transact-SQL).

Modificar código para usar la DISABLE_OPTIMIZED_NESTED_LOOP sugerencia

Como alternativa, aplique la siguiente sugerencia DISABLE_OPTIMIZED_NESTED_LOOP 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, vea DISABLE_OPTIMIZED_NESTED_LOOP.

Más información

Motor de base de datos de inicio del servicio

Se aplica a

  • SQL Server 2005 a SQL Server 2019