Desgaste de los procedimientos almacenados

Christian,

Puedes ejecutar el procedimiento 1 con la opcion "with recompile" y ve que
pasa.


AMB

"Christian" wrote:

Estimados: Tengo un procedimiento almacenado el cual contiene una consulta
que involucra la union 6 tablas. Actualmente este procedimiento tarda un
minuto en entregar los resultados, lo cual es bastante condiderando la
simpleza de la consulta y la poca cantidad de registros.

Tome la consulta que estaba contenida el el procedimiento almacenado y la
ejecute el Query Analizer y solo tardo 1 segundoraro????

Procedi a crear un nuevo procedimiento almacenado con la misma consulta del
procedimiento anterior y tambien tardo 1 segundo en ejecutarse.

El resumen tengo 2 procedimientos almacenados ambos con la misma consulta.
Uno tarda 1 minuto y el otro un segundo.

¿A que se puede deber?
¿Sera un cache?
¿Habra que recompilar el procedimiento?

Espero me puedan ayudar.

Saludos,
Christian M.

Eso me funciono, pero no existe una forma automatica de recompilar una vez
todos los procedimientos.

No quiero colocar "with recompile" en el procedimiento ya que cada vez que
se ejecute lo recompilara, desperdiciando la gran ventaja de los
procedimientos almacenados, que se compilan solo una vez.

Saludos,

Christian



"Alejandro Mesa" escribió en el
mensaje news:

Christian,

Puedes ejecutar el procedimiento 1 con la opcion "with recompile" y ve que
pasa.


AMB

"Christian" wrote:

> Estimados: Tengo un procedimiento almacenado el cual contiene una

consulta

> que involucra la union 6 tablas. Actualmente este procedimiento tarda

un

> minuto en entregar los resultados, lo cual es bastante condiderando la
> simpleza de la consulta y la poca cantidad de registros.
>
> Tome la consulta que estaba contenida el el procedimiento almacenado y

la

> ejecute el Query Analizer y solo tardo 1 segundoraro????
>
> Procedi a crear un nuevo procedimiento almacenado con la misma consulta

del

> procedimiento anterior y tambien tardo 1 segundo en ejecutarse.
>
> El resumen tengo 2 procedimientos almacenados ambos con la misma

consulta.

> Uno tarda 1 minuto y el otro un segundo.
>
> ¿A que se puede deber?
> ¿Sera un cache?
> ¿Habra que recompilar el procedimiento?
>
> Espero me puedan ayudar.
>
> Saludos,
> Christian M.
>
>
>
>
>

Puedes mostrar el código del procedimiento almacenado? Le pasas algún
parámetro que afecte de alguna forma las consultas que realiza el
procedimiento?

Gustavo Larriera, MVP
Solid Quality
MVP profile: http://aspnet2.com/mvp.ashx?GustavoLarriera
Blog: http://solidqualitylearning.com/blogs/glarriera/


"Christian" wrote in message
news:uiIUdpi%

Eso me funciono, pero no existe una forma automatica de recompilar una vez
todos los procedimientos.

No quiero colocar "with recompile" en el procedimiento ya que cada vez que
se ejecute lo recompilara, desperdiciando la gran ventaja de los
procedimientos almacenados, que se compilan solo una vez.

Saludos,

Christian



"Alejandro Mesa" escribió en el
mensaje news:

Christian,

Puedes ejecutar el procedimiento 1 con la opcion "with recompile" y ve
que
pasa.


AMB

"Christian" wrote:

> Estimados: Tengo un procedimiento almacenado el cual contiene una

consulta

> que involucra la union 6 tablas. Actualmente este procedimiento tarda

un

> minuto en entregar los resultados, lo cual es bastante condiderando la
> simpleza de la consulta y la poca cantidad de registros.
>
> Tome la consulta que estaba contenida el el procedimiento almacenado y

la

> ejecute el Query Analizer y solo tardo 1 segundoraro????
>
> Procedi a crear un nuevo procedimiento almacenado con la misma consulta

del

> procedimiento anterior y tambien tardo 1 segundo en ejecutarse.
>
> El resumen tengo 2 procedimientos almacenados ambos con la misma

consulta.

> Uno tarda 1 minuto y el otro un segundo.
>
> ¿A que se puede deber?
> ¿Sera un cache?
> ¿Habra que recompilar el procedimiento?
>
> Espero me puedan ayudar.
>
> Saludos,
> Christian M.
>
>
>
>
>

Hola, gracias a todos por responder.

no es una consulta para nada complicada, como dije anteriormente solo es la
union de 5 tablas. El procedimiento recibe una varchar de largo 30 con el
nombre de usuario, el cual lo utilizo en la clausula where.

Saludos,
Christian M.

"Gustavo Larriera (MVP)" escribió en el mensaje
news:#ofMy6i#

Puedes mostrar el código del procedimiento almacenado? Le pasas algún
parámetro que afecte de alguna forma las consultas que realiza el
procedimiento?

Gustavo Larriera, MVP
Solid Quality
MVP profile: http://aspnet2.com/mvp.ashx?GustavoLarriera
Blog: http://solidqualitylearning.com/blogs/glarriera/


