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

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).

Ahora lo mas importante, fijate en el numero de filas estimadas en cada
"clustered index scan". Este estimado es el # de filas que seran devueltas o
cardinalidad de el resultado.

- sentencia - variable (9.32584)
- procedimiento - parametro (31)

De donde salen estos valores?

Esta tabla tiene un undice por [CustomerID] llamado "CustomerID", y que
viene de paravilla para hacer el estimado, pues este es "customerid =
@customerid". Si chequeas los valores devueltos por el comando DBCC, veremos
lo sgte:

Para estimar este valor en la sentencia que usa la variable, el optimizador
uso el valor de la columna "All density" correspondiente a "CustomerID" en el
vector de densidades, cuyo valor es 0.01123596. Si multiplicamos este valor
por el # de filas en la tabla 830, entonces obtenemos 9.32584 que es el mismo
valor estimado que aprece en el plan actual.

Por lo contrario, para estimar este valor en el procedimiento, dado que la
sentencia usa el parametro, entonces podemos ir al histograma y buscar el #
filas para el valor de RANGE_HI_KEY = "SAVEA" y vemos que EQ_ROWS = 31.

Dado que el estimado de filas es diferente, el optimizador decide usar
diferente metodo de acuerdo a ese estimado.

Que pasa si ahora usamos la opcion "option (recompile)" o si parametrizamos
la sentencia que usa la variable?

Ojo, yo no estoy diciendo que debemos usar "option (recompile)" para usar
mejor estimados, sino que la uses en este caso para hacer una comparacion
justa. Tambien puedes usarla si detectas problemas devido a diferencia de
distribucion de los valores usados en el predicado, osea que el mismo plan se
esta usando para valores tipicos y no tipicos.

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
option (recompile);

Si haces el cambio y ejecutas el script nuevamente, voila ahora los planes
son los mismos, incluyendo los estimados de # de filas que se esperan sean
devueltas (cardinalidad).

No se debe probar este script en un servidor de produccion, pues el uso de
"dbcc freeproccache" eliminara todos los planes que estan en el cache y cada
sentencia nueva sera compilada nuevamente.

Todo esto lo puedes estudiar en los sgtes libros / documentos:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://technet.microsoft.com/en-us/...66419.aspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://technet.microsoft.com/en-us/...66425.aspx

Inside Microsoft SQL Server(TM) 2005: The Storage Engine
http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-3

Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization
http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-4


yo no estoy al tanto de si estos libros y/o documentos existen en el
lenguaje Español, asi que si alguien sabe de esto y puede incluir los links
pues bienvenido y gracías por anticipado.


AMB


"jose" wrote:

Buenos días, tengo un sp que ejecuta en 10 seg.
sin embargo, si ejecuto el código interno del mismo desde el queryanalizar
con los mismos parámetros funciona el 6seg.

qué puede pasar? por qué el mismo código pero dentro del sp funciona mucho
más lento?

gracias

Este es el tipo de respuesta que todos deberíamos dar. Explicativa, clara,
con ejemplos y referencias.

Felicidades Alejandro.

"Caminar sobre el agua y desarrollar software a partir de unas
especificaciones es fácil, si ambas están congeladas."
Edward V. Berard, ingeniero informático


"Alejandro Mesa" wrote in message
news:

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).

Ahora lo mas importante, fijate en el numero de filas estimadas en cada
"clustered index scan". Este estimado es el # de filas que seran devueltas
o
cardinalidad de el resultado.

- sentencia - variable (9.32584)
- procedimiento - parametro (31)

De donde salen estos valores?

Esta tabla tiene un undice por [CustomerID] llamado "CustomerID", y que
viene de paravilla para hacer el estimado, pues este es "customerid > @customerid". Si chequeas los valores devueltos por el comando DBCC,
veremos
lo sgte:

Para estimar este valor en la sentencia que usa la variable, el
optimizador
uso el valor de la columna "All density" correspondiente a "CustomerID" en
el
vector de densidades, cuyo valor es 0.01123596. Si multiplicamos este
valor
por el # de filas en la tabla 830, entonces obtenemos 9.32584 que es el
mismo
valor estimado que aprece en el plan actual.

