Desgaste de los procedimientos almacenados

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

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

'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

el codigo del sp, asi como la estructura de las tablas involucradas
incluyendo indices y restricciones para poder darte una mejor

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

plan compilado no adecuado de acuerdo al valor del parametro. SQL Server

el plan compilado la primera vez que se ejecuta el procedimiento y no hay

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

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,

se pasa un valor no tipico, SQL Server escoje un plan optimo para ese

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

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",

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

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

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

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,

la columna para por este parametro. Limpia el cache de proc, ejecuta el

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

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

proc con el valor que se repite, luego ejecuta el proc pero con un valor

los 10,000 unicos. Chequea las estadisticas de entrada-salida y chequea

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)

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

> union de 5 tablas. El procedimiento recibe una varchar de largo 30 con

> nombre de usuario, el cual lo utilizo en la clausula where.
>
> Saludos,
> Christian M.
>
> "Gustavo Larriera (MVP)" escribió en el

> 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

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

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

> el
> > > mensaje news:
> > >> Christian,
> > >>
> > >> Puedes ejecutar el procedimiento 1 con la opcion "with recompile" y

> > >> que
> > >> pasa.
> > >>
> > >>
> > >> AMB
> > >>
> > >> "Christian" wrote:
> > >>
> > >> > Estimados: Tengo un procedimiento almacenado el cual contiene

> > > consulta
> > >> > que involucra la union 6 tablas. Actualmente este procedimiento
> tarda
> > > un
> > >> > minuto en entregar los resultados, lo cual es bastante

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

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