"Christian" wrote in message
news:uiIUdpi%
> Eso me funciono, pero no existe una forma automatica de recompilar una

vez

> todos los procedimientos.
>
> No quiero colocar "with recompile" en el procedimiento ya que cada vez

que

> se ejecute lo recompilara, desperdiciando la gran ventaja de los
> procedimientos almacenados, que se compilan solo una vez.
>
> Saludos,
>
> Christian
>
>
>
> "Alejandro Mesa" escribió en

el

> mensaje news:
>> Christian,
>>
>> Puedes ejecutar el procedimiento 1 con la opcion "with recompile" y ve
>> que
>> pasa.
>>
>>
>> AMB
>>
>> "Christian" wrote:
>>
>> > Estimados: Tengo un procedimiento almacenado el cual contiene una
> consulta
>> > que involucra la union 6 tablas. Actualmente este procedimiento

tarda

> un
>> > minuto en entregar los resultados, lo cual es bastante condiderando

la

>> > simpleza de la consulta y la poca cantidad de registros.
>> >
>> > Tome la consulta que estaba contenida el el procedimiento almacenado

y

> la
>> > ejecute el Query Analizer y solo tardo 1 segundoraro????
>> >
>> > Procedi a crear un nuevo procedimiento almacenado con la misma

consulta

> del
>> > procedimiento anterior y tambien tardo 1 segundo en ejecutarse.
>> >
>> > El resumen tengo 2 procedimientos almacenados ambos con la misma
> consulta.
>> > Uno tarda 1 minuto y el otro un segundo.
>> >
>> > ¿A que se puede deber?
>> > ¿Sera un cache?
>> > ¿Habra que recompilar el procedimiento?
>> >
>> > Espero me puedan ayudar.
>> >
>> > Saludos,
>> > Christian M.
>> >
>> >
>> >
>> >
>> >
>
>

Christian,

Una forma rapida seria limpiando el cache de procedimientos "dbcc
freeproccache", pero primero lee sobre los efectos de este comando. No se
recomineda usarse en servidores en produccion, puesto que afectara otras
aplicaciones o bases de datos.

Efectos de DBCC FREEPROCCACHE, y DBCC DROPCLEANBUFFERS en SQL Server
http://solidqualitylearning.com/blo.../2762.aspx

Tambien puedes crear un cursor para recorrer la vista de sistema
"INFORMATION_SCHEMA.ROUTINES" y armar una sentencia dinamica que marque cada
procedimiento para que este sea recompilado automaticamente la proxima vez
que se ejecute, usando el procedimineto "sp_recompile", algo asi como:

declare @sql varchar(4000)
declare @rs sysname
declare @rn sysname
declare c cursor
local
fastforward
for
select ROUTINE_SCHEMA, ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = 'PROCEDURE'
and objectproperty(object_id(N'['+ROUTINE_SCHEMA+N'].['+ROUTINE_NAME+N']'),
'IsMSShipped') = 0

open c

while 1 = 1
begin
fetch next from c into @rs, @rn

if @@errpr != 0 or @@fetch_status != 0 break

set @sql = N'exec sp_recompile ''[' + @rs + N'].[' + @n + N']'''
exec sp_executesql @sql
end

close c
deallocate c
go

Nota: Este codigo no le probe antes de postearlo

La pregunta que te hizo Gustavo es muy importante. Seria bueno que postearas
el codigo del sp, asi como la estructura de las tablas involucradas
incluyendo indices y restricciones para poder darte una mejor recomendacion.

Si la consulta esta usando ese parametro en el filtro de la clausula
"where", posiblemente la distribucion de los valores de la columna
involucrada no sea uniforme y esto puede causar que SQL Server este usando el
plan compilado no adecuado de acuerdo al valor del parametro. SQL Server crea
el plan compilado la primera vez que se ejecuta el procedimiento y no hay un
plan compilado en el cache, pero para crear este plan, SQL Server usa el
valor del parametro que se pasa, para investigar en las estadisticas de
distribucion del indice, el valor estimado de las filas que seran devuelta
para este valor y en dependencia de si son pocas o muchas, SQL Server
decidira si usar un rastreo del indice nonclustered, una busqueda directa
(seek) en ese indice, un rastreo del indice clustered o de la tabla o una
busqueda directa por el indice clustered. Si el valor que se pasa es un valor
tipico o uniforme, entonces SQL Server usara un plan de ejecucion dado, el
cual no sera optimo para cuando se pase un valor atipico (no uniforme). Lo
mismo puede pasar de forma contraria, la primera vez que compila el plan, si
se pasa un valor no tipico, SQL Server escoje un plan optimo para ese valor y
este plan no sera optimo cuando se le pase un valor tipico. En ingles este
comportamiento se conoce como "parameter sniffing", que si lo traducimos (no
soy traductor) sera algo asi como olfateo de los parametros.

Que podemos hacer para evitar este comportamiento cuando sabemos que la
distribucion de los valores de una columna (o grupo de columnas que
participan en filtro), que sera usada en el filtro de la clausula "where", no
es uniforme?