Por lo contrario, para estimar este valor en el procedimiento, dado que la
sentencia usa el parametro, entonces podemos ir al histograma y buscar el
#
filas para el valor de RANGE_HI_KEY = "SAVEA" y vemos que EQ_ROWS = 31.

Dado que el estimado de filas es diferente, el optimizador decide usar
diferente metodo de acuerdo a ese estimado.

Que pasa si ahora usamos la opcion "option (recompile)" o si
parametrizamos
la sentencia que usa la variable?

Ojo, yo no estoy diciendo que debemos usar "option (recompile)" para usar
mejor estimados, sino que la uses en este caso para hacer una comparacion
justa. Tambien puedes usarla si detectas problemas devido a diferencia de
distribucion de los valores usados en el predicado, osea que el mismo plan
se
esta usando para valores tipicos y no tipicos.

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
option (recompile);

Si haces el cambio y ejecutas el script nuevamente, voila ahora los planes
son los mismos, incluyendo los estimados de # de filas que se esperan sean
devueltas (cardinalidad).

No se debe probar este script en un servidor de produccion, pues el uso de
"dbcc freeproccache" eliminara todos los planes que estan en el cache y
cada
sentencia nueva sera compilada nuevamente.

Todo esto lo puedes estudiar en los sgtes libros / documentos:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://technet.microsoft.com/en-us/...66419.aspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server
2005
http://technet.microsoft.com/en-us/...66425.aspx

Inside Microsoft SQL Server(TM) 2005: The Storage Engine
http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-3

Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization
http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-4


yo no estoy al tanto de si estos libros y/o documentos existen en el
lenguaje Español, asi que si alguien sabe de esto y puede incluir los
links
pues bienvenido y gracías por anticipado.


AMB


"jose" wrote:

Buenos días, tengo un sp que ejecuta en 10 seg.
sin embargo, si ejecuto el código interno del mismo desde el
queryanalizar
con los mismos parámetros funciona el 6seg.

qué puede pasar? por qué el mismo código pero dentro del sp funciona
mucho
más lento?

gracias

:)

AMB

"Carlos Sacristan" wrote:

Este es el tipo de respuesta que todos deberíamos dar. Explicativa, clara,
con ejemplos y referencias.

Felicidades Alejandro.

"Caminar sobre el agua y desarrollar software a partir de unas
especificaciones es fácil, si ambas están congeladas."
Edward V. Berard, ingeniero informático


