All geek questions in one place

Sql Server 2000 Stored Procedure Preventing Parallelism or Something Else?

I have a huge disgusting stored procedure that was not slow a couple of months ago but now has. I barely know what this does, and I'm in no way interested in rewriting it.

I know that if I take the body of a stored procedure and then declare / set the values ​​of the parameters and run them in the query analyzer, it works more than 20 times faster.

From the Internet, I read that this is probably due to a poor cached query plan. So, I tried to run sp with "WITH RECOMPILE" after EXEC, and I also tried putting "WITH RECOMPLE" inside sp, but none of them helped even a bit.

When I look at the sp vs query execution plan, the biggest difference is that sp has “Parallelism” operations all over the place, and the query doesn't. Could this be the cause of the speed difference?

Thanks, any ideas would be great ... I'm stuck.

+3
source share
5 answers

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

, , , , SQL Server 2000 , , . , , , sp_executesql . , .

v . SQL Server 2000 , , :

SQL Server parallelism, ( MAXDOP) . , MAXDOP = 0 32-way , SQL Server 32 , , . - " ", SQL Server , MAXDOP [...], , SQL Server .

, , MAXDOP 0, . , , , . MAXDOP , , , .

+4

, , , . . , , , . , , , - . , .

+1

, , / , 20 .

, SP, ? , .

? ? , , , .

0

? , ?

0

Source: https://habr.com/ru/post/1737684/

More articles:


All Articles