with - update hint sql server

OPCIÓN(RECOMPRA) es siempre más rápido; ¿Por qué?

Question

Me encontré con una situación extraña en la que al OPTION (RECOMPILE) a mi consulta, se ejecuta en medio segundo, mientras que OPTION (RECOMPILE) que la consulta OPTION (RECOMPILE) más de cinco minutos.

Este es el caso cuando la consulta se ejecuta desde el Analizador de consultas o desde mi programa C # a través de SqlCommand.ExecuteReader() . Llamar (o no llamar) a DBCC FREEPROCCACHE o DBCC dropcleanbuffers no hace diferencia; Los resultados de la consulta siempre se devuelven instantáneamente con OPTION (RECOMPILE) y más de cinco minutos sin él. La consulta siempre se llama con los mismos parámetros [por el bien de esta prueba].

Estoy usando SQL Server 2008.

Me siento bastante cómodo escribiendo SQL pero nunca he usado un comando OPTION en una consulta anterior y no estaba familiarizado con todo el concepto de cachés de plan hasta que escanee las publicaciones en este foro. Mi comprensión de las publicaciones es que OPTION (RECOMPILE) es una operación costosa. Aparentemente crea una nueva estrategia de búsqueda para la consulta. Entonces, ¿por qué entonces las consultas subsiguientes que omiten OPTION (RECOMPILE) son tan lentas? ¿No deberían las consultas posteriores hacer uso de la estrategia de búsqueda que se calculó en la llamada anterior que incluía la sugerencia de recompilación?

¿Es altamente inusual tener una consulta que requiera una sugerencia de recompilación en cada llamada?

Perdón por la pregunta de nivel de entrada, pero realmente no puedo hacer cara o cruz de esto.

ACTUALIZACIÓN: me han pedido que publique la consulta ...

select acctNo,min(date) earliestDate 
from( 
    select acctNo,tradeDate as date 
    from datafeed_trans 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_money 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_jnl 
    where feedid=@feedID and feedDate=@feedDate 
)t1 
group by t1.acctNo
OPTION(RECOMPILE)

Cuando ejecuto la prueba desde el Analizador de consultas, anexo las siguientes líneas:

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate=''1/2/2009''

Cuando lo llamo desde mi programa C #, los parámetros se pasan a través de la propiedad SqlCommand.Parameters .

A los efectos de esta discusión, puede suponer que los parámetros nunca cambian, por lo que podemos descartar el olor de los parámetros subóptimos como causa.

Answer 1

A menudo, cuando hay una diferencia drástica de ejecución a ejecución de una consulta, creo 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 se realizará la consulta, por ejemplo, el tipo de método que utilizará para unir claves entre tablas utilizando un hash o mirando a través del conjunto completo. Puede llamar Estadísticas de actualización en toda la base de datos o solo 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 a continuación). Puede que no sea adecuado actualizar las estadísticas de inmediato en una base de datos de producción, ya que habrá cierta sobrecarga, ralentización y retraso dependiendo de la cantidad de datos a muestrear. También puede optar por utilizar un escaneo completo o muestreo para actualizar las estadísticas. Si mira el Plan de consulta, también puede ver las estadísticas en los Índices en uso, utilizando el comando DBCC SHOW_STATISTICS (nombre de tabla, nombre de índice) . Esto le mostrará la distribución y los rangos de las claves que usa el plan de consulta para basar su enfoque.

2. SNAPFING DE PARÁMETROS : el plan de consultas que se almacena en caché no es óptimo para los parámetros particulares que está pasando, incluso aunque la consulta en sí no haya cambiado. Por ejemplo, si transfiere un parámetro que solo recupera 10 de cada 1,000,000 filas, entonces el plan de consulta creado puede usar una combinación Hash, sin embargo, si el parámetro que transfiere usará 750,000 de las 1,000,000 filas, el plan creado puede ser un escaneo de índice o escaneo de tabla. En tal situación, puede decirle a la declaración de SQL que use la opción OPCIÓN (RECOMPRA) o un SP para usar CON RECOMPIBLE. Para decirle al motor que este es un "plan de uso único" y no para usar un plan en caché que probablemente no aplique. No existe una regla sobre cómo tomar esta decisión, depende de conocer la forma en que los usuarios usarán la consulta.

3. ÍNDICES : 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. FILAS CAMBIADAS : las filas que está consultando cambian drásticamente de llamada a llamada. Por lo general, las estadísticas se actualizan automáticamente en estos casos. Sin embargo, si está creando SQL dinámico o llamando a SQL en un ciclo 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, OPTION (RECOMPRA) es útil.

