Parameterization en SQL Server 2005/2008

Uno de los clientes para los que labore posee una base de datos central completamente transaccional. Esta base de datos es leída por una aplicación web, la cual posee una capa intermedia de servidores de aplicación los cuales se conectan directamente a las bases de datos.

En este punto podemos diferenciar 3 niveles: web, aplicación y bases de datos. Las correctas practicas de programación nos llevarían a mantener la mayor parte del código dentro de la base de datos para de esta forma obtener un mejor performance disminuyendo la cantidad de información que viaja por la red, en este caso a través de los 3 niveles ya explicados anteriormente.

Sin embargo por una serie de antiguas practicas de programación incorrectas los desarrolladores mantienen mas del 70% de código fuente en las rutinas de .NET en los servidores de aplicación.

Esto no se convirtió en un problema real en cuanto el nivel de transacciones no era tan alto como lo ha llegado a ser, al igual como el tamaño y la complejidad de las bases de datos ha alcanzado atreves del tiempo.

Una de las políticas de la compañía es no hacer cambios en el código y en las bases de datos de Producción fuera de los “Service Packs” o cambios de versión de las aplicaciones, por lo que podrán suponer que cada “Service Pack” conlleva una gran serie de actualizaciones que son liberadas en el ambiente de Producción al mismo tiempo.

Habiendo aclarado el tipo de ambiente, practicas de programación y alguna de las políticas para los deployments podre llegar al punto que deseo exponer en este blog; una vez, un día después de la liberación de uno de los “Service Packs”, un lunes, los clientes comenzaron a contactar a servicio al cliente quejándose que nadie podía ingresar a ninguna de las aplicaciones, al aparecer la pantalla de LOGIN y darle aceptar, nada sucedía hasta obtener un time-out como respuesta.

Inmediatamente tuve que ingresar a los servidores de bases de datos y me lleve la desagradable sorpresa que el CPU de uno de ellos estaba al 100% de utilización, solo me preguntaba que cambio pudo venir con el Service Pack que estuviera ocasionando este extraño comportamiento!!

Después de un tiempo capturando traces, obteniendo información de las DMV’s entre otras herramientas llegamos a la conclusión que el nivel de compilamiento era muy superior al mostrado históricamente antes del cambio de versión, pero entre tantos cambios que vinieron con este Service Pack hubiéramos tenido que tener la aplicación abajo por demasiado tiempo hasta encontrar los statements responsables de semejante horror.

Dos cosas se hicieron para solucionar este problema, primero; por medio del análisis de los traces llegamos a la conclusión que había que crear índices en un par de tablas que los necesitaban desesperadamente y segundo; al saber que la mayoría de las ejecuciones eran tipo BATCH, ya que las llamadas del código están en los servidores de aplicación cambiamos el tipo de PARAMETERIZATION de simple a forced.

Comando:

ALTER DATABASE <DB_NAME> SET PARAMETERIZATION FORCED

Que es lo que esta opción de base de datos realiza?

Parametriza todos los valores que vienen con los selects, updates, deletes e inserts que sean tipo batch, individualmente, para que estos no sean recompilados cada vez que son ejecutados. O sea los mantiene a nivel de memoria.

Lógicamente esta opción no aplica para todos los statements que estén dentro de los procedimientos almacenados ya que estos son compilados efectivamente por el motor de base de datos.

 También hay otras situaciones donde la parametrizacion no aplica, pueden acceder a estas ingresando al siguiente link.

https://technet.microsoft.com/en-us/library/ms175037.aspx

Al aplicar estos cambios el grado de recompilamiento bajo significativamente y junto a los índices creados el CPU volvió a niveles normales de entre 20% y 30%, y todos los usuarios pudieron felizmente ingresar de nuevo a las aplicaciones.

Conclusión

Antes de finalizar este tema sugiero siempre recomendar a los desarrolladores mantener todo el código posible siempre dentro del motor de base de datos y cuando nos topamos con ambientes como el expuesto aquí recomiendo hacer el cambio de la parametrizacion a “FORCED” antes de que ocurran los desastres.