Comparing Single Column, Multi-Column, and Filtered Statistics in SQL Server

Statistics in SQL Server are simple in theory: they help the optimizer estimate how many rows a query might return.

In practice? Things get weird fast. Especially when you start filtering on multiple columns, or wondering why the optimizer thinks millions of rows are coming back when you know it’s more like a few hundred thousand.

In this post, I’ll walk through examples using single-column, multi-column, and filtered statistics—and show where estimates go off the rails, when they get back on track, and why that doesn’t always mean you need to update everything with FULLSCAN.

Continue reading

All Eyes on the Wrong Problem: How Mitigations Distract from Real Performance Pain

The biggest lesson I’ve learned from helping folks manage data in Azure is this: if you’ve got a truly terrible problem you’d rather people didn’t notice, a great way to hide it is by educating your support staff and users about something bad but not AS terrible— something with a small mitigation—and constantly refocusing them on that.

The user base— and even your own support staff— will think that anyone who talks about the bigger issue just doesn’t understand how to fix the “known” problem.

This is the story of Azure General Purpose storage for Azure SQL Managed Instance and Azure SQL Database.

Continue reading

100 Things I Hate About Views: Undeclared Data Types in Columns

Views let you do dumb things by accident in SQL Server. Then they make you have to think way too hard to fix them.

Continue reading

What the Decline of SQL Server Quality Means for Developers and DBAs

‘Is it just me, or is SQL Server quality slipping?’

I asked myself that question for couple/few years until I faced up to it: SQL Server is well into a period where Microsoft investment is waning, and Microsoft regularly isn’t able to deliver the features they promise.

Continue reading

AI Will Eliminate DBA Jobs Faster Than You Think

I listened to ‘Surviving the A.I. Endgame’ this weekend and realized: I’ve become one of the believers that AI advances are very likely to completely change tech and knowledge roles as we know them over the next 10 years. This is going to dramatically shrink the workforce across MANY roles (and many of those impacted will be outside of the tech sector). It isn’t that people won’t be needed anymore, but far fewer people will be needed. Including people with database administrator (DBA) roles like mine.

Continue reading

How to Stop SSDT / Database Projects / SQLPackage from Modifying Database Options

SQL Server’s free state-based version control tooling was introduced under the ‘Data Dude’ brand, then became known as ‘SQL Server Data Tools’ (SSDT). Its extension for the (now dying) Azure Data Studio IDE is called ‘SQL Database Projects’. If you need to find documentation, you often need to know to search for specific component names like SQLPackage.exe, which is a command line utility used to deploy SSDT Projects AKA SQL Database Projects.

Continue reading

Buyer Beware: Azure SQL Managed Instance Storage Is Regularly as Slow as 60 Seconds

What are your stories of unbelievably bad performance from cloud vendors? I’ll go first. For years, Azure SQL Managed Instance’s General Purpose Tier has documented ‘approximate’ storage latency as being “5-10 ms.” This week they added a footnote: “This is an average range. Although the vast majority of IO request durations will fall under the top of the range, outliers which exceed the range are possible.”

Continue reading