Getting Around Error 40510 to Configure Resource Governor in Azure SQL Managed Instance

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.

Continue reading

How Many Features Are Missing from Azure SQL Managed Instance?

Spoiler: a large amount of features from SQL Server 2022 are missing from Azure SQL Managed Instance. Some major features are missing that were introduced in SQL Server 2019– and here we are just a few weeks away from 2024.

But Microsoft’s top-line marketing claims about Azure SQL Managed Instance remain that ‘it’s always up to date with the latest SQL features and functionality.’

Let’s dig into some of the documented highlights on missing features, so you can decide for yourself what to think of that statement.

Continue reading

Jer and Kendar Explore Optimized Locking

SQL Server has a new feature that’s currently only available in Azure SQL Database: Optimized Locking.

Jeremiah Peschka joins Kendra (aka Kendar) to talk through the docs and nerd out on locks, blocks, and how to pronounce the acronym “LAQ”.

Prefer to explore optimized locking with a diagram? I’ve also got a little sketchnote for ya.

Continue reading

Should We Do Index Maintenance on an Azure SQL Managed Instance or Azure SQL Database?

Have you ever received advice that was technically correct, but it was too hard to understand?

I think of this as “accidental bad advice,” because it can lead to confusion and bad outcomes. There’s a LOT of accidental bad advice out there on index maintenance for SQL Server and cloud versions like Azure SQL, even in the official documentation.

In this post I’m answering a common index maintenance question, and we’re going to keep it simple.

Continue reading

3 Lessons Learned About Azure SQL Database

Hosted cloud databases make a lot of administrative tasks easier, or take care of them for you altogether.

But here are three things that I’ve found a little too easy to forget about Azure SQL Database.

Continue reading

Workers vs. Requests in Azure SQL Database

We now explicitly define ‘requests’ and ‘workers’ in the Azure SQL Database documentation, and we’ve cleaned up multiple places where we used to equate the two terms. In this post, I share the history of the two terms when it comes to Azure SQL Database, why the two were ever equated, and why things like this are tricky to change.

Continue reading

What I Learned Writing About How to Diagnose and Troubleshoot High CPU in Azure SQL Database

Writing helps me learn. In my job as a Content Developer, this is more true than ever: there’s a fantastic group of folks, both in the Database Docs team and in the Microsoft Data Platform engineering team, who review and contribute to content.

I’ve just had the pleasure of publishing my first new article in the Microsoft Docs, Diagnose and troubleshoot high CPU on Azure SQL Database.

Continue reading