Opción de configuración del servidor Optimizar para cargas de trabajo ad hoc

  • Artículo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)

La opción Optimizar para cargas de trabajo ad hoc se utiliza para mejorar la eficiencia de la memoria caché del plan para cargas de trabajo que contienen muchos lotes ad hoc de uso único. Cuando esta opción está establecida en 1, el Motor de base de datos almacena un pequeño código auxiliar del plan compilado en la caché de planes al compilar un lote por primera vez, en lugar del plan compilado completo. Esta opción puede ayudar a disminuir la presión de memoria al impedir que la caché de planes se llene de planes compilados que no se reutilizan. Pero habilitar esta opción puede afectar a la capacidad de solucionar problemas de planes de uso único.

El stub del plan compilado permite al Motor de base de datos reconocer que este lote ad hoc se ha compilado anteriormente y tan solo almacena un stub de plan compilado. Cuando este lote se invoca (compila o ejecuta) de nuevo, el Motor de base de datos compila el lote, elimina el código auxiliar del plan compilado de la caché de planes y añade el plan compilado completo a la caché de planes.

Para encontrar códigos auxiliares de plan compilados, consulte la vista de catálogo sys.dm_exec_cached_plans y busque "Plan compilado" en la columna cacheobjtype. El código auxiliar tiene un valor plan_handle único. El código auxiliar del plan compilado no tiene un plan de ejecución asociado, por lo que, al consultar el identificador del plan, no se devolverá un plan de presentación gráfico o XML.

La marca de seguimiento 8032 revierte los parámetros de límite de la caché al valor SQL Server 2005 (9.x) de RTM que, en general, permite que las cachés sean mayores. Use este valor cuando las entradas de caché que se reutilizan con frecuencia no quepan en la caché y cuando la opción Optimizar para cargas de trabajo ad hoc no pueda resolver el problema con la caché de planes.

Advertencia

La marca de seguimiento 8032 puede ocasionar la degradación del rendimiento si las memorias caché grandes suponen que haya menos memoria disponible para otros consumidores de memoria, como el grupo de búferes.

Observaciones

Establecer la opción Optimizar para cargas de trabajo ad hoc en 1 solo afecta a los planes nuevos; los planes que ya están en la caché del plan no resultan afectados.

Para afectar a los planes de consulta que ya están almacenados en caché inmediatamente, es necesario borrar la cache mediante ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE o reiniciar SQL Server.

Recomendaciones

Evite tener un gran número de planes de uso único en la caché de planes. Las causas más comunes son:

  • Tipos de datos de parámetros de consulta que no se definen de forma coherente. Esto se aplica especialmente a la longitud de las cadenas, pero puede aplicarse a cualquier tipo de datos que tenga una longitud máxima, una precisión o una escala. Por ejemplo, si un parámetro denominado @Greeting se pasa como nvarchar(10) en una llamada y como nvarchar(20) en la llamada siguiente, se crean planes independientes para cada tamaño de parámetro.

  • Consultas que no están parametrizadas. Si una consulta tiene uno o más parámetros para los que se envían valores codificados al Motor de base de datos, podría existir un gran número de planes de consulta para cada consulta. Podrían existir planes para cada combinación de longitudes y tipos de datos de parámetros de consulta que se haya usado.

Si el número de planes de uso único utiliza una parte significativa de memoria de Motor de base de datos de SQL Server en un servidor OLTP y estos planes son planes ad hoc, use esta opción de servidor para reducir el uso de memoria con estos objetos.

Si la opción optimizar para cargas de trabajo ad hoc está habilitada, no puede ver los planes de ejecución de consultas de uso único, ya que solo se almacena en caché el código auxiliar del plan. Según el entorno y la carga de trabajo, puede beneficiarse de las dos características siguientes:

  • La característica Almacén de consultas, introducida en SQL Server 2016 (13.x), le ayuda a encontrar rápidamente diferencias de rendimiento causadas por cambios en el plan de consulta. El Almacén de consultas está habilitado de manera predeterminada en las nuevas bases de datos de SQL Server 2022 (16.x) y versiones posteriores.

  • Puede que la parametrización forzada mejore el rendimiento de determinadas bases de datos al reducir la frecuencia de las compilaciones y recopilaciones de consultas. Las bases de datos que se benefician de la parametrización forzada suelen ser las que experimentan grandes volúmenes de consultas simultáneas de orígenes como las aplicaciones de punto de venta.

    La parametrización forzada puede provocar problemas de rendimiento debido a la confidencialidad de los parámetros. Para obtener más información, consulte Investigar y resolver problemas confidenciales de parámetros. Para SQL Server 2022 (16.x) y versiones posteriores, también puede habilitar la optimización del plan confidencial de parámetros.

Ejemplos

Para determinar el número de planes almacenados en caché de uso único, ejecute la consulta siguiente:

SELECT objtype,
    cacheobjtype,
    SUM(refcounts) AS AllRefObjects,
    SUM(CAST(size_in_bytes AS BIGINT)) / 1024 / 1024 AS SizeInMB
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc'
    AND usecounts = 1
GROUP BY objtype, cacheobjtype;

Contenido relacionado