1 - Se puede crear el procedimiento con la opcion "with recompile".
2 - Se puede ejecutar el procedimiento con la opcion "with recompile".
3 - Se puede asignar el valor del parametro a una variable interna del
procedimiento y usar la variable en el filtro, en vez de usar el parametro.
En este caso SQL Server al no saber el valor de la variable en tiempo de
compilacion, usara la densidad de la column o columnas involucradas para
estimar las filas a seleccionar, si esta columnas no forman parte del indice,
SQL Server usara la densidad del indice o una formula (que ahorita no
recuerdo) con porcientos fijos para esta estimacion. Ojo, esta solucion se
recomineda solo cuando la distribucion de los valores no es uniforme.
4 - Usar sql dinamico sin parametrizacion, pero esta solucion no la
recomiendo para nada.

La solucion de ejecutar "dbcc freeproccache", es una solucion muy drastica y
no se recomienda ser usada en servidores en producccon.

Puedes prepararte un ejemplo para probar lo anterior. Crea una tabla con
100, 000 filas y un par de columnas. En la primera columna usa 10, 000
valores distintos y en el resto de las filas usa un unico valor. Crea un
indice por esta columna. Crea un procedmiento que espere un parametro de
entrada y usa una sentencia "select" que seleccione desde la tabla, filtrando
la columna para por este parametro. Limpia el cache de proc, ejecuta el proc
con un valor cualquiera de los 10,000 que son unicos, luego ejecuta
nuevamente el proc con el valor que se repirte 90,000 veces. Ve que el plan
de ejecucion que se usa es el mismo, pero chequea las estadisticas de
entrada/ salida IO para que veas la diferencia. Limpia el cache, ejecuta el
proc con el valor que se repite, luego ejecuta el proc pero con un valor de
los 10,000 unicos. Chequea las estadisticas de entrada-salida y chequea que
el plan de ejecucion es diferente que el de las primeras dos ejecuciones.

Estadísticas de distribución en SQL Server 2000 (I)
http://www.helpdna.net/colab02.htm

Estadísticas de Distribución en SQL Server 2000 (2)
http://www.configuracionesintegrale...articulo35

A technique for ensuring plan stability in SQL Server 2000
http://blogs.msdn.com/khen1234/arch...24228.aspx

Tips, Tricks, and Advice from the SQL Server Query Optimization Team
http://blogs.msdn.com/queryoptteam/...65991.aspx


AMB

"Christian" wrote:

Hola, gracias a todos por responder.

no es una consulta para nada complicada, como dije anteriormente solo es la
union de 5 tablas. El procedimiento recibe una varchar de largo 30 con el
nombre de usuario, el cual lo utilizo en la clausula where.

Saludos,
Christian M.

"Gustavo Larriera (MVP)" escribió en el mensaje
news:#ofMy6i#
> Puedes mostrar el código del procedimiento almacenado? Le pasas algún
> parámetro que afecte de alguna forma las consultas que realiza el
> procedimiento?
>
> Gustavo Larriera, MVP
> Solid Quality
> MVP profile: http://aspnet2.com/mvp.ashx?GustavoLarriera
> Blog: http://solidqualitylearning.com/blogs/glarriera/
>
>
> "Christian" wrote in message
> news:uiIUdpi%
> > Eso me funciono, pero no existe una forma automatica de recompilar una
vez
> > todos los procedimientos.
> >
> > No quiero colocar "with recompile" en el procedimiento ya que cada vez
que
> > se ejecute lo recompilara, desperdiciando la gran ventaja de los
> > procedimientos almacenados, que se compilan solo una vez.
> >
> > Saludos,
> >
> > Christian
> >
> >
> >
> > "Alejandro Mesa" escribió en
el
> > mensaje news:
> >> Christian,
> >>
> >> Puedes ejecutar el procedimiento 1 con la opcion "with recompile" y ve
> >> que
> >> pasa.
> >>
> >>
> >> AMB
> >>
> >> "Christian" wrote:
> >>
> >> > Estimados: Tengo un procedimiento almacenado el cual contiene una
> > consulta
> >> > que involucra la union 6 tablas. Actualmente este procedimiento
tarda
> > un
> >> > minuto en entregar los resultados, lo cual es bastante condiderando
la
> >> > simpleza de la consulta y la poca cantidad de registros.
> >> >
> >> > Tome la consulta que estaba contenida el el procedimiento almacenado
y
> > la
> >> > ejecute el Query Analizer y solo tardo 1 segundoraro????
> >> >
> >> > Procedi a crear un nuevo procedimiento almacenado con la misma
consulta
> > del
> >> > procedimiento anterior y tambien tardo 1 segundo en ejecutarse.
> >> >
> >> > El resumen tengo 2 procedimientos almacenados ambos con la misma
> > consulta.
> >> > Uno tarda 1 minuto y el otro un segundo.
> >> >
> >> > ¿A que se puede deber?
> >> > ¿Sera un cache?
> >> > ¿Habra que recompilar el procedimiento?
> >> >
> >> > Espero me puedan ayudar.
> >> >
> >> > Saludos,
> >> > Christian M.
> >> >
> >> >
> >> >
> >> >
> >> >
> >
> >
>
>