Category: Resource Governor

Just like sp_configure, Resource Governor is configured in two steps. The first step is to specify the configuration you want, the second step is to ALTER RESOURCE GOVERNOR RECONFIGURE.
But unlike sp_configure which has a “config_value” column and a “run_value” column, there’s no single view that makes it easy to determine what values are configured, and what values are in use. It turns out that the catalog views are the configured values and the dynamic management views are the current values in use:

Read on for a variety of scripts to help configure resource governor.

Comments closed

MAXDOP by Username in Azure SQL DB

Published 2023-07-31 by Kevin Feasel

Jose Manuel Jurado Diaz comes up with a solution:

Azure SQL Database is a powerful platform that provides managed database services with built-in intelligence and robust resource management. While Azure SQL Database doesn’t have a direct implementation of the traditional Resource Governor feature available in SQL Server, we can explore a pseudo-Resource Governor approach using user-defined functions and custom tables. In this article, we’ll discuss the concept, present a sample implementation using a custom function, and highlight the possibilities it opens up for controlling CPU resources in Azure SQL Database.

Click through for the UDF and how to use it. My first inclination was to say that I couldn’t see it working well at all under load, though on second thought, performance won’t be bad like having a UDF execute for each row in a table, so it’s probably more of a manageable overhead.

Comments closed

Resource Governor and Scheduler Domination

Published 2021-11-09 by Kevin Feasel

Forrest McDaniel shares an example of Resource Governor blocking query activity:

Ok, I get it, scheduling queries can be complicated. See this and this and maybe this and this too but only if you have insomnia. I still thought I kinda understood it. Then I started seeing hundreds of query timeouts on a quiet server, where the queries were waiting on…what? CPU? The server’s at 20%, why would a query time out after a minute, while only getting 5s of CPU, and not being blocked?
It was Resource Governor messing up one of my servers recently in a way I never expected.

Click through for the story. I’m not sure I’ve experienced this when running Resource Governor, but Forrest has an easy demo which replicates the problem.

Comments closed

Workload Management in Azure Synapse Analytics

Published 2021-06-11 by Kevin Feasel

Reiss McSporran starts a new series on workload management in Azure Synapse Analytics:

So you’ve started using Synapse SQL Pools, you’ve scaled up to improve performance, but your queries aren’t going as fast as you’d hoped. You take a look at resource utilisation and you see that as you scale up, your total resource utilisation per query goes down. But didn’t we scale up to allow our query to use more resource and run quicker? What’s going on here? A little thing called Workload Management.
What is Workload Management? At a very high level, Workload Management allows you to control how your workload uses system resources. The main problem here is defaults!

Click through for the full story. Or at least the full part 1 of the even fuller story.

Comments closed

Resource Governor and Memory Grants

Published 2021-01-29 by Kevin Feasel

Deepthi Goguri explains how you can use Resource Governor to control memory utilization:

Queries doesn’t start unless available memory is 150% of requested query memory because sql server will always allocate a little bit of additional buffer memory. Lets take the 100MB query which will get 150MB with additional buffer. What if this query only get 100MB and doesn’t get that additional buffer in the beginning to run the query? In that case, the query will wait until the 25x the query cost which is in seconds. When the timeout occurs and if the exact grant (100MB) is available without the buffer amount then the query starts to run this time. In other scenario, even after waiting for so long and after the timeout, the exact memory (100MB) without the memory grant is not available and less than 100MB is available, in that case the grant can be decreased to the minimum required grant. What if even the minimum required grant is not available, in that case we will receive an error 8645. We can configure the timeout by using the resource governor.

Read on for more detail.

Comments closed

Tracking Resource Utilization by User

Published 2020-08-12 by Kevin Feasel

Brent Ozar uses most of Resource Governor:

You’ve got a bunch of users on the same SQL Server, and you wanna know who’s using the most resources. SQL Server doesn’t make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can’t intuitively tell which USERS are.
To solve this problem, we’re going to use Resource Governor.
Wait. Come back.

I’ve always liked the idea behind Resource Governor and since about SQL Server 2016, it has been quite a useful product because “make some queries slow down” can absolutely be the right answer when those queries are harming the performance of queries which matter more.

