La versión DBA de un XKCD classic.

Cuando identifique que el rastreo de parámetros es un problema, debe probar si implementar sugerencias de recompilación ayudará a planificar la calidad. (¿No está seguro de qué parámetro es el olfateo? Aprende de esta publicación de blog o de este video gratuito de 30 minutos.) Debes decidir: ¿qué pista o comando usas y dónde lo pones? ¿Qué compensaciones está haciendo cuando se trata de poder ajustar el rendimiento de su servidor SQL en el futuro?

Esta publicación se ejecuta a través de opciones comunes que tiene que empujar (o golpear) SQL Server para generar un nuevo plan de ejecución. Daré algunos pros y contras para cada método y explicaré lo que es útil y lo que vale la pena evitar.

Para mantener las cosas relativamente simples, solo estoy discutiendo cómo se aplica esto a los procedimientos almacenados hoy: esta publicación no cubre otras formas de consultas parametrizadas (o no parametrizadas). Si tienes grandes preguntas en esas áreas, siéntete libre de sugerirlo para una publicación futura en los comentarios.

Descargo de responsabilidad: Las sugerencias de recompilación pueden matar tu rendimiento al encender tus CPU en llamas cuando se usan incorrectamente. ¡Maneje con cuidado!

Útiles Vistas de administración dinámica

Cuando hablo de impacto en la caché del plan de ejecución, me referiré a dos DMV:

• sys.dm_exec_query_stats: Este DMV es útil para ver las instrucciones principales en su SQL Server, independientemente de si forman parte de un procedimiento o no. Echa un vistazo a una consulta de ejemplo aquí.
• sys.dm_exec_procedure_stats – Este DMV solo está disponible en SQL Server 2008 y versiones posteriores. Realiza un seguimiento de las métricas de ejecución de los procedimientos almacenados. Consulte una consulta de ejemplo en Libros en línea.

Para ambos DMV, tener un plan de ejecución en la caché está vinculado a poder ver métricas de ejecución: número de ejecuciones totales, CPU total y media, lecturas lógicas, etc. Cuando se elimina un plan de ejecución de la caché debido a recompilación, presión de memoria, reinicio u otras acciones, también se eliminan las métricas de ejecución relacionadas.

Ejecutar un procedimiento con una sugerencia de recompilación en tiempo de ejecución

Una opción que me encanta para realizar pruebas rápidas y fáciles es la capacidad de llamar a un procedimiento almacenado con una sugerencia de recompilación en tiempo de ejecución. Esto es genial porque no tienes que cambiar ningún código compilado. Tu sugerencia también se aplica a lo que estás haciendo en tiempo de ejecución.

Para hacer esto, solo usa sintaxis como esta:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. Aún puede obtener el rastreo de parámetros en cualquier llamada a subprocesos dentro del procedimiento almacenado en sí.

Así que, aunque a veces me gusta esto para probar, si tiene algún anidamiento en sus procedimientos almacenados, puede que no le ayude a obtener un plan de ejecución verdaderamente fresco.

Usando el Procedimiento almacenado del sistema sp_recompile

Esto a veces es útil si necesita intervenir durante un incidente de producción si tiene un mal plan utilizado repetidamente por las sesiones entrantes. You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

Cuando se ejecuta, los planes relacionados no se eliminan inmediatamente de la caché del plan de ejecución de SQL Server. En su lugar, la magia ocurre la próxima vez que se ejecutan consultas que hacen referencia al objeto recompilado. En ese momento, las estadísticas de ejecución existentes en sys. dm_exec_query_stats se restablecerán para las instrucciones del plan.

Los recuentos de ejecución en sys. dm_exec_procedure_stats no se restablecerán necesariamente en el siguiente uso si marca una tabla utilizada por un procedimiento almacenado para la recompilación. Pero si marca el procedimiento almacenado para la recompilación, se restablecerá en el siguiente uso. (No te molestes en tratar de memorizar esto, solo lee el siguiente párrafo.)

Este tiene grandes desventajas. Este comando requiere permisos altos: el usuario que lo ejecuta requiere permisos ‘alter’ en la tabla. También requiere un alto nivel de bloqueo para completarlo. En sistemas ocupados he visto a este comando participar en algunas cadenas de bloqueo desagradables. ¡Ten cuidado con él!

Aunque esto puede ser útil ocasionalmente para solucionar problemas y realizar pruebas, no lo convierta en parte del código de producción.

