Este team de redactores ha estado horas buscando para darle solución a tus dudas, te compartimos la solución por eso deseamos que te sea de mucha ayuda.
Solución:
Entonces, solo desea borrar los planes de consulta Ad-hoc y aún no desea borrar todo el caché del procedimiento. Lo que está preguntando está en Borrar sus planes de SQL ad-hoc mientras mantiene intactos sus planes de SP
El blog te pide que corras
DBCC FREESYSTEMCACHE('SQL Plans')
Según el blog
La caché de procedimientos en realidad consta de 4 almacenes de caché distintos que contienen diferentes tipos de planes. Esos almacenes de caché son:
- CACHESTORE_OBJCP: estos son “Planes de objetos”: procedimientos almacenados, funciones y disparadores. En general, las cosas buenas.
- CACHESTORE_SQLCP: estos son “Planes SQL”: declaraciones SQL ad-hoc (incluidas las parametrizadas) y declaraciones preparadas. Esto es lo que buscamos.
- CACHESTORE_PHDR: los llamados “árboles enlazados” para vistas, restricciones y valores predeterminados. Irrelevante para el problema discutido.
- CACHESTORE_XPROC: no son realmente planes de ejecución, sino indicadores de los puntos de entrada de sus SP ampliados.
para que puedas ver selectivamente borrar el SQLCP eliminaría los planes preparados y ad-hoc. Probé esto en mi sistema.
Ejecutó consulta
select objtype,
count(*) as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts) as avg_use_count
from sys.dm_exec_cached_plans
--where objtype='adhoc'
group by objtype
Y la salida fue
Puedes ver la imagen tiene 1264 planes ad-hoc y 69 declaraciones preparadas.
Ahora borro selectivamente el SQLCP usando DBCC FREESYSTEMCACHE('SQL Plans') y vuelva a ejecutar la consulta nuevamente, lo que me dio el siguiente resultado
Ahora puedes ver que los planes ad-hoc y preparados son 2 y 6 respectivamente. Mientras que otros NO se ven afectados.
DECLARE @plan_handle varbinary(64)
DECLARE db_cursor CURSOR FOR
SELECT plan_handle
FROM sys.dm_exec_cached_plans
WHERE objtype = 'Adhoc' -- and usecounts = 1 -- optional: just delete the ones that are used only once
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @plan_handle
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC FREEPROCCACHE (@plan_handle);
FETCH NEXT FROM db_cursor INTO @plan_handle
END
CLOSE db_cursor
DEALLOCATE db_cursor
Sección de Reseñas y Valoraciones
Puedes añadir valor a nuestra información añadiendo tu experiencia en los informes.