transact - with recompile sql server
Regla de oro sobre cuándo usar la opción WITH RECOMPILE (5)
El uso más común es cuando puede tener una cláusula WHERE dinámica en un procedimiento ... no le gustaría que ese plan de consulta en particular se compile y guarde para las ejecuciones posteriores porque muy bien podría no ser la misma cláusula la próxima vez el procedimiento se llama.
Entiendo que la opción WITH RECOMPILE obliga al optimizador a reconstruir el plan de consulta para los procesos almacenados, pero ¿cuándo le gustaría que sucediera?
¿Cuáles son algunas reglas generales sobre cuándo usar la opción WITH RECOMPILE y cuándo no?
¿Cuál es la sobrecarga efectiva asociada con solo ponerlo en cada sproc?
Solo se debe usar cuando las pruebas con datos reprentativos y el contexto demuestren que hacerlo sin producir planes de consulta no válidos (cualesquiera que sean las posibles razones). No asuma de antemano (sin pruebas) que un SP no se optimizará adecuadamente.
Exclusión única para la invocación manual únicamente (es decir, no la codifique en el SP): cuando sepa que ha alterado sustancialmente el carácter de las tablas de destino. por ejemplo, TRUNCATE, cargas a granel, etc.
Es otra oportunidad para la optimización prematura.
Nota: Tengo muchos puntos. Si un usuario nuevo envía la misma respuesta a continuación, y usted acepta, votó a favor de ellos.
Como han dicho otros, no desea simplemente incluir WITH RECOMPILE en cada proceso almacenado como una cuestión de hábito. Al hacerlo, eliminaría uno de los principales beneficios de los procedimientos almacenados: el hecho de que guarda el plan de consulta.
¿Por qué es potencialmente un gran problema? La computación de un plan de consulta es mucho más intensiva que la compilación de código de procedimiento regular. Debido a que la sintaxis de una declaración SQL solo especifica lo que desea, y no (generalmente) cómo obtenerla, eso le permite a la base de datos un amplio grado de flexibilidad cuando crea el plan físico (es decir, las instrucciones paso a paso para reunir y modificar datos). Hay muchos "trucos" que el preprocesador de consulta de bases de datos puede hacer y las elecciones que puede realizar: qué orden unir las tablas, qué índices usar, si se deben aplicar cláusulas WHERE antes o después de las uniones, etc.
Para una instrucción SELECT simple, puede no hacer una diferencia, pero para cualquier consulta no trivial, la base de datos va a pasar un tiempo serio (medido en milisegundos, en oposición a los microsegundos habituales) para llegar a un plan óptimo. Para consultas realmente complejas, ni siquiera puede garantizar un plan óptimo , solo tiene que usar heurística para llegar a un plan bastante bueno . Así que forzándolo a recompilar cada vez, le estás diciendo que tiene que pasar por ese proceso una y otra vez, incluso si el plan que tenía antes era perfectamente bueno.
Dependiendo del proveedor, debe haber desencadenantes automáticos para recompilar planes de consulta; por ejemplo, si las estadísticas en una tabla cambian significativamente (como que el histograma de valores en una determinada columna comienza distribuyéndose uniformemente por el tiempo se torna muy distorsionado), entonces el DB debe notar eso y recompilar el plan. Pero en general, los implementadores de una base de datos van a ser más inteligentes sobre eso en conjunto de lo que eres.
Al igual que con cualquier actuación relacionada, no tome fotos en la oscuridad; averigüe dónde están los cuellos de botella que están costando el 90% de su rendimiento, y resuélvalos primero.
Ponerlo en cada procedimiento almacenado NO es una buena idea, porque compilar un plan de consulta es una operación relativamente costosa y no verá ningún beneficio de los planes de consulta almacenados en caché y reutilizados.
El caso de una cláusula dynamic where donde se creó un procedimiento almacenado se puede manejar utilizando sp_executesql para ejecutar el TSQL en lugar de agregar WITH RECOMPILE al procedimiento almacenado.
Otra solución (SQL Server 2005 en adelante) es usar una pista con parámetros específicos utilizando la sugerencia OPTIMIZE FOR. Esto funciona bien si los valores en las filas son estáticos.
SQL Server 2008 ha introducido una característica poco conocida llamada " OPTIMIZE FOR UNKNOWN ":
Esta sugerencia le indica al optimizador de consultas que use los algoritmos estándar que siempre ha usado si no se han pasado los valores de los parámetros a la consulta. En este caso, el optimizador examinará todos los datos estadísticos disponibles para llegar a una determinación de cuáles deberían ser los valores de las variables locales utilizadas para generar el plan de consulta, en lugar de mirar los valores de parámetros específicos que la aplicación pasó a la consulta.
en general, una alternativa mucho mejor que WITH RECOMPILE es OPTION(RECOMPILE) como puede ver en la explicación a continuación, tomada de la respuesta a esta pregunta aquí
Cuando se encuentra un problema de sensibilidad a los parámetros, un consejo común en los foros y sitios de preguntas y respuestas es "usar recompilación" (suponiendo que las otras opciones de ajuste presentadas anteriormente no son adecuadas). Desafortunadamente, ese consejo a menudo se malinterpreta para significar agregar la opción WITH RECOMPILE al procedimiento almacenado.
El uso de WITH RECOMPILE nos devuelve al comportamiento de SQL Server 2000, donde se recompila todo el procedimiento almacenado en cada ejecución. Una mejor alternativa, en SQL Server 2005 y posterior, es utilizar la sugerencia de consulta OPCIÓN (RECOMPRA) solo en la declaración que sufre el problema de detección de parámetros. Esta sugerencia de consulta resulta en una recompilación de la declaración problemática solamente; los planes de ejecución para otras declaraciones dentro del procedimiento almacenado se guardan en la memoria caché y se vuelven a usar de forma normal.
El uso de WITH RECOMPILE también significa que el plan compilado para el procedimiento almacenado no se almacena en caché. Como resultado, no se mantiene información de rendimiento en los DMV como sys.dm_exec_query_stats. El uso de la sugerencia de consulta en su lugar significa que un plan compilado se puede almacenar en caché, y la información de rendimiento está disponible en el DMV (aunque está limitada a la ejecución más reciente, solo para la declaración afectada).
Para las instancias que ejecutan al menos SQL Server 2008 compilación 2746 (Service Pack 1 con actualización acumulativa 5), el uso de OPTION (RECOMPILE) tiene otra ventaja significativa sobre WITH RECOMPILE: solo OPTION (RECOMPILE) habilita la optimización de incrustación de parámetros.