RECOMPILE sugerencias en encabezados de procedimientos almacenados

Cuando tenga procedimientos que desee generar planes nuevos, comience a buscar cómo usar las sugerencias dentro del procedimiento. La opción que la mayoría de la gente descubre primero es usar ‘WITH RECOMPILE’ en el encabezado del procedimiento almacenado, de esta manera:

El uso de RECOMPILAR en el encabezado del procedimiento almacenado es bastante drástico: el procedimiento no almacenará en caché un plan de ejecución cuando se ejecute. Esto significa:

• No hay planes de ejecución en caché para revisar
• No hay estadísticas de ejecución registradas en sys. dm_exec_query_stats
• No hay estadísticas de ejecución registradas en sys.dm_exec_procedure_stats

Wow, eso es un fastidio. Si necesita identificar el impacto que este procedimiento está teniendo en su servidor, debe ejecutar algún tipo de sesión de seguimiento o eventos extendidos y cosechar e interpretar los resultados. Eso no es rápido, y los rastros en ejecución pueden afectar el rendimiento.

Un gran negativo. Por esa razón, no me gusta mucho esta elección.

RECOMPILAR sugerencias en sentencias individuales

Esta opción es un poco más de trabajo, pero tiene una rentabilidad mucho mejor con el tiempo. With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

Incluso si necesita usar la sugerencia en todas las instrucciones del procedimiento en lugar de en el encabezado, ¡esto todavía tiene beneficios! Al poner la pista de recompilación en el nivel de instrucción en el proc, mágicamente obtienes:

• Planes de ejecución limitados en caché para revisar (última ejecución)
• Estadísticas de ejecución limitadas registradas en sys.dm_exec_query_stats. Solo obtendrás estadísticas de la última ejecución, pero la columna plan_generation_num se incrementará, al menos dándote una idea de que algo está pasando.
• Estadísticas de ejecución registradas en sys. dm_exec_procedure_stats

El hecho de que obtenga alguna información en estos DMV puede ser súper útil con el tiempo. Aplicar sugerencias de recompilación solo a las instrucciones que las necesitan también es más responsable: reduce el riesgo de quemarse con el tiempo con la quema de CPU.

DBCC FREEPROCCACHE-la opción nuclear

Esto no es estrictamente una sugerencia de recompilación– pero ciertamente causa recompilación. SQL Server tiene un comando que puede usar para decirle: «Comience de nuevo con planes de ejecución nuevos». Por lo general se usa así:

This command makes everything start fresh– for every query:

• El uso de CPU puede aumentar a medida que se compilan nuevos planes
• Las estadísticas de ejecución se borran en sys.dm_exec_query_stats (inmediatamente)
• Las estadísticas de ejecución se borran en sys.dm_exec_procedure_stats (inmediatamente)

Este comando no es malo: hay un momento y un lugar en la resolución de problemas cuando se lo usa puede ayudarlo a llegar rápidamente a la causa raíz de un problema de rendimiento. Sin embargo, dado que afecta a toda la caché y también puede afectar al rendimiento, debe tener mucho cuidado al usarla. NO recomiendo usar este comando en trabajos regulares, procesos automatizados o código de producción.

A partir de SQL Server 2008, puede usar este comando con un poco más de cuidado: puede eliminar un plan específico de la caché utilizando el» controlador de plan «o el» controlador sql», pero, por supuesto, debe averiguar qué es ese controlador y por qué desea eliminarlo. Esto podría ser útil en algunas situaciones de nicho, pero en la práctica no surge mucho. También puede borrar un grupo de gobernadores de recursos, pero, bueno, tendría que usar el gobernador de recursos.

¿Qué significa todo esto?

Cuando esté haciendo pruebas, hay un lugar en su kit de herramientas para ‘procedimiento EXEC CON RECOMPILE’ y el procedimiento sp_recompile. Úsalos con cuidado y cuidado con posibles repercusiones, particularmente con sp_recompile.

Cuando esté implementando sugerencias en código, no tenga puestas las anteojeras de RECOMPILACIÓN – a veces también puede usar sugerencias de «OPTIMIZAR PARA» con éxito (aunque a veces esté optimizando para mediocres). Y, a veces, el sql dinámico también puede ayudarlo.

Pero si utiliza sugerencias de RECOMPILACIÓN, mantenga sus recompilaciones en el nivel de instrucción, y no en los encabezados de sus procedimientos.

Para obtener más información, consulte nuestra clase Fundamentos de Detección de parámetros.