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.
A request that is being blocked does no work. Here's an example:
Create a blocking query
use AdventureWorks2012;
go
begin tran;
update HumanResources.Department
set Name = 'testing 1 2 3'
where DepartmentID = 7;
--rollback tran;
Run a query that will be blocked by the above session
use AdventureWorks2012;
go
select *
from HumanResources.Department;
Now look at any "work" this blocked request could be doing (query sys.dm_exec_requests
for this blocked session, and then wait some time and query again to compare/contrast):
select
start_time,
status,
total_elapsed_time_sec =
total_elapsed_time / 1000,
cpu_time,
reads,
logical_reads,
writes
from sys.dm_exec_requests
where session_id = 52; -- my blocked session's spid
waitfor delay '00:00:10';
select
start_time,
status,
total_elapsed_time_sec =
total_elapsed_time / 1000,
cpu_time,
reads,
logical_reads,
writes
from sys.dm_exec_requests
where session_id = 52; -- my blocked session's spid
What you see here is a request that is doing no work, as it is not in the running
state. When it is suspended
then it is not on a processor doing any work.
Best Answer
Based on my notes from SQL Server internals (by Kalen Delaney).... below is my understanding
Query Governor Cost limit is the max no. of seconds that a query can run.
So, essentially :
Also, it should be noted that changing the
Query Governor Cost limit
will flush the entire plan cache resulting in generating a new plan afterwards.