Sql-server – Sql Server Resource Governor – track IO

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.

An update - I still don't know how best to detect what type of throttling has gone on, but I did find the culprit in my case. It does seem that the CPU CAP to 75% was causing a much higher decrease in performance than a 25% drop should suggest. Raising the CPU Cap to 100% fixed the problem.

A connect article seems to validate this might be a problem with the CPU Cap implementation itself.

Its not the best answer to this question, and if someone still wants to answer the original "how to tell what throttling is going on..." question. I'd be amenable.

If you are struggling to understand why your queries are being throttled so severely, this might be a solution worth trying for it.

http://connect.microsoft.com/SQLServer/feedback/details/781335/resource-governor-feature-cap-cpu-percent-does-not-work-as-expected