Blogs

Should You Use SQL Server Readable Secondaries if Queries Can Fail Repeatedly at Any Time?

By Kendra Little on February 21, 2024

If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully “refreshed” on the secondary replica. Those queries may keep failing until you manually intervene.

It’s unclear if Microsoft will ever fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.

Ugly Bug: SQL Server Online Index Rebuild sometimes happens offline without warning

By Kendra Little on February 7, 2024

I found a nasty bug in SQL Server and Azure SQL Managed Instance recently: sometimes an “online” index rebuild of a disk-based rowstore clustered index (basically a normal, everyday table) isn’t actually “online”. In fact, it’s very OFFLINE, and it blocks both read and write queries against the table for long periods.

If you manage to make it through a rebuild successfully, the problem goes away for future rebuilds of that clustered index – likely leaving you bruised and bewildered.

Is the Azure SQL Managed Instance Business Critical Service Tier Worth the Cost?

By Kendra Little on January 29, 2024

The Business Critical service tier in Azure SQL Managed Instance is a lot more expensive than General Purpose. For the extra money, you get a different architecture.

Is it worth the extra cost? Spoiler: your mileage will vary, but probably not. Let’s talk about why.

Azure SQL Managed Instance General Purpose Architecture and Performance/Cost Tradeoffs

By Kendra Little on January 22, 2024

Whether or not you use Azure SQL Managed Instance, you will likely be asked for an opinion on it eventually if you’re a SQL Server person.

While the architecture is documented, it can be a bit of a long read and some of the gotchas are spread out over different pages – so I’m drawing up the architecture of each service tier along with notable implications for the design on performance and cost. Here’s the scoop on General Purpose.

Automatic Plan Correction Could be a Great Auto Tuning Feature for SQL Server- Here is What it Needs

By Kendra Little on January 17, 2024

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.

Power BI Performance Tuning with Eugene Meidinger

By Kendra Little on January 16, 2024

Eugene Meidinger stops by the Dear SQL DBA Podcast to chat about Power BI Performance Tuning.

We talk about the various engines and languages used in Power BI and big-picture strategies for getting performance from the start. Eugene then talks about the community of tools and techniques that can be used to dig in and solve performance problems in Power BI.

Microsoft is Breaking Database Compatibility Levels for SQL Server

By Kendra Little on December 18, 2023

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.