June 5, 2003 at 11:46 am
SQL 2000, sp2 w/all up to date hotfixes on Win2K server
I have a stored procedure that is running with an index hint. I have removed the index hint, drop and recreated the stored procedure, run a sp_recompile on the stored procedure waited a few days and SQL is still using the forced index. What am I missing?
June 5, 2003 at 1:00 pm
Have you run a query plan to see if it is natural for this to use that index?
June 5, 2003 at 1:45 pm
Yes I have run a query plan. I have created the same stored procedure I am having problems with under a new name, run a query plan and SQL chooses a different index than the one being forced and the performance is much better.
June 6, 2003 at 4:26 am
Add the line
WITH RECOMPILE right before the AS in the existing SP and run a few times.
Then go back and remove it.
I have seen instances where the execution plan in the cache wouldn't clear with sp_recompile.
However, I am a bit perplexed with the fact it found one when you dropped and recreated the object since the object wuld have a new object id. Did you do a DROP PROEDURE and a CREATE PROCEDURE or what were the steps you did exactly.
June 6, 2003 at 9:17 am
Here is what I did. I first tried to take out the index hint by altering the stored procedure and running a sp_recompile. No success. Then I dropped and recreated the stored procedure taking out the index hint - no success. So I tried running an sp_recompile again. No success. Waited a few days - nothing.
Now I tried your suggestion and dropped and recreated the stored proc with recomile. After about 20 times it seemed to pick up a new plan and I thought it was fixed and I removed the with recompile syntax by dropping and recreating. It has now gone back to the forced index. Could it be that I have old plans hanging out in cache and it had started using those again?
Thanks for the help,
Madison
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply