Que es Ad hoc Workloads y como funciona: Ad hoc Workloads como funciona para optimizar

JuanCastillo 24 septiembre, 20175 noviembre, 2017 1 comentario

Como optimizar consultas SQL con Ad hoc Workloads

ad hoc workloads es una opcion utilizada en la configuracion del motor de base de datos SQL server para mejorar el rendimiento de las consultas con grandes volumenes de datos

Generar el plan de ejecución tomará pocos milisegundos de los ciclos de la CPU, que es insignificante para una consulta o carga pequeña, pero será considerable para una carga de trabajo muy pesada. Debido a esto, SQL Server almacena estos planes generados en un tipo especial de memoria llamado Plan Cache para eliminar la sobrecarga generada por el plan de consulta si se ejecuta de nuevo la misma consulta. Cuando envíe su consulta al motor de SQL Server, buscará en la caché del plan si hay algún plan de ejecución existente que pueda ser reutilizado, si se encuentra un plan de ejecución disponible en la caché del plan, se utilizará el plan para ejecutarlo , de lo contrario, el Optimizador de consultas de SQL Server creará un nuevo plan y lo mantendrá en la caché del plan para su uso futuro.

Como configurar Ad hoc Workloads

Hay una opción muy buena en SQL Server para optimización, es decir, «Ad hoc Workloads«. Mediante esta opción, puede maximizar el uso del plan de cache de SQL Server. Cuando su carga de trabajo contiene consultas de un solo uso adhoc o lotes, esta opción le proporcionará una buena mejora de rendimiento en términos del plan de caché.

Para cambiar esta configuracion existe dos maneras por codigo o mediante el SSMSOpcion 1

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
sp_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO
sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
sp_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO

sp_CONFIGURE 'show advanced options',1

RECONFIGURE

sp_CONFIGURE 'optimize for ad hoc workloads',1

RECONFIGURE

sp_CONFIGURE 'show advanced options',1

RECONFIGURE

sp_CONFIGURE 'optimize for ad hoc workloads',1

RECONFIGURE

Opcion 2

Pantalla de configuracion de nuestro motor de base de datos ad hoc workloads — Opciones de configuracion del SQL ad hoc workloads

Todos se preguntan, Cambio estos parametros y ahora que?

Con la siguiente consulta podran ahora validar sus cambios

SELECT objtype AS [PlanCacheType]
 ,COUNT_BIG(1) AS [NumOfPlans]
 ,SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024
AS [PlanSizeInMB]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY NumOfPlans DESC
GO

SELECT objtype AS [PlanCacheType]

,COUNT_BIG(1) AS [NumOfPlans]

,SUM(CAST(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024

AS [PlanSizeInMB]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY NumOfPlans DESC

Si los planes de ejecución en caché de su sistema siguen sin ajustarse al almacenamiento de la caché del plan aunque habilitó la opción Optimizar para cargas de trabajo ad hoc y no puede ampliar la memoria de SQL Server ni realizar cambios en su aplicación, Ejecute el siguien script

DBCC FREESYSTEMCACHE ('ALL',DEFAULT);

1	DBCC FREESYSTEMCACHE ('ALL',DEFAULT);

Un comentario en «Que es Ad hoc Workloads y como funciona: Ad hoc Workloads como funciona para optimizar»

Edgar

el 9 julio, 2018 a las 7:10 pm
Enlace permanente

Lejos de optimizar consultas, te ayuda a la optimización del uso de la memoria.
Saludos
Accede para responder