0

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).

4
  • MAXDOP also works at the database level in sql 2017 - does that help you at all? Jun 7, 2021 at 8:42
  • @George.Palacios For my case is not helpful because I do not want to mess with the database's overall parallelism. It is this particular job that is so messy and I want a quick fix for it.
    – Alexei
    Jun 7, 2021 at 8:51
  • The DBA told us about this and our team needs to fix. What exactly did your DBA tell you to fix? Maybe this should be fixed by the DBA as has the means to tell you exactly what is going on. Some ideas: Maybe it is time to migrate the database to new hardware. Maybe it is time to invest in more RAM, CPU, SAN IO (as in GB/s connection). Could you possibly elaborate on these open questions? No, you can not set the degree of parallelism at the SPID level.
    – John K. N.
    Jun 7, 2021 at 10:57
  • @JohnK.N. To optimize the performance of a certain procedure, so this is on dev side. However, since this is a very sensitive topic that incurs application changes, I thought of a quick win without deploying anything.
    – Alexei
    Jun 7, 2021 at 12:05

1 Answer 1

4

If you have enterprise edition you can achieve this by using Resource Governor. Assuming that you have a way to identify the session that is connecting and running the misbehaving TSQL (application name, login, machine name amongst other possibilities).

MaxDoP is a rather odd parameter and everybody should have read Pedro Lopes' description of what it actually does

https://techcommunity.microsoft.com/t5/sql-server/what-is-maxdop-controlling/ba-p/1505968

Regarding Resource Governor there are lots of resources online describing how to set it up and configure - I rather like the articles on the Simple talk website.

https://www.red-gate.com/simple-talk/sql/learn-sql-server/resource-governor/

1
  • 1
    Yes, Enterprise edition is used and Resource Governor seems to be solution. Our application is not the only one misbehaving and I think they should use for throttling. Ref. to MAXDOP, it is the only way to I know for a quick throttling, but I guess the Resource Governor allow way more customization. Thanks.
    – Alexei
    Jun 7, 2021 at 11:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.