Sql-server – Assign maximum memory limit for a particular database in sql server

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

You can set the job to be owned by a specific login, and use the Resource Governor to limit CPU for that login.

CREATE RESOURCE POOL [Pool_ThatJob] WITH
(
    max_cpu_percent=50, 
    cap_cpu_percent=50 -- 2012+ only 
);
GO

CREATE WORKLOAD GROUP [Group_ThatJob]
  USING Pool_ThatJob;
GO

ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

CREATE FUNCTION dbo.RGClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT CASE ORIGINAL_LOGIN() WHEN N'that_login'
    THEN N'Group_ThatJob' ELSE N'default' END);
END
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.RGClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

This won't take effect if the job is already running; it will come into play the next time the job starts (or if that user establishes any other sessions).