¡TRANSOLUTIONS AHORA ES PARTE DE BAUFEST!
Ingresa a baufest.com para conocer más sobre nuestros servicios.

BLOG

Jueves, 28 Junio 2018 14:19

SQL Server "Parameter sniffing" consideraciones en su uso

Para el artículo de hoy presentamos a Adolfo Quesquén, DBA Senior en Trans Solutions Systems. Veremos algunas consideraciones, beneficios y problemas, en el uso de Parameter Sniffing.

Parameter Sniffinges es una técnica usada por los motores de base de datos (en este caso SQL Server) para optimizar el plan de ejecución de un store procedure dependiendo de los valores de los parámetros del mismo. Por lo general, es una excelente técnica de ayuda para mejorar la performance de la ejecución del store procedure, cuando en la mayoría de los escenarios donde se ejecuta, se trabaja con el mismo conjunto de datos.

Cuando un procedimiento almacenado o una instrucción con parámetros es invocado por primera vez, el motor de base de datos SQL Server genera un plan de ejecución utilizando los valores de los parámetros. Con estos valores el motor de base de datos crear mejores planes de ejecución. El motor crea el plan de ejecución sabiendo qué valor se está buscando. Después de ello, el motor de base de datos almacena los planes de ejecución para su posterior uso y evitar crear el plan de ejecución otra vez.

BENEFICIOS DEL PARAMETER SNIFFING

Revisemos un ejemplo: Tenemos una tabla personal donde se busca por el campo DNI, que es un campo indexado. El plan de ejecución que se genera utiliza el índice.

set nocount on
--drop table dbo.Persona
create table dbo.Persona (DNI int identity , apellidos nvarchar(100) , nombres nvarchar(100), direccion nvarchar(300))
create index IdxPersona_dni on dbo.Persona(DNI)
create index IdxPersona_Apellidos on dbo.Persona(Apellidos)
go
insert into dbo.persona values
('Arizabal', 'Jose Pedro','lima, 1234'),
('bravo', 'Jose Ernesto','lima, 1234')
go
insert into dbo.persona values
('Tito', 'Ernesto Carlos','lima, 1234')
go 1000 -- se ejecuta 1000 veces insertando 1000 registros

El plan generado con la primera invocación utiliza el índice “IdxPersona_dni”, que indexa al campo DNI. Para generar el plan de ejecución el motor utiliza el valor del parámetro, en este caso es “500”.

dbcc freeproccache
go
Declare @pDNI int
set @pDNI=500
Exec sp_executesql N'select * from persona where DNI = @pDNI',N'@pDNI int',@pDNI

Como se puede ver el “Parameter Compiled Value” y “Parameter Runtime Value” en ambos casos es “500”

Parameter Compiled Value

Realicemos otra búsqueda donde el DNI es 700:

set @pDNI=700
Exec sp_executesql N'select * from persona where DNI = @pDNI', N'@pDNI int', @pDNI
go
El plan de ejecución existente es reusado para esta nueva búsqueda. El parámetro compilado de @pDNI es “(500)” y el parámetro de ejecución es “(700)”
parámetro de ejecución
Con este comportamiento tenemos mejores planes de ejecución y ahorro en recursos de CPU y tiempo en la generación de los planes de ejecución.

PROBLEMAS CON EL “PARAMETER SNIFFING”

Hay casos en los que la forma como funciona el “Parameter sniffing” ocasiona problemas de performance.

En la tabla que hemos creado podemos buscar por el campo “apellido”. En ella realicemos las siguientes búsquedas:

  • Buscar el apellido “Arizabal”, sólo 1 registro cumple la condición. El plan de ejecución generado debería utilizar el índice que tiene este campo.
  • Buscar el apellido “Tito”, son 1,000 registros los que cumplen la condición. El plan generado debería realizar la búsqueda directamente en la tabla sin utilizar índice.

Busquemos primero por apellido =“Arizabal”

dbcc freeproccache
go
Declare @pApellidos nvarchar(100)
set @pApellidos='Arizabal'
Exec sp_executesql N'select * from persona where apellidos = @PApellidos',N'@pApellidos nvarchar(50)',@pApellidos

El plan de ejecución generado utiliza el índice. El “Estimated Number of Rows” es “1” y el valor del “Parameter Compiled Value” y “Parameter Runtime Value” es “Arizabal”. El plan de ejecución es óptimo para esta búsqueda.

