Nota:
El acceso a esta página requiere autorización. Puede intentar iniciar sesión o cambiar directorios.
El acceso a esta página requiere autorización. Puede intentar cambiar los directorios.
Se aplica a:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)
La opción optimize for ad hoc workloads se utiliza para mejorar la eficiencia de la caché de planes en 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 caché a la configuración RTM de SQL Server 2005 (9.x), que en general permite que las memorias caché sean mayores. Usa esta configuración cuando las entradas de caché reutilizadas con frecuencia no tengan espacio en la caché y cuando la opción optimize for ad hoc workloads no pudo resolver el problema con la caché de planes.
Advertencia
La marca de seguimiento 8032 puede provocar un rendimiento deficiente si las cachés grandes hacen que haya menos memoria disponible para otros consumidores de memoria, como el grupo de búferes.
Observaciones
Establecer la opción optimize for ad hoc workloads en 1 afecta solo a los nuevos planes; los planes que ya están en la memoria caché del plan no se ven 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
@Greetingse 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 optimize for ad hoc workloads está habilitada, no puede ver los planes de ejecución para consultas de uso único, ya que solo se almacena en caché la plantilla 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;