"Alejandro Mesa" wrote in message
news:
> 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).
>
> Ahora lo mas importante, fijate en el numero de filas estimadas en cada
> "clustered index scan". Este estimado es el # de filas que seran devueltas
> o
> cardinalidad de el resultado.
>
> - sentencia - variable (9.32584)
> - procedimiento - parametro (31)
>
> De donde salen estos valores?
>
> Esta tabla tiene un undice por [CustomerID] llamado "CustomerID", y que
> viene de paravilla para hacer el estimado, pues este es "customerid > > @customerid". Si chequeas los valores devueltos por el comando DBCC,
> veremos
> lo sgte:
>
> Para estimar este valor en la sentencia que usa la variable, el
> optimizador
> uso el valor de la columna "All density" correspondiente a "CustomerID" en
> el
> vector de densidades, cuyo valor es 0.01123596. Si multiplicamos este
> valor
> por el # de filas en la tabla 830, entonces obtenemos 9.32584 que es el
> mismo
> valor estimado que aprece en el plan actual.
>
> Por lo contrario, para estimar este valor en el procedimiento, dado que la
> sentencia usa el parametro, entonces podemos ir al histograma y buscar el
> #
> filas para el valor de RANGE_HI_KEY = "SAVEA" y vemos que EQ_ROWS = 31.
>
> Dado que el estimado de filas es diferente, el optimizador decide usar
> diferente metodo de acuerdo a ese estimado.
>
> Que pasa si ahora usamos la opcion "option (recompile)" o si
> parametrizamos
> la sentencia que usa la variable?
>
> Ojo, yo no estoy diciendo que debemos usar "option (recompile)" para usar
> mejor estimados, sino que la uses en este caso para hacer una comparacion
> justa. Tambien puedes usarla si detectas problemas devido a diferencia de
> distribucion de los valores usados en el predicado, osea que el mismo plan
> se
> esta usando para valores tipicos y no tipicos.
>
> 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
> option (recompile);
>
> Si haces el cambio y ejecutas el script nuevamente, voila ahora los planes
> son los mismos, incluyendo los estimados de # de filas que se esperan sean
> devueltas (cardinalidad).
>
> No se debe probar este script en un servidor de produccion, pues el uso de
> "dbcc freeproccache" eliminara todos los planes que estan en el cache y
> cada
> sentencia nueva sera compilada nuevamente.
>
> Todo esto lo puedes estudiar en los sgtes libros / documentos:
>
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
> http://technet.microsoft.com/en-us/...66419.aspx
>
> Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server
> 2005
> http://technet.microsoft.com/en-us/...66425.aspx
>
> Inside Microsoft SQL Server(TM) 2005: The Storage Engine
> http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-3
>
> Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization
> http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-4
>
>
> yo no estoy al tanto de si estos libros y/o documentos existen en el
> lenguaje Español, asi que si alguien sabe de esto y puede incluir los
> links
> pues bienvenido y gracías por anticipado.
>
>
> AMB
>
>
> "jose" wrote:
>
>> Buenos días, tengo un sp que ejecuta en 10 seg.
>> sin embargo, si ejecuto el código interno del mismo desde el
>> queryanalizar
>> con los mismos parámetros funciona el 6seg.
>>
>> qué puede pasar? por qué el mismo código pero dentro del sp funciona
>> mucho
>> más lento?
>>
>> gracias
>>

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).

Ahora lo mas importante, fijate en el numero de filas estimadas en cada
"clustered index scan". Este estimado es el # de filas que seran devueltas o
cardinalidad de el resultado.

- sentencia - variable (9.32584)
- procedimiento - parametro (31)

De donde salen estos valores?

Esta tabla tiene un undice por [CustomerID] llamado "CustomerID", y que
viene de paravilla para hacer el estimado, pues este es "customerid =
@customerid". Si chequeas los valores devueltos por el comando DBCC, veremos
lo sgte:

Para estimar este valor en la sentencia que usa la variable, el optimizador
uso el valor de la columna "All density" correspondiente a "CustomerID" en el
vector de densidades, cuyo valor es 0.01123596. Si multiplicamos este valor
por el # de filas en la tabla 830, entonces obtenemos 9.32584 que es el mismo
valor estimado que aprece en el plan actual.

Por lo contrario, para estimar este valor en el procedimiento, dado que la
sentencia usa el parametro, entonces podemos ir al histograma y buscar el #
filas para el valor de RANGE_HI_KEY = "SAVEA" y vemos que EQ_ROWS = 31.

Dado que el estimado de filas es diferente, el optimizador decide usar
diferente metodo de acuerdo a ese estimado.

Que pasa si ahora usamos la opcion "option (recompile)" o si parametrizamos
la sentencia que usa la variable?

Ojo, yo no estoy diciendo que debemos usar "option (recompile)" para usar
mejor estimados, sino que la uses en este caso para hacer una comparacion
justa. Tambien puedes usarla si detectas problemas devido a diferencia de
distribucion de los valores usados en el predicado, osea que el mismo plan se
esta usando para valores tipicos y no tipicos.

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
option (recompile);

Si haces el cambio y ejecutas el script nuevamente, voila ahora los planes
son los mismos, incluyendo los estimados de # de filas que se esperan sean
devueltas (cardinalidad).

No se debe probar este script en un servidor de produccion, pues el uso de
"dbcc freeproccache" eliminara todos los planes que estan en el cache y cada
sentencia nueva sera compilada nuevamente.

Todo esto lo puedes estudiar en los sgtes libros / documentos:

Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
http://technet.microsoft.com/en-us/...66419.aspx

Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
http://technet.microsoft.com/en-us/...66425.aspx

