LA OPCIÓN (RECOMPILAR) es Siempre Más Rápida; ¿Por Qué?

A menudo, cuando hay una diferencia drástica de ejecución a ejecución de una consulta, encuentro que a menudo es uno de los 5 problemas.

1. ESTADÍSTICAS - Las estadísticas están desactualizadas. Una base de datos almacena estadísticas sobre el rango y la distribución de los tipos de valores en varias columnas en tablas e índices. Esto ayuda al motor de consultas a desarrollar un "Plan" de ataque sobre cómo realizará la consulta, por ejemplo, el tipo de método que utilizará para hacer coincidir las claves entre tablas utilizando un hash o buscando a través de todo el set. Puede llamar a Estadísticas de actualización en toda la base de datos o solo en ciertas tablas o índices. Esto ralentiza la consulta de una ejecución a otra porque cuando las estadísticas están desactualizadas, es probable que el plan de consulta no sea óptimo para los datos recién insertados o modificados para la misma consulta (se explica más adelante). Puede no ser apropiado actualizar las estadísticas inmediatamente en una base de datos de Producción, ya que habrá algunos gastos generales, ralentización y retraso dependiendo de la cantidad de datos a muestra. También puede optar por utilizar un Análisis completo o un Muestreo para actualizar las estadísticas. Si observa el Plan de consulta, también puede ver las estadísticas de los índices en uso utilizando el comando DBCC SHOW_STATISTICS (tablename, indexname) . Esto le mostrará la distribución y los rangos de las claves en las que el plan de consulta está utilizando para basar su enfoque.

2. BÚSQUEDA DE PARÁMETROS : El plan de consulta que se almacena en caché no es óptimo para los parámetros particulares que están pasando, a pesar de que la consulta en sí no ha cambiado. Por ejemplo, si pasa un parámetro que solo recupera 10 de las 1.000.000 filas, el plan de consulta creado puede usar una combinación Hash; sin embargo, si el parámetro que pasa usará 750.000 de las 1.000.000 filas, el plan creado puede ser un análisis de índice o de tabla. En tal situación, puede decirle a la instrucción SQL que use la opción OPCIÓN (RECOMPILE) o un SP para usar CON RECOMPILE. Para decirle al Motor esto es un " Single Usar Plan " y no usar un Plan en caché que probablemente no se aplique. No hay una regla sobre cómo tomar esta decisión, depende de conocer la forma en que los usuarios usarán la consulta.

3. INDEXES - Es posible que la consulta no haya cambiado, pero un cambio en otro lugar, como la eliminación de un índice muy útil, ha ralentizado la consulta.

4. ROWS CHANGED - Las filas que está consultando cambian drásticamente de una llamada a otra. Por lo general, las estadísticas son actualizado automáticamente en estos casos. Sin embargo, si está creando SQL dinámico o llamando a SQL dentro de un bucle cerrado, existe la posibilidad de que esté utilizando un Plan de consulta obsoleto basado en el número drástico incorrecto de filas o estadísticas. De nuevo en este caso LA OPCIÓN (RECOMPILAR) es útil.

5. LA LÓGICA Es la Lógica, su consulta ya no es eficiente, estaba bien para un pequeño número de filas, pero ya no escala. Esto generalmente implica un análisis más profundo de la Plan de Consulta. Por ejemplo, ya no puedes hacer cosas a granel, sino que tienes que fragmentar las cosas y hacer confirmaciones más pequeñas, o tu Producto cruzado estaba bien para un conjunto más pequeño pero ahora ocupa CPU y Memoria a medida que se escala más grande, esto también puede ser cierto para usar DISTINCT, estás llamando a una función para cada fila, tus coincidencias de clave no usan un índice debido a la conversión de tipo de FUNDICIÓN o NULOS o funciones... Hay demasiadas posibilidades aquí.

En general, cuando escribe una consulta, debe tener alguna imagen mental de aproximadamente cómo se distribuyen ciertos datos dentro de su tabla. Una columna, por ejemplo, puede tener un número distribuido uniformemente de valores diferentes, o puede ser sesgada, el 80% de las veces tiene un conjunto específico de valores, ya sea que la distribución varíe con frecuencia con el tiempo o sea bastante estática. Esto le dará una mejor idea de cómo construir una consulta eficaz. Pero también cuando el rendimiento de la consulta de depuración tienen una base para construir una hipótesis de por qué es lento o ineficiente.

Para agregar a la excelente lista (dada por @ CodeCowboyOrg) de situaciones donde la OPCIÓN (RECOMPILE) puede ser muy útil,

1. Variables de la tabla . Cuando se utilizan variables de tabla, no habrá estadísticas predefinidas para la variable de tabla, lo que a menudo conduce a grandes diferencias entre las filas estimadas y reales en el plan de consulta. El uso de la OPCIÓN (RECOMPILAR) en consultas con variables de tabla permite la generación de un plan de consulta que tiene una estimación mucho mejor de la fila números involucrados. Tuve un uso particularmente crítico de una variable de tabla que era inutilizable, y que iba a abandonar, hasta que agregué la OPCIÓN(RECOMPILAR). El tiempo de ejecución pasó de horas a solo unos minutos. Eso es probablemente inusual, pero en cualquier caso, si está utilizando variables de tabla y trabajando en la optimización, vale la pena ver si OPTION(RECOMPILE) hace una diferencia.

Las primeras acciones antes de afinar las consultas es desfragmentar/reconstruir los índices y estadísticas, de otra manera estás perdiendo el tiempo.

Debe comprobar el plan de ejecución para ver si es estable (es el mismo cuando cambia los parámetros), si no, es posible que tenga que crear un índice de cobertura (en este caso para cada tabla) (sabiendo que el sistema puede crear uno que sea útil para otras consultas también).

Como ejemplo : crear índice idx01_datafeed_trans En datafeed_trans ( feedid, Fecha de alimentación) INCLUDE (acctNo, tradeDate)

Si el plan es estable o puede estabilizarlo, puede ejecutar la oración con sp_executesql('sql sentence') para guardar y usar un plan de ejecución fijo.

Si el plan es inestable, debe usar una instrucción ad-hoc o EXEC('sentencia sql') para evaluar y crear un plan de ejecución cada vez. (o un procedimiento almacenado "con recompile").

Espero que ayude.