5. LA LÓGICA Es la lógica, su consulta ya no es eficiente, estuvo bien para un pequeño número de filas, pero ya no se escala. Esto generalmente implica un análisis más profundo del Plan de Consulta. Por ejemplo, ya no puede hacer cosas a granel, pero tiene que Trozar cosas y cometer Comisiones más pequeñas, o su Producto Cruzado estaba bien para un conjunto más pequeño, pero ahora ocupa CPU y Memoria a medida que aumenta de tamaño, esto también puede ser cierto para utilizando DISTINCT, está llamando a una función para cada fila, sus coincidencias de clave no usan un índice debido a la conversión de tipo CASTING o NULLS o funciones ... 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 uniformemente distribuido de valores diferentes, o puede estar sesgada, el 80% del tiempo tiene un conjunto específico de valores, independientemente de si la distribución variará con el tiempo o será bastante estática. Esto le dará una mejor idea de cómo crear una consulta eficiente. Pero también cuando la depuración del rendimiento de la consulta tiene una base para construir una hipótesis sobre por qué es lento o ineficiente.

Answer 2

Hay momentos en que usar OPTION(RECOMPILE) tiene sentido. En mi experiencia, la única vez que esta es una opción viable es cuando está usando SQL dinámico. Antes de explorar si esto tiene sentido en su situación, recomendaría reconstruir sus estadísticas. Esto se puede hacer ejecutando lo siguiente:

EXEC sp_updatestats

Y luego recrea tu plan de ejecución. Esto asegurará que cuando se cree su plan de ejecución, usará la información más reciente.

Al agregar OPTION(RECOMPILE) reconstruye el plan de ejecución cada vez que se ejecuta la consulta. Nunca escuché que se describió como creates a new lookup strategy pero tal vez solo estamos usando términos diferentes para la misma cosa.

Cuando se crea un procedimiento almacenado (sospecho que está llamando a sql ad-hoc desde .NET pero si está utilizando una consulta parametrizada, esto termina siendo una llamada de proceso almacenada ) SQL Server intenta determinar el plan de ejecución más efectivo para esta consulta basado en los datos en su base de datos y los parámetros pasados ​​( detección de parámetros ), y luego almacena en caché este plan. Esto significa que si crea la consulta donde hay 10 registros en su base de datos y luego la ejecuta cuando hay 100,000,000 de registros, el plan de ejecución en caché puede que ya no sea el más efectivo.

En resumen, no veo ninguna razón para que OPTION(RECOMPILE) sea ​​un beneficio aquí. Sospecho que solo necesita actualizar sus estadísticas y su plan de ejecución. Reconstruir estadísticas puede ser una parte esencial del trabajo de DBA dependiendo de su situación. Si aún tiene problemas después de actualizar sus estadísticas, le sugiero que publique ambos planes de ejecución.

Y para responder a su pregunta, sí, diría que es muy inusual que su mejor opción sea recompilar el plan de ejecución cada vez que ejecuta la consulta.

Answer 3

Las primeras acciones antes de las consultas de ajuste es desfragmentar / reconstruir los índices y las estadísticas; de lo contrario, está perdiendo el tiempo.

Debe verificar el plan de ejecución para ver si es estable (es lo mismo cuando cambia los parámetros), de lo contrario, podría tener que crear un índice de portada (en este caso para cada tabla) (conociendo el sistema puede crear uno que es útil para otras consultas también).

como ejemplo: create index idx01_datafeed_trans On datafeed_trans (feedid, feedDate) INCLUDE (acctNo, tradeDate)

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

si el plan es inestable, debe usar una declaración ad-hoc o EXEC (''oración SQL'') para evaluar y crear un plan de ejecución cada vez. (o un procedimiento almacenado "con recompilación").

Espero eso ayude.

Answer 4

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

6. Variables de tabla Cuando utiliza variables de tabla, no habrá estadísticas preconstruidas para la variable de tabla, lo que a menudo genera grandes diferencias entre las filas estimadas y las reales en el plan de consulta. El uso de OPTION (RECOMPRA) en consultas con variables de tabla permite la generación de un plan de consulta que tiene una estimación mucho mejor de los números de fila implicados. Tuve un uso particularmente crítico de una variable de tabla que no se podía utilizar, y que iba a abandonar, hasta que agregué OPCIÓN (RECOMPRA). El tiempo de ejecución pasó de horas a solo unos minutos. Probablemente esto sea inusual, pero en cualquier caso, si está utilizando variables de tabla y está trabajando en la optimización, vale la pena ver si OPTION (RECOMPULE) hace la diferencia.