DBCC FREEPROCCACHE es un comando que se encarga de borrar todos los planes de ejecución cacheados en la instancia (o uno en concreto si se especifica su handle específico). Este comando debe ser visto como lo que es, un comando para entornos de pruebas, pero nunca como un recurso a tener en cuenta en un entorno de producción.

Generalmente cuando alguien lee sobre este comando, acaba leyendo algo como lo que he comentado en el primer párrafo de este post, pero como siempre vale mas ver en nuestras carnes qué ocurre, que leerlo…te recomiendo que leas esto para que veas lo que ocurre en un sistema de producción cuando lo lanzamos.

En uno de mis últimos clientes, se estaba sufriendo un problema de parameter sniffing (motivo de otro post) por el cual, planes de ejecución compilados se iban degradando con el paso de las horas-días hasta llegar un momento en el que el sistema casi por completo dejaba de ser estable y comenzaba a dar tiempos de respuesta muy altos. Se dieron cuenta que lanzando el comando DBCC FREEPROCCACHE el rendimiento comenzaba a mejorar y decidieron utilizarlo como una estrategia cuando llegaba el momento, sin pararse a pensar en las consecuencias.

El problema del parameter snifing en SQL Server tiene diversas formas de ser resuelto, y pese a que este post no quiero destinarlo a ello, si que quiero dejar patente que DBCC FREEPROCCACHE no es una de ellas.

Para ver un efecto de lo que ocurre en un sistema productivo al lanzar dicho comando DBCC; a nivel CPU se borran los planes de ejecución y la consecuencia es que cada nueva query deberá ser compilada para regenerar su nuevo plan de ejecución. En el escenario del que os hablo, este proceso está durante 10 minutos duplicando prácticamente x2 el consumo de CPU:

1

*Siendo 03 el día, 15 la hora y la línea de números de 13 a 42 los minutos

El buffer caché hit ratio además baja abruptamente (siendo nada bueno que esté por debajo del 99.98%, vemos cómo baja sustancialmente de ese límite):

2

*Siendo 03 el día, 15 la hora y la línea de números de 00 a 32 los minutos

 

Y luego finalmente, un efecto colateral derivado de ello es que los latch a estructuras de memoria se incrementan hasta niveles exponenciales, con tiempos de acceso a latch de estructuras en RAM de más de 10s.

 

3

*Siendo 03 el día, 15 la hora y la línea de números de 00 a 32 los minutos

En este cliente ocurría un problema de mal alineamiento hw que producía unas latencias excesivas de media, este comando simplemente lo agrava. Lo normal es que no aparezca esto en un entorno «sano» a nivel de configuración HW ni E/S

En un entorno de producción todo lo anterior se traduciría como un abrupto descenso de rendimiento que durante unos minutos produce que las aplicaciones funcionen alarmantemente lentas, y que sin saber «por qué» vuelven a la normalidad. Esa vuelta a la normalidad obviamente se deberá a que una vez recompilados los nuevos planes de ejecución, el sistema funcionará con normalidad…hasta que el problema de parameter sniffing (que no hemos solucionado) vuelva a hacerse notar.

En este caso concreto la degradación era tan rápida que habia días que se tenía que lanzar el comando incluso cada 3-4 horas…

 

Enrique Catalá

Microsoft Data Platform MVP & Mentor at SolidQ
I am Mentor and Microsoft Data Platform MVP at SolidQ. I am Microsoft Certified Trainer (MCT) andfocused in SQL Server motor relation, where i have successfully led more than 100 projects, not just in Spain, but also in EEUU,Mexico, Austria, etc.

I am the main architect of SolidQ Solutions called HealthCheck, SQL2Cloud, SCODA and SolidQ SSIS Generator. Appart from that, I am regular speaker of SolidQ Summit.
Enrique Catalá