Sample Header Ad - 728x90

How to reduce the maximum degree of parallelism at session level?

0 votes
1 answer
353 views
I have inherited a legacy application that includes a job that creates performance issue at instance level. The DBA told us about this and our team needs to fix the stored procedure that is the most resource intensive. Unfortunately, the logic is quite a mess and I am aiming for quick wins until we manage to rewrite the logic. The job executes three stored procedures that consume a huge amount of CPU and I/O. I have run sp_who2 active while the job was running and noticed that about 8 threads were running for its SPID. I am wondering if I can temporarily reduce the strain on the server by reducing the maximum degree of parallelism. I know about MAXDOP , but it seems to be only applicable at instance, database or query level (through hints). I would like an option to set this at session (SPID) level, to avoid changing all the queries in the computation. The first comment from here indicates that DBCC OPTIMIZER_WHATIF(CPUs, 1) might be an ugly hack that does the trick, but I cannot find if this change applies to session level or instance level (I do not want to mess with anything on the server except for the problematic job).
Asked by Alexei (1191 rep)
Jun 7, 2021, 08:39 AM
Last activity: Jun 7, 2021, 12:06 PM