How often and when should the sp_recompile procedure be used

We are at our slowest this time of year... Having never set the sp_recompile
directive for our procedures, would now be a good time to tell the server to
recompile all the stored procedures? I believe we have added perhaps 30 or
40 indexes since October.
Is this an appropriate method to activate the recompile?
DECLARE @.procname varchar(150)
DECLARE proc_recompile cursor for select name from sysobjects where xtype='P'
OPEN proc_recompile
FETCH NEXT FROM proc_recompile INTO @.procname
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_recompile @.procname
FETCH NEXT FROM proc_recompile INTO @.procname
END
CLOSE proc_recompile
DEALLOCATE proc_recompile
--
Regards,
Jamie
Is the system slow? Blocking problems? Did you upgrade to sql 2005?Do a
database restore?
If not......
SQL recompiles stored procedures for you so....
Just update youre statistics an you wil be fine
Just my 2 cents
Greetz.
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"thejamie" wrote:

> We are at our slowest this time of year... Having never set the sp_recompile
> directive for our procedures, would now be a good time to tell the server to
> recompile all the stored procedures? I believe we have added perhaps 30 or
> 40 indexes since October.
> Is this an appropriate method to activate the recompile?
> --
> DECLARE @.procname varchar(150)
> DECLARE proc_recompile cursor for select name from sysobjects where xtype='P'
> OPEN proc_recompile
> FETCH NEXT FROM proc_recompile INTO @.procname
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> EXEC sp_recompile @.procname
> FETCH NEXT FROM proc_recompile INTO @.procname
> END
> CLOSE proc_recompile
> DEALLOCATE proc_recompile
> --
> --
> Regards,
> Jamie
|||I made the mistake of reading the book. It is an optimization technique.
The impression I get from the book is that this should be done fairly
regularly.
The statistics are updated each week after the indexes are rebuilt. I've
added some new indexes and thought perhaps the query plan may find a better
index for a given stored procedure, use it, and thus, get better results.
To find indexes, I ran the profiler and analyzed the results. The profiler
gave me good suggestions for indexes to add.
Slow time of the year is what I mean - a chance to tweak things without the
system toppling over.
Regards,
Jamie
"Hate_orphaned_users" wrote:
[vbcol=seagreen]
> Is the system slow? Blocking problems? Did you upgrade to sql 2005?Do a
> database restore?
> If not......
> SQL recompiles stored procedures for you so....
> Just update youre statistics an you wil be fine
> Just my 2 cents
> Greetz.
> --
> I drank alot of beer and ended up in the police department database.
> Drank more beer and learned SQL in the dark hours.
> DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
> I love SQL
>
>
> "thejamie" wrote:
|||If you or youre company developed the database\application i would say be my
guest and optimize\tune the database.
If not, be carefull adding indexes based on traces and adding 2 much indexes.
Greetz,
Flying Dutch men.
|||Hi Jamie

> Slow time of the year is what I mean - a chance to tweak things without the
> system toppling over.
> --
Be careful that your changes are not because of un-representative activity
as this could cause more problems rather than solve any. You should really
test any changes using representative workloads before implementing them.
John