Parámetro Sniffing(o Spoofing) en SQL Server

sql-server tsql

Question

Hace un tiempo tuve una consulta que corrí bastante para uno de mis usuarios. Aún se estaba desarrollando y modificando, pero finalmente se estabilizó y ejecutó con bastante rapidez, por lo que creamos un procedimiento almacenado a partir de él.

Hasta ahora, tan normal.

El procedimiento almacenado, sin embargo, fue lento. No hay diferencia material entre la consulta y el proceso, pero el cambio de velocidad fue masivo.

[Antecedentes, estamos ejecutando SQL Server 2005.]

Un DBA local amistoso (que ya no trabaja aquí) echó un vistazo al procedimiento almacenado y dijo "¡Parámetro spoofing!" ( Editar: aunque parece que posiblemente también se lo conoce como "detección de parámetros", lo que podría explicar la escasez de éxitos de Google cuando intenté buscarlo).

Hemos abstraído parte del procedimiento almacenado a uno segundo, envolvemos la llamada a este nuevo proceso interno en el externo existente, llamado el exterior y, listo, fue tan rápido como la consulta original.

Entonces, ¿qué da? ¿Alguien puede explicar la suplantación de parámetros?

Crédito de bonificación por

• destacando cómo evitarlo
• sugiriendo cómo reconocer una posible causa
• discutir estrategias alternativas, por ejemplo, estadísticas, índices, claves, para mitigar la situación

Answer 1

Cambiar el procedimiento de almacenamiento para que se ejecute como un lote debería aumentar la velocidad.

Selección de archivo por lotes, es decir:

exec (''select * from order where  order id =''''''+ @ordersID'')

En lugar del procedimiento almacenado normal, seleccione:

select * from order where  order id = @ordersID

Simplemente pase el parámetro como nvarchar y obtendrá resultados más rápidos.

Answer 2

El rastreo de parámetros es una técnica que SQL Server usa para optimizar el plan de ejecución de consultas para un procedimiento almacenado. Cuando llama por primera vez al procedimiento almacenado, SQL Server examina los valores de los parámetros proporcionados de su llamada y decide qué índices usar en función de los valores de los parámetros.

Entonces, cuando la primera llamada contiene parámetros no muy típicos, SQL Server puede seleccionar y almacenar un plan de ejecución subóptimo con respecto a las siguientes llamadas del procedimiento almacenado.

Puede solucionar esto por cualquiera

• utilizando WITH RECOMPILE
• copiando los valores de los parámetros a las variables locales dentro del procedimiento almacenado y usando los locales en sus consultas.

Incluso escuché que es mejor no usar procedimientos almacenados sino enviar sus consultas directamente al servidor. Recientemente me encontré con el mismo problema donde aún no tengo una solución real. Para algunas consultas, la copia a vars locales ayuda a volver al plan de ejecución correcto, para algunas consultas el rendimiento se degrada con los vars locales.

Todavía tengo que investigar más sobre cómo SQL Server almacena en caché y reutiliza (subóptimo) los planes de ejecución.

Answer 3

En mi experiencia, la mejor solución para el olfateo de parámetros es ''Dynamic SQL''. Dos cosas importantes a tener en cuenta es que 1. debe usar parámetros en su consulta dinámica sql 2. debe usar sp_executesql (y no sp_execute), lo que guarda el plan de ejecución para cada valor de parámetro

Answer 4

FYI - necesitas estar al tanto de otra cosa cuando trabajas con SQL 2005 y procs almacenados con parámetros.

SQL Server compilará el plan de ejecución del proceso almacenado con el primer parámetro que se use. Entonces, si ejecutas esto:

usp_QueryMyDataByState ''Rhode Island''

El plan de ejecución funcionará mejor con los datos de un estado pequeño. Pero si alguien da la vuelta y corre:

usp_QueryMyDataByState ''Texas''

El plan de ejecución diseñado para datos del tamaño de Rhode Island puede no ser tan eficiente con datos del tamaño de Texas. Esto puede producir resultados sorprendentes cuando se reinicia el servidor, ya que el plan de ejecución recién generado se dirigirá a cualquier parámetro que se use primero, no necesariamente el mejor. El plan no se volverá a compilar hasta que haya una gran razón para hacerlo, como si se reconstruyeran las estadísticas.

Aquí es donde entran en juego los planes de consulta, y SQL Server 2008 ofrece muchas características nuevas que ayudan a los DBA a anclar un plan de consulta en particular a largo plazo sin importar qué parámetros se llamen primero.

Mi preocupación es que cuando reconstruiste el proceso almacenado, obligaste al plan de ejecución a volver a compilar. Lo llamó con su parámetro favorito, y luego, por supuesto, fue rápido, pero el problema puede no haber sido el proceso almacenado. Pudo haber sido que el proceso almacenado fue recompilado en algún momento con un conjunto inusual de parámetros y, por lo tanto, un plan de consulta ineficiente. Es posible que no hayas arreglado nada y que puedas enfrentar el mismo problema la próxima vez que el servidor se reinicie o el plan de consulta se vuelva a compilar.

Answer 5

Muy simple y ordenado, el Optimizador de consultas utiliza un plan de consulta antiguo para consultas que se ejecutan con frecuencia. pero en realidad el tamaño de los datos también está aumentando, por lo que en ese momento se requiere un nuevo plan optimizado y un optimizador de consultas utilizando el plan de consulta antiguo. Esto se llama Detección de parámetros. También he creado publicaciones detalladas sobre esto. Visite esta url: http://www.dbrnd.com/2015/05/sql-server-parameter-sniffing/

Answer 6

Sí, creo que te refieres al rastreo de parámetros, que es una técnica que el optimizador de SQL Server usa para tratar de determinar los valores / rangos de los parámetros para que pueda elegir el mejor plan de ejecución para tu consulta. En algunos casos, SQL Server realiza un trabajo pobre en la detección de parámetros y no elige el mejor plan de ejecución para la consulta.

Creo que este artículo del blog http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx tiene una buena explicación.

Parece que el DBA en su ejemplo eligió la opción # 4 para mover la consulta a otro sproc a un contexto de procedimiento separado.

También podría haber usado el comando recompilar en el sproc original o usar la opción optimizar para el parámetro.

Answer 7

Tuve un problema similar. El plan de ejecución de mi procedimiento almacenado tomó 30-40 segundos. Intenté usar las Declaraciones de SP en la ventana de consulta y me llevó unos pocos ms ejecutarlas. Luego trabajé declarando variables locales dentro del procedimiento almacenado y transfiriendo los valores de los parámetros a las variables locales. Esto hizo que la ejecución de SP fuera muy rápida y ahora el mismo SP se ejecuta en pocos milisegundos en lugar de 30-40 segundos.

Answer 8

Una manera simple de acelerar eso es reasignar los parámetros de entrada a los parámetros locales en el mismo comienzo del sproc, por ej.

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

    --Work w/ @SniffAvoidingLocalParameter in sproc body 
    -- ...