optimize for ad hoc workloads (opción de configuración del servidor)optimize for ad hoc workloads Server Configuration Option

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

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.The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. Cuando esta opción está establecida en 1, Motor de base de datosDatabase Engine almacena un pequeño código auxiliar del plan compilado en la memoria caché del plan al compilar un lote por primera vez, en lugar del plan compilado completo.When this option is set to 1, the Motor de base de datosDatabase Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. Esto ayuda a disminuir la demanda de memoria al impedir que la memoria caché del plan se llene de planes compilados que no se reutilizan.This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

El código auxiliar del plan compilado permite que Motor de base de datosDatabase Engine reconozca que este lote ad hoc se ha compilado antes, pero que solo se ha almacenado un código auxiliar del plan compilado, de modo que cuando se invoca de nuevo este lote (compilado o ejecutado), Motor de base de datosDatabase Engine compila el lote, quita de la memoria caché del plan el código auxiliar del plan compilado y agrega el plan compilado completo a la memoria caché del plan.The compiled plan stub allows the Motor de base de datosDatabase Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the Motor de base de datosDatabase Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.

El código auxiliar del plan compilado es uno de los elementos cacheobjtypes mostrados por la vista de catálogo sys.dm_exec_cached_plans.The compiled plan stub is one of the cacheobjtypes displayed by the sys.dm_exec_cached_plans catalog view. Tiene un identificador de sql e identificador del plan único.It has a unique sql handle and plan handle. El código auxiliar del plan compilado no tiene un plan de ejecución asociado a él por lo que, al consultar el identificador del plan, no se devolverá un plan de presentación XML.The compiled plan stub does not have an execution plan associated with it and querying for the plan handle will not return an XML Showplan.

La marca de seguimiento 8032 revierte los parámetros de límite de la memoria caché al valor SQL Server 2005 (9.x)SQL Server 2005 (9.x) de RTM que, en general, permite que las memorias caché sean mayores.Trace flag 8032 reverts the cache limit parameters to the SQL Server 2005 (9.x)SQL Server 2005 (9.x) RTM setting which in general allows caches to be larger. Use este valor cuando las entradas de caché que se reutilizan con frecuencia no quepan en la caché y cuando la opción de configuración del servidor Optimizar para cargas de trabajo ad hoc no haya podido resolver el problema con la caché de planes.Use this setting when frequently reused cache entries do not fit into the cache and when the optimize for ad hoc workloads Server Configuration Option has failed to resolve the problem with plan cache.

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.Trace flag 8032 can cause poor performance if large caches make less memory available for other memory consumers, such as the buffer pool.

RecomendacionesRecommendations

Evite tener un gran número de planes de uso único en la caché de planes.Avoid having a large number of single-use plans in the plan cache. Una de las causas más comunes por las que ocurre este problema es porque los tipos de datos de los parámetros de consulta no están definidos de forma coherente.A common cause of this problem is when the data types of query parameters is not consistently defined. 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.This particularly applies to the length of strings but can apply to any data type that has a maxlength, a precision, or a scale. Por ejemplo, si un parámetro denominado @Greeting se pasa como un nvarchar (10) en una llamada y un nvarchar (20) en la siguiente llamada, se crean planes independientes para cada tamaño de parámetro.For example, if a parameter named @Greeting is passed as an nvarchar(10) on one call and an nvarchar(20) on the next call, separate plans are created for each parameter size. Si una consulta tiene varios parámetros y no están definidos de forma coherente cuando se realiza su llamada, podría existir un gran número de planes de consulta para cada consulta.If a query has several parameters and they are not consistently defined when called, a large number of query plans could exist for each query. Podrían existir planes para cada combinación de longitudes y tipos de datos de parámetros de consulta que se haya usado.Plans could exist for each combination of query parameter data types and lengths that have been used.

Si el número de planes de uso único usa una parte significativa de memoria de Motor de base de datos de SQL ServerSQL Server Database Engine 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.If the number of single-use plans take a significant portion of Motor de base de datos de SQL ServerSQL Server Database Engine memory in an OLTP server, and these plans are Ad-hoc plans, use this server option to decrease memory usage with these objects. Para determinar el número de planes almacenados en caché de uso único, ejecute la consulta siguiente:To find the number of single-use cached plans, run the following query:

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

Importante

Establecer la opción Optimizar para cargas de trabajo ad hoc en 1 afecta solo a los planes nuevos; los planes que ya están en la memoria caché del plan no resultan afectados.Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected. 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 ServerSQL Server.To affect already cached query plans immediately, the plan cache needs to be cleared using ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, or SQL ServerSQL Server has to restart.

Consulte tambiénSee Also

sys.dm_exec_cached_plans (Transact-SQL) sys.dm_exec_cached_plans (Transact-SQL)
Opciones de configuración de servidor (SQL Server)Server Configuration Options (SQL Server)