sql-server - unico - indices tabla sql server

Búsqueda de índice utilizada en la consulta pero exploración de índice en procedimiento almacenado

Question

Estoy experimentando un comportamiento extraño en SQL Server 2008 R2.

Tengo la siguiente consulta:

UPDATE  TableToUpdate
SET     ColumnToUpdate = @ColumnValue
WHERE   ColumnA IN
        (
            SELECT  ColumnA
            FROM    SubQ1Table
            WHERE   ColumnToUpdateReference = @ColumnValue
        )
and     ColumnB in
        (
            SELECT  ColumnB
            FROM    SubQ2Table
            WHERE   ColumnToUpdateReference = @ColumnValue
        )

Cuando ejecuto la consulta manualmente y declaro la variable @ColumnValue de la siguiente manera:

DECLARE @ColumnValue INT = 123;

Se ejecuta en segundos y realiza una búsqueda de índice en el índice que cubre las columnas ColumnA, ColumnB y ColumnToUpdate en la tabla TableToUpdate.

Cuando creo un procedimiento almacenado usando la consulta exacta, excepto que el parámetro se transfiere ... por ejemplo:

EXEC sp_Query 123

Se usa una exploración de índice en el mismo índice y tarda alrededor de 30 segundos en completarse.

Miré ambos planes de consulta que son diferentes. Leyendo de izquierda a derecha, la primera diferencia es que el procedimiento almacenado parece hacer un bucle anidado (unión interna) mientras que la consulta directa hace un agregado de transmisión (agregado).

¿Por qué llamar a este SQL a través de un procedimiento almacenado marca la diferencia? Si necesita que proporcione más información, hágamelo saber.

Gracias por adelantado,

Tom.

Answer 1

¿Puedes verificar el parámetro del tipo de datos en el procedimiento almacenado?

si no es de tipo int, puede estar ocurriendo alguna conversación implícita, lo que está dando como resultado un escaneo de índice.

Answer 2

Crea tu SP con la opción RECOMPULE y luego revisa el plan de ejecución para el mismo. Gran artículo sobre lo mismo de abajo:

http://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/

Answer 3

Como sugirió GarethD, suena como si fuera el olfateo de los parámetros, en caso de que no sepas qué es eso, déjame (intento explicar).

SQL Server compila los procedimientos almacenados "olfateando" los parámetros enviados la primera vez que se ejecuta el procedimiento y el plan para esta ejecución se coloca en la memoria caché del plan para referencia futura.

Cada vez que se ejecuta el procedimiento, SQL Server recupera el plan de ejecución de la memoria caché y lo usa (a menos que exista una razón para la recompilación).

Puede surgir un problema si la primera vez que se ejecuta el procedimiento almacenado se usa un conjunto de parámetros que genera un plan aceptable para ese conjunto de parámetros, pero muy malo para otros conjuntos más comunes de parámetros.

Hay algunas formas de evitar esto que puedo pensar:

Hay algunas soluciones para superar este problema.

OPTION (RECOMPILE)
OPTION (OPTIMIZE FOR (@VARIABLE=VALUE))
OPTION (OPTIMIZE FOR (@VARIABLE UNKNOWN))
Use local variables

Depende de su configuración que sea mejor para usted, pero lea a su alrededor.

SQL Server - olfateo de parámetros

Parámetro Sniffing (o Spoofing) en SQL Server

https://www.simple-talk.com/sql/t-sql-programming/parameter-sniffing/

También en una nota al margen, tenga cuidado con el uso de ''sp_'' al nombrar los procedimientos almacenados, es un gran no, no. Mira este artículo , detalla un examen completo del uso de sp_.