Is there any reason NOT to use the approach you've defined? Absolutely.
Imagine you had bought a car - a car that when you hit 50MPH the engine starts to overheat. Would your reaction to this situation be to artificially limit the car to 49MPH, or to find out what the fault is with the engine?
Why should you limit your car to 49MPH? The manufacturer stated ...
Erik Darling mentioned the biggest practical reason for not using WSRM in a comment on your question:
...there's no reciprocal limiting of CPU use in other processes. SQL Server may not use those two cores, but other things may use the other 30 SQL Server is using. It's a crapshoot, really.
If this is working for you, then stick with it - we're all busy, ...
I change Resource Governor settings quite often without restarting SQL Server. I am not aware of any need to restart SQL Server after changing Resource Governor settings except possibly for a classifier function change. A few things that might be helpful to know:
Once a session is assigned to a Resource Governor workload group it stays there, even if the ...
The short answer is 'Yes'.
You can use RG to limit the CPU consumption of that process.
Note that RG is only available in Enterprise and Data Center editions of SQL Server 2008R2. Use the Microsoft documentation excellent walk-through to learn about using RG: Resource Governor How-to Topics
Longer answer - RG does not address the root cause, it's a band ...
The bad news
The only way to throttle the sessions you want is to uniquely identify them. If you cannot do this, then you cannot selectively throttle resources using the Resource Governor.
The good news
There are loads of ways to stratify connections into different resource pools. You may need the cooperation of the person(s) managing the application to ...
This is the opposite of what you should do.
When you have queries waiting on RESOURCE_SEMAPHORE, they're waiting for other queries to finish running, and give up their memory. The queries that are waiting aren't even running.
Letting all queries use more memory will only make the problem worse. You'd wanna configure resource governor to make queries ask ...
Resource governor classifier functions only run during the Login process. Impersonation via EXECUTE AS does not trigger the classifier function. EXECUTE AS is how SQL Server Agent runs jobs in the context of another login or user.
Specifying the Run As ... user or modifying the Job Owner does not change how SQL Server Agent logs into SQL Server. The ...
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 ...
This MSDN page states the memory settings only affect query memory:
Resource Governor Resource Pool
MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT
These settings are the minimum and maximum amount of memory reserved for the resource pool that can not be shared with other resource pools. The memory referenced here is query execution grant memory, not ...
There's nothing built in to SQL Server that will do this. If you don't feel like using sp_who3 and partying like it's 2002, here's another method.
If you just want to see what's happening now, run it like this:
EXEC dbo.sp_WhoIsActive @get_plans = 1, @get_locks = 1
If you want to use it long term to find out what users are up to, ...
If you want to limit the CPU of a resource pool then you need to use the CAP_CPU_PERCENT setting:
This settings is a hard cap limit on the CPU bandwidth for all
requests in the resource pool. Workloads associated with the pool can
use CPU capacity above the value of MAX_CPU_PERCENT if it is
available, but not above the value of CAP_CPU_PERCENT.
From the output you posted I can see
MEMORYCLERK_SQLQERESERVATIONS 39874 (MB)
The value is 38G which is huge. Why is SQL Server operations(sortand hash) requesting so much memory
What is SQLRESERVATIONS
SQL Server memory allocated during query execution for Sort and Hash operations. Do you really think 38 G is what the sort and hash operations ...
Resource Governor Concepts
Resource pools. A resource pool, represents the physical resources of the server. You can think of a pool as a virtual SQL
Server instance inside of a SQL Server instance.
Workload groups. A workload group serves as a container for session requests that have similar classification criteria. A workload
allows for ...
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 ...
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 ...
Think of the resource pool as physically dividing the CPU, memory, and IO of the server. This can be useful when you need to limit resources used by overlapping workloads. The workload groups further limit resources used at a query level within a pool. This can be useful when you need to limit resources used within a workload, perhaps to improve throughput.
I actually ended up engaging Microsoft on this. The explanation that the engineer gave is that RG doesn't do a good job at throttling an OLTP vs. OLAP type workloads on the same instance. Our use case was a server with 1000's of tiny queries taking 1-2 ms, with occasional queries taking 6-7 seconds, with the longer queries being the ones we wanted to ...
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 :
a non-zero or a negative value will disallow execution of any query that has an estimated cost exceeding that value.
Zero which is default - turns off the query governor ...
Yes, those two scenarios will result in the same resource allocation - in theory.
In scenario 1, if there is CPU pressure, the two workloads running in the "20% resource pool" will share that CPU "equally." How equally they are able to share the 20% can depend on a lot of things that are workload specific, given how SQL Server's scheduling algorithms work.
Im actually using a similar setup with 1TB of the database.
Has anyone had a similar issue and resolved it with either option, confirming viability of the option for us?
Im using backup on secondary only.Even in few outages, I tried to use copy only full backups and log backups to recover them.
No issues that using copy only backups.
A "volume" is either a drive letter, or a raw partition in SQL Server.
For a resource pool configured for X maximum IOps per volume where a query uses tempdb, splitting that tempdb across multiple volumes on a single physical disk, SSD or not, with the data on a single volume may well give you the granularity you desire. As always, I'd test it.
Ideally we'd be able to limit these queries to 50% of available I/O capacity. Unfortunately SQL Server Resource Pool's IOPS throttling is not percentage-baed nor volume-specific.
Correct. Not to delve too deep into this since it isn't configurable - but let's take a quick second and think about that sentence and what you're really talking about here.
Make sure your group name returned matches (identically) to your actual workload group name. Some times, I've found, it's helpful to reconfigure even if it doesn't say it needs to be done.
-- Create a test DB which our CF will be based on
CREATE DATABASE RGDB;
-- enable RG
ALTER RESOURCE GOVERNOR RECONFIGURE
CREATE FUNCTION dbo....
I have three suggestions
Look into your Data Warehouse designs. It should be optimized to let ad-hoc queries run relatively quickly. Analysis Services can help with this.
Off load DW queries into no busy hours. I find this works really well for companies I have worked at. One should not run "big" queries when most of your users are accessing the system. ...
we are using 2012 - the first implementation of columnstore indexes. Further analysis showed that the crashes always happened when a columnstore index was being created and the server was under memory pressure. We got rid of these columnstore indexes and we are no longer experiencing the errors.
It seems reasonable to assume, per the documentation and indeed the names of the parameters themselves, that MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME control the maximum quantity of I/Os per second per volume.
i.e. as in your question, if you have three volumes in the resource pool, and set MAX_IOPS_PER_VOLUME to 300, then you'd have a maximum total IOPS ...
Do you know what pages are getting the latches? DBCC heavily uses tempdb as @ShawnMelton was alluding. Have you considered that tempdb contention could be the source of your pagelatches? You should be able to investigate that with sp_whoisactive if you aren't already. How many data files do you have for tempdb? It sounds like you've only got one, try ...
Yes, your classifier function can identify the job that's running through APP_NAME(), which will actually contain a representation of the job_id. This means your jobs don't have to run as different users, but the classifier function does need to be able to identify jobs by id.
See the white paper I wrote back in 2008 for an example, in the section "Limiting ...