6

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?

5

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

3

If you're setting MAXDOP at half the cores, and other queries are still getting sidelined, you may be dealing with contention for memory or disk I/O. How much RAM is in the server, and how large is the application database?

Take a look at these perfmon counters when performance takes a nosedive, and you can get some idea if the server needs more memory or a faster disk subsystem:

SQLServer:Buffer Manager

  • Page reads/sec
  • Page life expectancy
  • Buffer cache hit ratio

If Page reads/sec skyrockets, and the other two drop significantly, the troublesome query is probably trashing your buffer pool and causing a ton of disk I/O. You could then either work on tuning the query, or throw more hardware at it (more RAM, faster disks).

  • Good point, we have 12 GB and the database is 200GB. But our Buffer cache hit ratio is 100% and always stays very high. I also do not see unusually high I/O. But I will remember to limit the maximum memory for the new dedicated instance. – Twinkles Oct 21 '13 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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