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