Questions tagged [resource-governor]
The resource-governor tag has no usage guidance.
36
questions
-1
votes
0
answers
4
views
When exactly do I use min cpu percent over max cpu percent?
I'm exploring resource governor and going through the below example from msdn. I have not fully understood about the example:
By creating a separate resource pool for each department, you can
assign ...
- 3,597
0
votes
1
answer
34
views
What does resource governor classify as CPU contention?
Suppose I have 8 CPUs, and therefore I will have 576 worker threads.
When multiple queries are executing, then each query can be assigned one or more threads. And each thread executes on a cpu.
On a ...
- 3,597
0
votes
0
answers
21
views
MSSQL Resource Governor reinitialization
I'd like to use Resource Governor for resource management depending on a session database.
The problem - application could take data from different databases ( no cross-database requests ).
To avoid ...
- 183
0
votes
1
answer
300
views
What properties are accessible in the resource governor classifier function?
I'm learning about the resource governor classifier function: https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defined-function?view=sql-...
- 3,597
3
votes
1
answer
340
views
Resource Governor and Resource Pool CPU Utilization Configuration
I have been trying to understand more in-depth how the resource governor resource pool limits work in conjunction with multiple user defined pools.
I've been trying to follow along the table in this ...
- 164
0
votes
3
answers
661
views
Rollback a transaction using single thread/multi thread
Would it be possible to rollback a transaction using a single thread?
I tried to find an answer to this question, but I couldn't find one that provided references. I read about rollback mechanisms. ...
- 236
0
votes
0
answers
199
views
MSSQL Server Resource Governance alternative
I've been looking into solutions for managing CPU sharing over multiple apps accessing the same SQL server database engine.I found that Resource Governance is one perfect way for me to define those ...
- 185
1
vote
1
answer
933
views
Best-practice use of Resource Governor in SQL Server 2017 [closed]
I am working on a server that houses both an OLTP workload and a data warehousing/reporting workload. The OLTP system requires sub-second (in the milliseconds) response time while the reporting ...
1
vote
1
answer
249
views
SQL Server Resource Pool and Workload Groups
Below is the scenario:
Scenario 1:
I got two workload groups assigned in the same resource pool with MAX CPU 20%. In the event of CPU bottleneck, the limit will come into effect with 10% each ...
- 13
5
votes
1
answer
504
views
Disabling Resource Governor in SQL Server
Should we restart SQL Server after disabling Resource Governor? What is the best practice?
Why I am asking this, because I am worried about the existing open sessions in SQL Server. Will their ...
0
votes
1
answer
793
views
Using Resource Governor to limit query CPU usage for same user
I have a remote MS SQL Server and I have several types of queries.
I run some queries that are part of a low-latency production application, and need to ensure they get domne in a timely manner.
But ...
- 345
10
votes
2
answers
9k
views
Limit max CPU usage SQL SERVER with WSRM
I have a physical server running one instance of SQL Server.
I notice that quite often this server is running at 100% CPU usage.
My IT team is not happy about this, and suggested we reserve 2 of the ...
- 345
2
votes
1
answer
466
views
Using Resource Governor and allocate more memory to satisfy high Memory Grant request by queries?
On a SQL Server 2016 database server, I have few queries which are really slow and which ask for more memory (queries are not optimized). This is making the whole database slow as lots of users are ...
- 1,315
4
votes
2
answers
5k
views
Limiting the CPU/Memory utilization for a Query in SQL server 2008R2
We have an AD-HOC process from the application team which is fetching data from one of our heavily used OLTP databases in SQLserver to migrate the data into MongoDB.
During the performance measure we ...
- 2,169
1
vote
1
answer
647
views
HA SQL AlwaysOn backup using Copy Only + Logs from secondary replica
I'm hoping to elicit some guidance from your collective DBA experiences.
Here's our problem:
Full backups of our larger databases (around 3TB) are consuming too much resources such that we are ...
- 113
3
votes
0
answers
296
views
Resource Governor IOPs counters - what are they counting?
When profiling a resource group that is known to be write-heavy, I am seeing a number of the "IOps write" counters remain at zero or increment unexpectedly. specifically, the following counters...
...
- 4,626
1
vote
1
answer
636
views
What does "volume" mean in MAX_IOPS_PER_VOLUME?
When setting MAX_IOPS_PER_VOLUME in a Resource Pool, what exactly does "volume" mean? Specifically, how many "volumes" would be the following cases:
Locally attached disk that's split into two ...
- 751
4
votes
1
answer
2k
views
Using Resource Governor with SQL Agent Jobs
I'm trying to setup Resource Governor for limiting IOPS for processes running using SQL agent jobs. The classifier function is set to identify a particular login (any spid that is running using that ...
- 512
0
votes
1
answer
347
views
Workload group "Importance" vs. Resource pool IOPS throttling
Our SQL 2014 server has a blazing-fast tempdb drive (2800 IOPS) but a much slower data drive (500 IOPS). Our application runs a few long-running reporting queries that are I/O-intensive and we'd like ...
- 751
2
votes
1
answer
2k
views
Resource Governor not limiting CPU / RAM
I have an application that is consuming a lot of resources, actually this application drives CPU to. 99% which makes the server unusable (I think there is nothing I can do about this as is a third-...
- 520
2
votes
2
answers
2k
views
Identify top logins\users consuming resources
Is there a way to identify top database users who are actually consuming high resources with their executed queries ?
- 161
1
vote
1
answer
838
views
SQL Server Resource governor user-defined resource pools
In the context of the SQL Server resource governor, why would I create a user-defined resource pool? It seems like you could get almost the same result by using the default resource pool for ...
2
votes
1
answer
100
views
Resource Governor seems to increase resource usage
We are in the unfortunate position of running a data warehouse on the same instance as our production OLTP system. Recently a "power user" joined our company and has begun running SQL statements ...
- 294
6
votes
2
answers
18k
views
There is insufficient system memory in resource pool to run this query
Over the past year we have been suffering a situation where many different queries at the same time will fail with the following error (or a variation in a different resource Governor group): There is ...
- 189
1
vote
0
answers
91
views
service broker/resource govenor
Is it possible to configure service broker to work with resource governor? Currently all commands are being sent to the internal group but I want them to go elsewhere.
- 189
5
votes
1
answer
173
views
Does Resource Governor help protect page life expectancy?
I have several large tables that I need to copy into partitioned copies on a transactional database.
I am worried about the hit that this will cause to performance of my production applications.
In ...
- 2,496
2
votes
1
answer
2k
views
What is the different between resource pools and workload groups in sql server?
In SQL server one can use resource pools and workload groups, what is the difference between them? Why should I use one over the other?
- 117
2
votes
1
answer
111
views
SQL Server 2014, Volumes in Resource Governor
I'm looking at Resource Governor on 2014 and how it relates to volumes. I can see where to set up MAX|MIN IOPS PER VOLUME on a resource pool. That's not keyed by volume, however sys....
- 24.8k
2
votes
1
answer
269
views
Sql Server 2014 IO Resource Governor
I've have configured the resource governor for cpu, memory, and IO. I have my dbcc db checks running under a pool and workload that only allows 10% of memory and cpu, and only 100 IOPS. I have also ...
- 544
3
votes
1
answer
1k
views
Run SQL Server agent job in a particular resource governor pool
I read Considerations for Writing a Classifier Function on MSDN. The only way I see possible is to create a separate user account for each resource governor pool and to classify sessions based on that ...
- 542
1
vote
1
answer
1k
views
Resource Governor on 2014 - CPU Not Being Throttled
We have a specific query ran from our platform that consumes a lot of CPU on a box that already has Enterprise, so Resource Governor seemed like a good solution. I'm working on configuring it on our ...
- 31
3
votes
1
answer
1k
views
Query Governor Cost Limit relationship with Time
Based on Technet, Query Governor Cost Limit refers to the estimated elapsed time, in seconds. This meets this excerpt from the Microsoft SQL Server 2008 Administrator's Pocket Consultant book.
...
- 1,123
1
vote
1
answer
1k
views
Troubleshooting Resource Governor / Classification Function [closed]
I've been trying to configure / troubleshoot the resource governor on a sql server 2014 instance. I can't get any work into anything but the default resource pool. I've been following this ...
- 97
5
votes
1
answer
1k
views
How to troubleshoot how resource governed query is being throttled in SQL 2012?
I have a parallelized query on SQL 2012 SP1 (ent) that is running in a user defined resource governor pool much much slower (2 seconds versus 125 to 140 seconds under user defined pool = ~75 times ...
- 383
2
votes
2
answers
10k
views
Assign maximum memory limit for a particular database in sql server
In my SQL-Server instance, there are many databases. I want to restrict the memory size of each database (for example 8 GB). When I tried using Resource Governor, there are options like ...
user2967729
6
votes
2
answers
18k
views
Limit CPU usage without Resource Governor
We have an SQL Server 2008R2 Standard Edition with several databases belonging to different applications on a 16 core server.
One recently introduced application is regularly executing expensive ...
- 2,361