Automatic Plan Correction Could Be a Great Auto Tuning Feature for SQL Server: Here Is What It Needs

I’ve written a bit about SQL Server’s Automatic Plan Correction feature before– I have an hour long free course with demos on Automatic Plan Correction here on the site.

Today I’m updating that course with a note: after using Automatic Plan Correction in anger for a good amount of time, I do not recommend enabling the feature. I’ve had it cause too many performance problems, and there are not a ton of options for an administrator when it’s causing those problems.

Meanwhile, becoming reliant on the feature for the places where it does help makes it difficult to disable the feature. You end up stuck with a very weird set of problems that are oddly similar to the problems the feature was designed to solve.

Further investment in the feature could solve these problems and make this a great tool for customers. Here’s a run down of what Automatic Plan Correction needs from a user who has suffered from it.

Continue reading

Microsoft is Breaking Database Compatibility Levels for SQL Server

According to Microsoft’s documentation, ‘Database compatibility level … allow[s] the SQL Server Database Engine to be upgraded while keeping the same functional status for connecting applications by maintaining the same pre-upgrade database compatibility level."

But these days, the “functional status” of a database at a given compatibility level differs depending on whether you’re using SQL Server, Azure SQL Managed Instance, or Azure SQL Database – and in the hosted versions it may change anytime without notice. Surprise, your database behaves differently now!

The whole concept is breaking down, and this is bad news for users of both managed services and the boxed product.

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