By Kendra Little on December 15, 2023
One feature I’ve not appreciated enough in the past in SQL Server is Resource Governor.
Resource Governor allows you to fix problems with Memory Grants in a simple way, as Erik Darling recommends. It also lets you classify sessions into groups and limit the maximum number of simultaneous requests, and /or limit the degree of parallelism if you need more CPU for other workloads. While this will slow down the queries you classify into that group, this can be super useful, especially if you’re already using something like Snapshot isolation to prevent blocking or you are using a read-only replica.
In Azure SQL Managed Instance, you get to use Resource Governor, even in the General Purpose tier. This is awesome.
Just make sure you execute commands in the context of the master database, or you’ll get error 40510: Statement 'ALTER RESOURCE GOVERNOR' is not supported in this version of SQL Server.
It is supported! Just USE master; before running your commands.
Directing traffic
At first, I thought this was a missing feature
When I first tested this, I believed the error message. I thought perhaps the documentation was misleading and that the feature was only available in Business Critical, or in some way limited.
Then I realized that I was executing the commands in the context of a user database. You can do this in the on-prem version of SQL Server, but Azure SQL Managed instance is architected a bit differently. When you start looking at your logs and system objects, you’ll notice things like database names actually being uniqueidentifiers, and lots of other little things you don’t see in an on-prem SQL Server.
There are also some handy unique features, like the sys.server_resource_stats view, which persists some performance data for ~14 days, even over restarts. (More on this in some future posts.)
But basically, some things like this work a little differently in Azure SQL Managed Instance, and I believe it’s related to the cloud-based architecture of the product.
I changed my database context to master, and it works perfectly
Just include a use master; statement at the top of your scripts and you won’t have an issue.
A note about this is now in the docs
I think one of the most common commands folks use for Resource Governor these days is ALTER WORKLOAD GROUP, like Erik suggests in his post. I submitted a PR to the docs to add a note to that page, and Randolph West has already merged it.
Hopefully this makes the ride smoother for others who need to do a little traffic direction for their workloads on Managed Instance.
REGISTER