plan de ejecución generado

Cambiemos el valor de la variable para buscar el apellido “Tito”

set @pApellidos='Tito'
Exec sp_executesql N'select * from persona where apellidos = @PApellidos',N'@pApellidos nvarchar(50)',@pApellidos
go

El plan de ejecución se reúsa. El “Parameter Compiled Value” es “Arizabal”, el “Parameter Runtime Value” es “Tito” y el “Estimated Number of Rows” es 1. Como sabemos son 1,000 registros los que cumplen la condición, bastante diferente al valor estimado de 1. Lo óptimo es realizar la búsqueda directamente en la tabla sin utilizar el índice, cosa que difiere del plan que se está utilizando para realizar esta búsqueda.

El plan de ejecución se reúsa

Realicemos la búsqueda en el orden inverso. Primero el Apellido “Tito” limpiando previamente los planes de ejecución existentes:

dbcc freeproccache
go
Declare @pApellidos nvarchar(100)
set @pApellidos='Tito'
Exec sp_executesql N'select * from persona where apellidos = @PApellidos',N'@pApellidos nvarchar(50)',@pApellidos

El plan de ejecución creado esta vez realiza la búsqueda usando un “Table Scan” a la tabla. Busca los registros que cumplan la condición en la tabla descartando utilizar el índice que existe. El valor de “Estimated Number of Rows” es 1,000 registros más acertada.

“Table Scan” a la tabla

Buscando el apellido “Arizabal”

set @pApellidos='Arizabal'
Exec sp_executesql N'select * from persona where apellidos = @PApellidos',N'@pApellidos nvarchar(50)',@pApellidos
go

Lo óptimo en el plan de ejecución de esta búsqueda es utilizar el índice, pero el motor reúsa el plan de ejecución existente.

el motor reúsa el plan de ejecución existente

OTROS PROBLEMAS FRECUENTES CON EL “PARAMETER SNIFFING”

Búsquedas con parámetros por rango de fecha

El rango buscado puede ser de días, meses o años. Si un plan generado para una búsqueda de un rango de un día se reúsa para la búsqueda en un rango más amplio, como años, lo probable es que el plan de ejecución no sea lo óptimo.

Búsquedas con parámetros con filtros >, >=, <, <=

Similar a las búsquedas por rango de fecha.

Búsquedas con parámetros y filtros con like

La búsqueda con parámetros en el “where” con un “like”, el plan generado va a ser óptimo para la primera invocación, pero para las siguientes el parámetro podría incluir más caracteres. También podría incluir el carácter comodín “%”, que puede estar al inicio y/o al final de la cadena, esto origina que el reusó del plan generado no sea el mas óptimo para las subsiguientes ejecuciones.

Búsquedas con parámetros y filtros con condiciones de “or’s”

Las instrucciones que incluyen “or’s” en la parte de “where” son lo mismo que en casos anteriores. Esta optimizado sólo para la primera invocación, no las siguientes.

Para todos estos tipos de búsquedas el problema es el reúso del plan de ejecución. Una buena alternativa inicial de solución para estos casos es recompilar la instrucción cada vez que se ejecute. Con esto se pierde el poder reusar los planes de ejecución, pero se van a obtener mejores planes de ejecución.

No en todos los casos esta recomendación es la mejor o es viable. Hay que analizar cada caso y utilizar otras alternativas de solución.

ALTERNATIVAS DE SOLUCIÓN

Las opciones que tenemos para resolver este tipo de problema son las siguientes:

  1. Desactivar el “parameter sniffing” en la base de datos:

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-2017

  1. Agregar hints a la instrucción:

“OPTIMIZE FOR UNKNOWN”, “OPTIMICE FOR VALUE”, “OPTIMIZE RECOMPILE”
https://blogs.msdn.microsoft.com/robinlester/2016/08/10/improving-query-performance-with-option-recompile-constant-folding-and-avoiding-parameter-sniffing-issues/

  1. Agregar variables intermedias en los procedimientos almacenados:

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

  1. Crear el procedimiento almacenado con la opción recompile o invocarlo con la opción recompile:

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/recompile-a-stored-procedure?view=sql-server-2017

  1. Crear la instrucción agregando dinámicamente las partes de la instrucción:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-2017