Questions tagged [resource-governor]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
24 views

SQL Server Resource Governor for Buffer Pool [duplicate]

We plan to have multiple databases on a Server Instance. Does Resource Governor allow buffer pool to be allocated for each database in SQL Server 2017?
5
votes
1answer
158 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
1answer
34 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 ...
10
votes
2answers
778 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 ...
2
votes
1answer
230 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 ...
4
votes
2answers
1k 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 ...
1
vote
1answer
306 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 ...
3
votes
0answers
144 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... ...
1
vote
1answer
155 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 ...
1
vote
0answers
357 views

Resource governor classifier function to find free pool

I have a SQL Server 2016 instance with around 30 db's. I want to equally distribute resources between them and I use Resource Governor for it. So, I created ~30 pools with the same settings, and my ...
4
votes
1answer
697 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 ...
0
votes
1answer
181 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 ...
2
votes
1answer
733 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-...
1
vote
2answers
872 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 ?
1
vote
1answer
356 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
1answer
67 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 ...
5
votes
2answers
8k 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 ...
1
vote
0answers
70 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.
5
votes
1answer
110 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 ...
1
vote
1answer
1k 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?
2
votes
1answer
76 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....
2
votes
1answer
206 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 ...
3
votes
1answer
843 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 ...
1
vote
1answer
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 ...
2
votes
1answer
884 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
vote
1answer
579 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 ...
5
votes
1answer
869 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 ...
1
vote
2answers
7k 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 ...
6
votes
2answers
15k 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 ...