Inside Microsoft SQL Server(TM) 2005: The Storage Engine
http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-3

Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization
http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-4


yo no estoy al tanto de si estos libros y/o documentos existen en el
lenguaje Español, asi que si alguien sabe de esto y puede incluir los links
pues bienvenido y gracías por anticipado.


AMB


"jose" wrote:

> Buenos días, tengo un sp que ejecuta en 10 seg.
> sin embargo, si ejecuto el código interno del mismo desde el queryanalizar
> con los mismos parámetros funciona el 6seg.
>
> qué puede pasar? por qué el mismo código pero dentro del sp funciona mucho
> más lento?
>
> gracias
>

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).
>
> Ahora lo mas importante, fijate en el numero de filas estimadas en cada
> "clustered index scan". Este estimado es el # de filas que seran devueltas o
> cardinalidad de el resultado.
>
> - sentencia - variable (9.32584)
> - procedimiento - parametro (31)
>
> De donde salen estos valores?
>
> Esta tabla tiene un undice por [CustomerID] llamado "CustomerID", y que
> viene de paravilla para hacer el estimado, pues este es "customerid =
> @customerid". Si chequeas los valores devueltos por el comando DBCC, veremos
> lo sgte:
>
> Para estimar este valor en la sentencia que usa la variable, el optimizador
> uso el valor de la columna "All density" correspondiente a "CustomerID" en el
> vector de densidades, cuyo valor es 0.01123596. Si multiplicamos este valor
> por el # de filas en la tabla 830, entonces obtenemos 9.32584 que es el mismo
> valor estimado que aprece en el plan actual.
>
> Por lo contrario, para estimar este valor en el procedimiento, dado que la
> sentencia usa el parametro, entonces podemos ir al histograma y buscar el #
> filas para el valor de RANGE_HI_KEY = "SAVEA" y vemos que EQ_ROWS = 31.
>
> Dado que el estimado de filas es diferente, el optimizador decide usar
> diferente metodo de acuerdo a ese estimado.
>
> Que pasa si ahora usamos la opcion "option (recompile)" o si parametrizamos
> la sentencia que usa la variable?
>
> Ojo, yo no estoy diciendo que debemos usar "option (recompile)" para usar
> mejor estimados, sino que la uses en este caso para hacer una comparacion
> justa. Tambien puedes usarla si detectas problemas devido a diferencia de
> distribucion de los valores usados en el predicado, osea que el mismo plan se
> esta usando para valores tipicos y no tipicos.
>
> 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
> option (recompile);
>
> Si haces el cambio y ejecutas el script nuevamente, voila ahora los planes
> son los mismos, incluyendo los estimados de # de filas que se esperan sean
> devueltas (cardinalidad).
>
> No se debe probar este script en un servidor de produccion, pues el uso de
> "dbcc freeproccache" eliminara todos los planes que estan en el cache y cada
> sentencia nueva sera compilada nuevamente.
>
> Todo esto lo puedes estudiar en los sgtes libros / documentos:
>
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2005
> http://technet.microsoft.com/en-us/...66419.aspx
>
> Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
> http://technet.microsoft.com/en-us/...66425.aspx
>
> Inside Microsoft SQL Server(TM) 2005: The Storage Engine
> http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-3
>
> Inside Microsoft® SQL Server(TM) 2005: Query Tuning and Optimization
> http://www.amazon.com/Inside-Micros...ks&qid54667734&sr=1-4
>
>
> yo no estoy al tanto de si estos libros y/o documentos existen en el
> lenguaje Español, asi que si alguien sabe de esto y puede incluir los links
> pues bienvenido y gracías por anticipado.
>
>
> AMB
>
>
> "jose" wrote:
>
> > Buenos días, tengo un sp que ejecuta en 10 seg.
> > sin embargo, si ejecuto el código interno del mismo desde el queryanalizar
> > con los mismos parámetros funciona el 6seg.
> >
> > qué puede pasar? por qué el mismo código pero dentro del sp funciona mucho
> > más lento?
> >
> > gracias
> >