sp_recompile doesn''t

Question

Post reply

sp_recompile doesn''t

colin.Leversuch-Roberts

SSC Guru

Points: 52643
More actions
March 16, 2005 at 7:34 am

#102437

I had a poor performing proc .. I identified 2 missing indexes. I added the indexes and updated stats. The offending proc is not taking advantage of the new indexes. I've issued a sp_recompile but to no effect. As this is production I can't do much more.
In a test environment I had to physically drop the procedure and recreate it before it used the new indexes.
Even an edit to the procedure did not cause it to change its query plan.
This doesn't quite fit with what I'd expect .. any thoughts ??
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

How about modifying the stored procedure to force it to use the indexes with query hints?

K. Brian Kelley
@kbriankelley

sa24 SSCrazy Eights Points: 8027 More actions · Answer 2

you can also try to clear the buffers by executing dbcc freeproccache and see if that helps.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

This generally isn't recommended in a production system as it will drop execution plans on all objects, not just the one in question.

K. Brian Kelley
@kbriankelley

sa24 SSCrazy Eights Points: 8027 More actions · Answer 4

Brian is right. I didn't see that it was production.

noeld SSC Guru Points: 96590 More actions · Answer 5

I have been able to run an ALTER on some production sp (with the same code, of course) and those were recompiled on the spot. What is even more you could see the speed difference in the profiler trace that was running LIVE!

Don't ask me why Profiler was running in production. It was a proof I had to make to the customer at their request

* Noel

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

Yes the alter could be a way .. The production system is very controlled ( by the DBA team including me ) I might be able to do that. Generally any change has to be authorised and signed off so I was more than a little annoyed about this proc.

The strange thing is that I added the indexes and the cluster was then failed over to add a new node .. Stats were updated on all tables later on in the night so it was somewhat unusual. Another proc which used the same indexes picked them up fine ( they were only indexes on join columns for the queries too -- don't ask < grin > )

Still life is never boring !!!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/