sql 2005 - SP más lento que Ejecución

Jose,

Dos cosas que olvide mencinar.

1 - Si vas a la ventana de mensajes, puedes ver las estadisticas de IO
despues de ejecutar el script. Veras las implicaciones de usar planes no
optimos de acuerdo al valor de el parametro.

2 - No ejecutes el script en un servidor de produccion. Los comandos DBCC
estan limpiando el cache de planes y de data. Las proximas sentencias seran
compiladas y la data sera leida desde disco hacia el cache. Esto puede
provocabajo rendimiento en las aplicaciones clientes.


AMB


"Alejandro Mesa" wrote:

Jose,

Trata de ejecutar nuevamente el procedimiento con la opcion "with recompile"
y chequea el plan de ejecucion.

Puede ser que el plan de ejecucion fue creado con otro valor, el cual tiene
diferente distribucion de filas en esa tabla. Como mencione anteriormente, si
al ejecutar el sp, su plan no esta en el cache, entonces se crea uno nuevo
pero el optimizador usa el valor del parametro para buscar un plan de acuerdo
a este. Si por casualidad el numero de filas para el valor con el que se creo
el plan es muy diferente de el valor que usas en esta prueba, entonces el
plan creado no es optimo para este ultimo valor. Este comportamiento se
conoce como "parameter sniffing".

Dejame poner un ejemplo.

use Northwind;
go
create procedure dbo.usp_parameter_sniffing
@CustomerID nchar(5)
as
select OrderID, OrderDate, CustomerID
from dbo.Orders
where CustomerID = @CustomerID;
go
dbcc freeproccache;
dbcc dropcleanbuffers;
go
set statistics io on;
go
exec dbo.usp_parameter_sniffing N'CENTC';
go
exec dbo.usp_parameter_sniffing N'SAVEA';
go
set statistics io off;
go
dbcc freeproccache;
dbcc dropcleanbuffers;
go
set statistics io on;
go
exec dbo.usp_parameter_sniffing N'SAVEA';
go
exec dbo.usp_parameter_sniffing N'CENTC';
go
set statistics io off;
go
drop procedure dbo.usp_parameter_sniffing;
go

El cliente 'CENTC' tiene una sola orden, asi que ejecutamos el sp la primera
vez, el optimizador opta por hacer un "index seek" en el indice nonclustered
"CustmoerID" y luego ir al indice clustered a traer el resto de las columnas
referenciadas. Sin embargo, el cliente 'SAVEA' tiene 31 filas, por lo que
usar "index seek" en el indice nonclustered "CustomerID" y luego ir al indice
clustered no es optimo. Puedes ver que cuando limpiamos el cache de planes y
data, y ejecutamos nuevamente el sp pero intercambiamos los valores de los
parametros, el optimizador decide usar "index scan" en el indice clustered,
pues para las 31 filas de 'SAVEA' es mas optimo, sin embargo este plan no es
optimo para 'CENTC'.

Como podemos combatir este comportamiento cuando sabemos que los valores de
los parametros tienen diferente distribucion?

La respuesta dependera de tu ambiente.

1 - Si tus la mayoria de los valores difieren mucho en distribucion, pues lo
mejor sera crear un plan nuevo cada vez que ejecutemos el sp. Eso lo puedes
lograr usando la llamada al sp con la opcion "with recompile" o modificando
el sp y agregando la opcion "with recompile" como parte de la metadata de
este.

create procedure dbo.usp_parameter_sniffing
@CustomerID nchar(5)
with recompile
...

Si ahora ejecutas el script, veras que en cada ejecucion, el optimizador
escoje el plan correcto de acuerdo al valor del parametro.

Tambien puedes usar:

exec dbo.usp_parameter_sniffing N'CENTC' with recompile;
go
exec dbo.usp_parameter_sniffing N'SAVEA' with recompile;
go

La primera opcion le dice a SQL Server que cada vez que ejecute el sp, cree
un nuevo plan y que cuando termine la ejecucion no guarde el plan en el cache.

La segunda opcion, le dice a SQL Server que cree un nuevo plan, sin quitar
el plan existente, y que cuando termine la ejecucion que no lo guarde en el
cache.

2 - Puedes usar variables intermedias para capturar el valor del parametro y
usar la variable en el predicado.

Al parecer esto es lo que hicistes, y ademas ya explique porque el plan
cambia.

3 - Si el procedimiento contiene mas de una sentencia, puedes usar la opcion
"option (recompile)" a nivel de sentencia.

Esto hace que solo la sentencia sea recompilada y no el procedimiento
completo. Esta opcion es a partir de la version 2005.

4 - Puedes usar la opcion "option (optimize for)".

Usando esta opcion puedes indicar que use una constante o el valor de una
variable, o la opcion UNKNOWN.

Por ejemplo, la mayoria de los valores tienen igual distribucion, pero solo
unos pocos difieren, entonces si decides que se use el plan que es optimo
para la mayoria, puedes cojer un valor tipico y ponerlo como constante. Si
usas "unknown", entonces sera como si usaras variables intermedias, incluso
si usas "option (recompile)".

5 - Tambien puedes usar un plan guia.

Esta ultima opcion te la dejo de tarea para que la leas en los BOL.

Dejame saber si hay algo mas en que te pueda ayudar sobre este tema.


AMB



"jose" wrote:

> Muchas gracias Alejandro, estaba pasando que el SP es muy grande y pensaba
> que los planes de ejecuciones eran iguales, pero no.
>
> Está pasando lo sig.
>
> SI ejecuto, por ej:
>
> alter pROCEDURE [dbo].[Test](@prm VARCHAR(50))
> AS
> BEGIN
> SELECT TOP 1 Campo1
> FROM Table with(nolock)
> WHERE Campo1 =@prm
> END
>
> exec [dbo].[Test] 'Valor'
>
> utiliza un plan de ejecucion que tarda 5seg
>
> en cambio si hago:
>
> alter pROCEDURE [dbo].[Test](@prm VARCHAR(50))
> AS
> BEGIN
> declare @prm_interno VARCHAR(50)
> set @prm_interno = @prm
> SELECT TOP 1 Campo1
> FROM Table with(nolock)
> WHERE Campo1 =@prm_interno
> END
>
> exec [dbo].[Test] 'Valor'
>
> o directamente ejecuto:
>
> declare @prm_interno VARCHAR(50)
> set @prm_interno = @prm
> SELECT TOP 1 Campo1
> FROM Table with(nolock)
> WHERE Campo1 =@prm_interno
>
>
> ejecuta otro plan de ejecución, en 1seg.
>
> O sea, el plan de ejecución cambia si el parámetro que uso para el where
> está declarado como parámetro del store, o como definida como variable
> interna
>
> "Alejandro Mesa" wrote:
>
> > Jose,
> >
> > Es dificil poder dar alguna opinion con tan poca informacion. Si por alguna
> > casualidad, cuando ejecutas el codigo en SSMS usas variables para simular
> > parametros del procedimiendo, entonces lo que ocurre es que el plan de
> > ejecucion no es el mismo cuando ejecutas el sp a cuando ejecutas el codigo.
> > Ve si puedes postear el plan de ejecucion de la sentencia en modo texto.
> >
> > set showplan_text on;
> > go
> >
> > exec usp_p1 ...
> >
> > declare @p1 ...
> >
> > select ...
> > where c1 = @p1;
> > go
> >
> > set showplan_text off;
> > go
> >
> > Lo que pasa es que no estamos haciendo una comparacion justa, pues cuando
> > usamos variables, para las cuales se desconoce su valor durante la
> > compilacion, el optimizador de queries usa el promedio de densidad de las
> > columnas que aparecen en comparaciones de igualdad en el predicado, si es que
> > contamos con un indice que pueda ser usado para analizar la selectividad de
> > este predicado, o 30% de el # total de filas en caso de desigualdades.
> >
> > Por otra parte, cuando usamos parametros, si existe tal indice, el
> > optimizador de queries usa el histograma para hacer este tipo de analizis.
> >
> > Si de verdad quieres comparar ambos codigos, entonces tuvieras que
> > parametrizar la sentencia usando sp_executesql, o usar la opcion "option
> > (recompile)" para que el valor de la variable sea reconocido en tiempo de
> > compilacion (en este caso recompilacion) y SQL Server pueda husmear este
> > valor y usarlo para encontrar un plan de ejecucion optimo para este valor
> > especifico.
> >
> > Deja poner un ejemplo breve sobre este tema.
> >
> >
> > use Northwind;
> > go
> > create procedure dbo.usp_p1
> > @customerid nchar(5)
> > as
> > set nocount on;
> >
> > select
> > oh.OrderID,
> > oh.OrderDate,
> > oh.CustomerID,
> > oh.EmployeeID,
> > od.ProductID,
> > od.Quantity,
> > od.UnitPrice,
> > od.Discount
> > from
> > dbo.Orders as oh
> > inner join
> > dbo.[Order Details] as od
> > on oh.OrderID = od.OrderID
> > where
> > oh.CustomerID = @customerid;
> > go
> > dbcc freeproccache;
> > go
> > DBCC SHOW_STATISTICS ("dbo.Orders", CustomerID);
> > GO
> > set statistics profile on;
> > go
> > declare @customerid nchar(5);
> >
> > set @customerid = N'SAVEA';
> >
> > select
> > oh.OrderID,
> > oh.OrderDate,
> > oh.CustomerID,
> > oh.EmployeeID,
> > od.ProductID,
> > od.Quantity,
> > od.UnitPrice,
> > od.Discount
> > from
> > dbo.Orders as oh
> > inner join
> > dbo.[Order Details] as od
> > on oh.OrderID = od.OrderID
> > where
> > oh.CustomerID = @customerid;
> >
> > exec dbo.usp_p1 @customerid;
> > go
> > set statistics profile on;
> > go
> > drop procedure dbo.usp_p1;
> > go
> >
> > Ejecuta el script (antes preciona Ctrl-K para incluir el plan actual de
> > ejecucion).
> >
> > Vamos ahora a tratar de explicar el resultado.
> >
> > Como ves, usamos el parametro @customerid en el sp, pero en el batch ad-hoc
> > es una variable, aunque para la ejecucion de el sp sigue siendo un parametro.
> > Cuando se compila el batch, no se conoce el valor de la variable, sin embargo
> > el sp es compilado cuando se ejecuta la primera vez (si este no esta en el
> > cache de procedimientos) y en ese momento el valor de la variable si se
> > conoce y por ende el valor de el parametro.
> >
> > Fijate que ambos planes de ejecucion son diferentes. El de la variable en la
> > sentencia, usa un "nested loop", mientras que el de el sp usa un "merge
> > join". A pesar de que ambos scanean el indice clustered "PK_Orders", el
> > primero lo hace de forma no ordenada, osea usando las paginas IAM, mientras
> > el otro lo hace de forma ordenada, siguiendo la estructura de el indice (esto
> > lo puedes ver en el grafico de el plan de ejecucion - presiona Ctrl-K antes
> > de ejecutar el script).