Sql Server 2000 Stored Procedure Prevent Parallelism or something?
Posted
by user187305
on Stack Overflow
See other posts from Stack Overflow
or by user187305
Published on 2010-03-19T13:58:01Z
Indexed on
2010/03/19
15:01 UTC
Read the original article
Hit count: 246
I have a huge disgusting stored procedure that wasn't slow a couple months ago, but now is. I barely know what this thing does and I am in no way interested in rewriting it.
I do know that if I take the body of the stored procedure and then declare/set the values of the parameters and run it in query analyzer that it runs more than 20x faster.
From the internet, I've read that this is probably due to a bad cached query plan. So, I've tried running the sp with "WITH RECOMPILE" after the EXEC and I've also tried putting the "WITH RECOMPLE" inside the sp, but neither of those helped even a little bit.
When I look at the execution plan of the sp vs the query, the biggest difference is that the sp has "Parallelism" operations all over the place and the query doesn't have any. Can this be the cause of the difference in speeds?
Thank you, any ideas would be great... I'm stuck.
© Stack Overflow or respective owner