August 28, 2007 at 11:15 am
in a nutshell i have a very poorly performing stored proc.
when i take the sql out of the sp, it runs like a dream. 2 seconds.
executing the proc, takes well over 3 minutes!!! what is up with that?
what should i be checking first?
here is what i've tried.
1. updating the statistics for the indexes that should be used in the query execution plan
2. recompiling the stored proc
😎
August 28, 2007 at 11:37 am
Common issue for these types of things can be the stored execution plan. If you have a query that is based on variabl range size the first execution plan can be less optimized for a different range size. As well other factors in your SP related to inputs can be the reason.
1) You can try adding WITH RECOMPILE to the SP to see if that improves.
2) Post here the SP so we can understand what you have and better be able to offer our input.
August 28, 2007 at 12:17 pm
yep ... it was totally the stored exec plan, but i guess i didn't hit all the necessary statistics to force it to recompile to use the indexes favorably.
i went ahead and created a script that would update ALL out of date statistics.
ran the sp_recompile on the sp in question.
and VOILA!! back in business.
here is my script:
declare @today datetime
set @today=(select getdate())
SELECT distinct 'update statistics '+ o.name
FROM sys.objects o
JOIN sys.indexes i ON o.object_id = i.object_id
where o.name not like 'sys%'
and (@today-STATS_DATE(i.object_id, i.index_id)) is not null
and (@today-STATS_DATE(i.object_id, i.index_id))>10
THANKS.
😎
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply