Siendo el día 12 del mes del Comando DBCC en SteveStedman.com, el comando DBCC destacado de hoy es DBCC FREEPROCCACHE.
Descripción:
DBCC FREEPROCCACHE se utiliza para purgar todos los planes de consulta analizados sin memoria. Esto se usa comúnmente en entornos de desarrollo, pero no es tan común en un entorno de producción.
El uso en un entorno de desarrollo es común, por ejemplo, cuando se trabaja en el ajuste del rendimiento o en la parametrización de consultas. Puede borrar la caché de procedimientos con DBCC FreeProcCache, ejecutar el programa o la página web que puede estar utilizando la base de datos y, a continuación, ver qué hay en la caché de procedimientos. Esto puede ser útil para encontrar consultas que necesiten parametrizarse. Otra forma de usarlo sería averiguar qué consultas están siendo ejecutadas por algún programa. Para hacer esto, comenzaría trabajando con una base de datos que no está siendo utilizada por otros, limpiando la caché de procedimientos con DBCC FreeProcCache, luego ejecutaría el programa que está tratando de averiguar, luego miraría lo que hay en la caché, nuevamente esto es algo que se podría hacer en un entorno de desarrollo o prueba, pero no recomendaría hacerlo en producción.
El uso en un entorno de producción debe ser raro, esta es una de las cosas comunes que se deben probar cuando SQL Server tiene dificultades. Si es el punto en el que SQL Server es extremadamente lento para responder y no ha podido encontrar la causa, una cosa a intentar es liberar la caché de procedimientos con DBCC FreeProcCache y ver si eso soluciona el problema.
Sintaxis DBCC FreeProcCache:
dbcc freeproccache
Ejemplo:
El siguiente ejemplo proviene de un entorno de desarrollo utilizando la base de datos AdventureWorks2012.
Primero nos conectamos a AdventureWorks2012 y vemos lo que hay en la caché.
USE AdventureWorks2012;GOSELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Aquí vemos que hay mucho en el caché. A continuación limpiamos la caché con DBCC FreeProcCache y echamos otro vistazo a lo que hay en la caché.
DBCC FREEPROCCACHE;SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
Después de ejecutar DBCC FreeProcCache, puede ver que no queda nada en la caché.
Cuando se ejecute la siguiente consulta, tendrá que ser reparada en lugar de usar una consulta ya analizada en la caché. Esto llevará un poco más de tiempo que si ya hubiera un plan analizado para ejecutarse. Ejecutemos 3 consultas, luego echemos un vistazo a la caché.
GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'John';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Mary';GOSELECT FirstName, MiddleName, LastName FROM person.Person p WHERE FirstName like 'Bill';
Observe la instrucción GO entre cada consulta. Esto indica a los SSM que ejecuten cada consulta como un lote separado. Sin la instrucción GO, las 3 consultas se habrían analizado como un solo lote.
Aquí vemos los resultados de las tres consultas. Los dos primeros devolvieron resultados, y el tercero no tenía filas en el conjunto de resultados. Ahora echemos un vistazo a la caché
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st;
A continuación vemos un total de 20 elementos en la caché ahora. el elemento superior resaltado en azul es la consulta que usamos para ver lo que había en la caché, el segundo bloque resaltado en rojo contiene las 3 consultas de arriba, y el tercer resto son consultas ejecutadas por SQL u otras consultas de soporte. Por ejemplo, la línea 13 es el dm_exec_sql_text que se llama desde la consulta anterior que comprueba el plan.
Si quisiéramos filtrar esto solo a las consultas que habíamos escrito, podría hacerlo agregando una cláusula WHERE text LIKE … a la consulta como se muestra aquí.
SELECT size_in_bytes, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Aquí vemos que solo aparecen las tres consultas, y que cada una de esas tres ocupa aproximadamente 40K de memoria en SQL Server. 
Entonces, por qué hay tres copias de la misma instrucción SELECT, esto parece un poco derrochador. De hecho, para obtener más información, consulte una publicación anterior llamada ¿Cuánta memoria caché de procedimientos ocupa una instrucción SQL? Hay maneras de corregir esto.
Uso de DBCC FreeProcCache Para Un Controlador de Plan específico
Si desea borrar solo un controlador de plan, y no todos los controladores de plan, puede usar el parámetro opcional llamado @ handle.
Para obtener el identificador del plan, comenzamos modificando nuestra consulta anterior para mostrarnos lo que hay en la caché del plan. Podría omitir la cláusula WHERE en su propio sistema, pero la tengo aquí para mostrarnos solo las tres consultas en cuestión desde arriba.
SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Aquí vemos los mismos tres planes de consulta anteriores, con una columna de suma llamada plan_handle. Para liberar un único controlador de plan, simplemente copiaríamos el controlador de plan numérico y lo añadiríamos a la consulta DBCC FreeProcCache.
DBCC FREEPROCCACHE(0x060007000100FF3310B8DA7D0600000001000000000000000000000000000000000000000000000000000000);SELECT size_in_bytes, text, plan_handle FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS stWHERE text LIKE N'SELECT FirstName%';
Donde solo vemos 2 de las tres consultas originales en la caché del plan.
¿Qué tamaño tiene mi Caché de procedimientos?
puede ejecutar la siguiente consulta para comprobar el tamaño de la caché de procedimientos.
SELECT count(*) AS NumPlans, objtype as Type,SUM(size_in_bytes)/(1024.0*1024.0) AS size_in_mbFROM sys.dm_exec_cached_plansGROUP BY objtype;
Que produce los siguientes resultados en mi servidor de pruebas.
Informes de estado de la base de datos y la caché del Plan
También puede ver la caché del plan mediante la aplicación Informes de estado de la base de datos, como se muestra aquí.
Notas:
Para obtener más información, consulte TSQL Wiki DBCC freeproccache.
Comando DBCC mes a SteveStedman.com es casi tan divertido como comer gelatina.
Steve y el equipo de Stedman Solutions están aquí para satisfacer todas sus necesidades de SQL Server.
Contáctanos hoy para tu consulta gratuita de 30 minutos..
¡Estamos listos para ayudar!
