question

lumiga avatar image
lumiga asked

How good is resource governor?

I have a virtualized SQL server 2017 with a single application.

It has 32 Gb ram and 16 processors.

The application has high CPU pattern, sometimes reaches the 100% during a few seconds.

When reaches 100% the machine gets stalled.

I’m thinking using resource governor to guarantee the CPU never reaches 100%

I need news from the ground; the good and less good about resource governor.

sqlserver
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green avatar image
anthony.green answered

Resource governor is good as long as you get the pools and classifying functions correct, otherwise you won’t solve what you need to solve.


However if you have run away processes which max out the CPU and having server stalls that sounds like you need to go on a performance tuning mission instead and go and fix the run away queries or processes which are causing the CPU spikes.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jeff Moden avatar image
Jeff Moden answered

To be honest, 32GB of RAM is a serious diet to have 16 processors on. Consider upgrading to 128. Even that can be a bit under-powered. Even if you totally disregard the needs of TempDB, that's only 8GB per processor.

And rather than Resource Governor, someone needs to fix the app or it's connections (make SURE that M.A.R.S. is disabled!). Anything that slams 16 CPUs into the wall needs some work!

Besides, KILL SPID is much more "fun" and it gets people to come to you rather than having to hunt them down to 'splain things! :D

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.