Parametrización Forzada en SQL Server

Hoy te voy a hablar de una opción muy interesante que puede mejorar el rendimiento de tus consultas: la parametrización forzada. ¿Qué es esto y cómo funciona? Te lo explico en este artículo.

Como ya sabes, si has leído mis artículos anteriores sobre planes de ejecución, caché de planes y optimización para consultas ad hoc, SQL Server genera un plan de ejecución óptimo para cada consulta que recibe, basándose en las estadísticas, los índices y otros factores. Este plan se almacena en la caché de planes para reutilizarlo si se vuelve a ejecutar la misma consulta. Sin embargo, hay casos en los que la misma consulta puede tener diferentes valores de parámetros, lo que hace que el plan óptimo varíe. Por ejemplo, si tenemos una consulta que busca los clientes de una ciudad determinada, el plan óptimo puede cambiar según la ciudad que se especifique.

¿Qué es la parametrización?

En estos casos, SQL Server tiene dos opciones: generar un nuevo plan para cada valor de parámetro diferente (lo que se llama parametrización simple) o generar un plan genérico que sirva para todos los valores posibles (lo que se llama parametrización forzada). La primera opción tiene la ventaja de que el plan se adapta al valor concreto, pero tiene el inconveniente de que consume más recursos y puede llenar la caché de planes con planes similares. La segunda opción tiene la ventaja de que ahorra recursos y evita la fragmentación de la caché de planes, pero tiene el inconveniente de que el plan puede no ser el más adecuado para algunos valores.

¿Qué es la parametrización forzada?

La parametrización forzada hace que SQL Server trate todas las consultas como si fueran procedimientos almacenados con parámetros, y genera un plan genérico para cada consulta, independientemente del valor de los parámetros. Esto puede mejorar el rendimiento en algunos casos, pero también puede empeorarlo en otros. Por eso hay que tener cuidado al usar esta opción y hacer pruebas antes de aplicarla en un entorno productivo.

¿Es mejor la parametrización forzada?

Entonces, ¿cuál es la mejor opción? Depende. No hay una respuesta única, sino que depende del tipo de consultas, de la distribución de los datos, del tamaño de la base de datos y de otros factores. Lo ideal sería poder elegir la opción más conveniente para cada consulta, pero eso no es posible con la parametrización simple. Por eso existe la parametrización forzada, que nos permite activar esta opción a nivel de base de datos o a nivel de consulta individual, mediante una directiva o una sugerencia (HINT).

¿Cuándo activar la parametrización forzada?

Hemos visto que la parametrización forzada puede ayudarnos a reducir recursos al reutilizar los planes de ejecución de las consultas aunque cambien los parámetros. Imagina que tenemos una base de datos que da servicio a una aplicación web con miles o millones de usuarios lanzando la misma consulta, cada uno para sus datos. Esto nos va a generar miles de millones de planes de ejecución, en su mayoría iguales que van a consumir recursos para generarse teniendo ya otro igual guardado y que van a llenar la caché.

Sin embargo esto puede perjudicar mucho el rendimiento si la distribución de nuestros datos no es uniforme. Pero no todo está perdido, a partir de SQL 2019, con los joins dinámicos este problema se reduce en gran medida. 

¿Cómo activo la parametrización forzada?

Si llegado a este punto crees que esta solución es para ti, has detectado muchas recompilaciones que están llenando tu caché, has visto que tus consultas invierten mucho tiempo en compilar planes o simplemente quieres hacer pruebas vamos a ver como activarlo. Como hemos dicho además de con un HINT en cada consulta podemos activar la parametrización forzada por base de datos. Podremos hacerlo desde SSMS, en la pestaña opciones de las propiedades de nuestra base de datos o con el siguiente script:

USE [master]
GO
ALTER DATABASE [MiBaseDeDatos] SET PARAMETERIZATION FORCED
GO

Una vez habilitado, debería bajar el consumo de CPU de nuestro SQL, si, por el contrario, no es así deberás volver atrás y pensar en otras soluciones como la optimización para consultas Ad Hoc o analizar más a fondo con Query Store tus planes de ejecución.

Conclusión

En este artículo te he explicado qué es la parametrización forzada y cómo funciona. También te he mostrado las ventajas y los inconvenientes de esta opción y cómo puedes activarla o desactivarla según tus necesidades. Espero que te haya resultado útil y que lo pongas en práctica. Recuerda que puedes dejarme tus comentarios o preguntas al final del artículo, en Twitter o por mail. Hasta la próxima.