Comments closed

Workload Classification with Resource Governor in Azure Synapse Analytics

Published 2020-05-05 by Kevin Feasel

Niko Neugebauer keys in on an interesting addition to Azure Synapse Analytics:

Given that we can specify 5 different parameters (USER MEMBERNAME, ROLE MEMBERNAME, WLM_LABEL, WLM_CONTEXT, START_TIME/END_TIME) – there must be a prioritisation mechanism in order to decide which condition gets selected. This mechanism is called Parameter Weighting in Azure Synapse and it assigns the following weight to each of those parameters:
USER = 64
ROLE = 32
WLM_LABEL = 16
WLM_CONTEXT = 8
START_TIME/END_TIME = 4
meaning that if the Workload Classifier fits into the timeframe START_TIME/END_TIME, WLM_LABEL & ROLE – it will receive 52 points = 4 + 16 + 32,
while a different Workload Classifier that fits into WLM_CONTEXT & USER will get 72 points = 8 + 64, thus will prevail and will be selected over the first Workload Classifier.

Azure Synapse Analytics (including when it was known as SQL Data Warehouse) has had some resource governor-related things I’ve wanted in the box product for a while, including labels (which are better than using application name).

Comments closed

Workload Isolation in Azure Synapse Analytics

Published 2020-04-27 by Kevin Feasel

Niko Neugebauer explains how resource governance works with Azure Synapse Analytics SQL Pools:

Carrying on with the Azure Synapse series on the workload identification, classification and isolation started with
Query Identification in Azure SQL DW (Synapse Analytics), in this post I wanted to focus on the workload groups and the workload isolation (aka Resource Governance).
Before advancing and looking into Azure Synapse Analytics “Resource Governor” (my own naming, my fault – and yeah, I shall keep it naming properly), we need to look at the resource classes in Azure Synapse Analytics.
But even before that et me start with WTH – Where is the Heck of Resource Governance in Azure SQL Database ? (Don’t throw at me those Managed Instances, which is a SQL Server with Availability Group running in tuned VM in the background – I want & need the Azure SQL Database to have the proper Resource Governance.

Click through for an explanation plus demonstration.

Comments closed

Risks Of Using Resource Governor To Set Max DOP

Published 2018-10-11 by Kevin Feasel

Joe Obbish builds an example where Resource Governor’s CPU cap can actively harm query performance:

I uploaded the query plan here if you want to look at it. This type of scenario can happen even without Resource Governor. For example, a compiled parallel query may be downgraded all the way to MAXDOP 1 if it can’t get enough parallel threads.
The query performs significantly worse than before, which hopefully is not a surprise. A single execution took 12860 ms of CPU time and 13078 ms of elapsed time. Nearly all of the query’s time is spent on the hash join for the index intersection, with a tempdb spill and the processing of additional rows both playing a role. The tempdb spill occurs because SQL Server expected the build side of the hash join to be reduced to 1213170 rows. The bitmap filtering does not occur so 8 million rows were sent to the build side instead.

Read the whole thing.

Comments closed

Dealing With Noisy Neighbors

Published 2017-09-21 by Kevin Feasel

Kevin Kline explains what Resource Governor does:

There are lots of ways to manage noisy neighbors. For example, you could spin up additional instances of SQL Server on a single physical or virtual machine (VM), and then segregate the applications to a distinct instance. You could also follow the old adage of “one application, one SQL Server” by putting the SQL Server onto its own machine, either physical or virtual. But that can get very expensive very quickly, depending on your licensing methodology.
If you’re running SQL Server 2008 or later, you might want to investigate Resource Governor as an alternative. Resource Governor lets you create limits on the amount of system resources a database and application can consume. On versions 2008 to 2012, Resource Governor can explicitly limit CPU and memory and, starting with version 2014, limit I/O consumption as well. This is powerful medicine for multi-tenant instances with noisy neighbors!

My response to noisy neighbors is to turn my music up really loud as a passive-aggressive response. Oh, wait, wrong kind of noisy neighbor… H/T SentryOne

Comments closed