patternsqlMinor
Limit CPU usage without Resource Governor
Viewed 0 times
withoutresourcelimitgovernorusagecpu
Problem
We have an SQL Server 2008R2 Standard Edition with several databases belonging to different applications on a 16 core server.
One recently introduced application is regularly executing expensive queries that lead to 100% CPU usage. Of course the other applications are reporting performance issues.
The Resource Governor seems like a suitable tool to put the reins on the rogue application, unfortunately it is only available in the Enterprise Edition.
Since the other applications are rather simple, I tried to get the problem under control by reducing the "max degree of parallelism" of the instance, so that a single query can not bring down everything. While that succeeded in keeping the CPU load at 50%, it did surprisingly nothing to keep other applications from being bogged down.
Now we have decided to move the databases for the new application to a dedicated instance, but what would be the best configuration for this instance? Should I keep the MAXDOP-setting, use a CPU-affinity mask or is there another option to limit CPU usage that I am not aware of?
One recently introduced application is regularly executing expensive queries that lead to 100% CPU usage. Of course the other applications are reporting performance issues.
The Resource Governor seems like a suitable tool to put the reins on the rogue application, unfortunately it is only available in the Enterprise Edition.
Since the other applications are rather simple, I tried to get the problem under control by reducing the "max degree of parallelism" of the instance, so that a single query can not bring down everything. While that succeeded in keeping the CPU load at 50%, it did surprisingly nothing to keep other applications from being bogged down.
Now we have decided to move the databases for the new application to a dedicated instance, but what would be the best configuration for this instance? Should I keep the MAXDOP-setting, use a CPU-affinity mask or is there another option to limit CPU usage that I am not aware of?
Solution
You can use Windows System Resource Manager (WSRM), which is a feature in Windows Server (not sure of minimum version, but definitely 2008 R2+).
This will allow you to control the amount of CPU used by a process, so if you hadn't already separated out the rogue application to its own SQL Server instance, you would have had do that anyway.
At that point you can set whatever
Setting the CPU affinity mask is an option. You'd want to completely isolate the application onto its own set of cores to eliminate contention (note: watch your NUMA nodes). If you have those extra cores available, then go for it, but I'd prefer the WSRM solution because when that application is idle, all the CPUs can be used by other applications.
This will allow you to control the amount of CPU used by a process, so if you hadn't already separated out the rogue application to its own SQL Server instance, you would have had do that anyway.
At that point you can set whatever
MAXDOP you want, as the process will never exceed the maximum limits you set in WSRM.Setting the CPU affinity mask is an option. You'd want to completely isolate the application onto its own set of cores to eliminate contention (note: watch your NUMA nodes). If you have those extra cores available, then go for it, but I'd prefer the WSRM solution because when that application is idle, all the CPUs can be used by other applications.
Context
StackExchange Database Administrators Q#51887, answer score: 5
Revisions (0)
No revisions yet.