Sql Server 2000 Stored Procedure Prevent Parallelism or something?

Question

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.

Answer 1

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.

Are you sure that it is not the fetching of these params ahead of the SP's execution that's not causing your slowness? With bypassing the population of the params you could be oversimplifying your issue.

Where do these params come from? How are they populated? It seems from your question that you've isolated the stored proc and found out that it might not be the issue.

Answer 2

try adding SET ARITHABORT ON at the top of the procedure.

as seen here: http://stackoverflow.com/questions/2465887/why-would-set-arithabort-on-dramatically-speed-up-a-query

Answer 3

If you have made many changes to the table and not run a re-index or defragment on the tables in question you probably should. Check out this article. The reason i suggest this is because the procedure at one time was fast and now over time it has degraded performance. I don't think making changes to an already existing procedure that was tested and worked well at one time should change on account of degraded performance over time. This usually only treats the symptoms not the actual problem.

Answer 4

Could it be a problem with contention? Does this store procedure run at a particular time when other heavy lifting is also happening?

Answer 5

If the only difference between the two query plans is parallelism, try putting OPTION (MAXDOP 1) at the end of the query to limit it to a serial plan.

As to why they are different, I'm not sure, but I remember the SQL Server 2000 optimizer as being, um, finicky. Similar to your case, what we usually saw was that ad-hoc query batches would be fast and the same query via sp_executesql would be slow. Never did fully figure out what was going on.

Serial v parallel can definitely explain the difference in speeds, though. On SQL Server 2000, parallel plans use all the processors on the machine, not just the ones it needs:

If SQL Server chooses to use parallelism, it must use all the configured processors (as determined by the MAXDOP query hint configuration) for the execution of a parallel plan. For example, if you use MAXDOP=0 on a 32-way server, SQL Server tries to use all 32 processors even if seven processors might perform the job more efficiently as compared to a serial plan that only uses one processor. Because of this all-or-nothing behavior, if SQL Server chooses the parallel plan and you do not restrict the MAXDOP query hint[...], the time that it takes SQL Server to coordinate all the processors on a high-end server outweighs the advantages of using a parallel plan.

By default, I believe the server-wide setting of MAXDOP is 0, meaning use as many as possible. If you recently upgraded your database server with more processors to help performance, that could ironically explain why your performance is suffering. If that's the case, you might try setting the MAXDOP hint to the number of processors you had before and see if that helps.