16
votes
Limit max CPU usage SQL SERVER with WSRM
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 ...
- 5,570
12
votes
Limit max CPU usage SQL SERVER with WSRM
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 ...
- 29.8k
7
votes
Disabling Resource Governor in SQL Server
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 ...
- 32.6k
6
votes
Accepted
Using Resource Governor with SQL Agent Jobs
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 ...
- 70.5k
6
votes
Accepted
Limiting the CPU/Memory utilization for a Query in SQL server 2008R2
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 ...
- 4,098
6
votes
Identify top logins\users consuming resources
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.
Grab sp_WhoIsActive
If you just want to see what's ...
5
votes
Using Resource Governor to limit query CPU usage for same user
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 ...
- 5,001
5
votes
Accepted
Using Resource Governor and allocate more memory to satisfy high Memory Grant request by queries?
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 ...
4
votes
Accepted
Resource Governor not limiting CPU / RAM
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. ...
- 32.6k
4
votes
Accepted
Resource Governor and Resource Pool CPU Utilization Configuration
Question 1
Do user defined resource pools consider the default pool in their
calculations?
Yes, they do. You can verify it by editing the minimum CPU for the default pool and trying to add another ...
- 5,926
3
votes
What does resource governor classify as CPU contention?
From the documentation of MIN_CPU_PERCENT and MAX_CPU_PERCENT. Formatting and emphasis mine
These settings are the minimum and maximum guaranteed average CPU bandwidth for all requests in the ...
- 5,512
3
votes
Accepted
Rollback a transaction using single thread/multi thread
Rollback are mostly single threaded (this is why rollback could take way longer then the transaction you are rollbacking).
https://www.brentozar.com/archive/2014/03/happens-issue-kill/
You cannot &...
- 3,257
2
votes
Best-practice use of Resource Governor in SQL Server 2017
Supporting a hybrid workload requires more than just resource governance.
The docs call this scenario Real-Time Operational Analytics, and it's a bunch of features all working together.
First you ...
- 47.6k
2
votes
Accepted
SQL Server Resource Pool and Workload Groups
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 "...
- 29.8k
2
votes
SQL Server Resource governor user-defined resource pools
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 ...
- 32.6k
2
votes
There is insufficient system memory in resource pool to run this query
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 ...
- 189
2
votes
Accepted
SQL Server 2014, Volumes in Resource Governor
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 ...
- 70.5k
1
vote
Accepted
What properties are accessible in the resource governor classifier function?
I can't find this being documented anywhere, for some strange reason. Below is a list that I collected some years ago when I evaluated this.
Remember that this function is called at connect time. ...
- 17.9k
1
vote
Accepted
HA SQL AlwaysOn backup using Copy Only + Logs from secondary replica
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 ...
- 1,960
1
vote
What does "volume" mean in MAX_IOPS_PER_VOLUME?
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 ...
- 70.5k
1
vote
Workload group "Importance" vs. Resource pool IOPS throttling
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 ...
- 35.5k
1
vote
Identify top logins\users consuming resources
Sp_who3 would provide you with useful info from users who are executing queries in real time.
SELECT des.session_id ,
des.status ,
des.login_name ,
des.[HOST_NAME] ,
der.blocking_session_id ,
...
- 1,064
Only top scored, non community-wiki answers of a minimum length are eligible