Welcome back, my fellow sleuths, to my mystery-inspired blog series! I’m having a ton of fun writing these, and I hope you’re enjoying the ride through SQL Server’s haunted hallways as much as I am. For this one, the “ghost” isn’t a person in a mask — it’s AI. AI has burst onto the SQL Server scene like a new member of Mystery Inc., promising to solve every problem, write all your code, and tune every query. But here’s the truth: sometimes AI is the villain in disguise. When AI Leads the Gang Astray Picture this: the gang bursts into the abandoned carnival, and AI points dramatically at Fred —…
-
-
Like any good mystery, some SQL Server problems look spooky on the surface but have a very simple culprit hiding in the shadows. Every good Scooby-Doo episode starts with something spooky: flickering lights, creepy footsteps, and someone yelling, “There’s a ghost in here!” In the world of SQL Server, one of the most common “ghosts” I run into? Forgotten database compatibility modes. The Setup: A Creepy Little Detail Here’s the deal: when you create a new database, it gets the compatibility mode that matches the engine level at that time. Sounds fine, right? But let’s say you spun up an Azure SQL Database years ago—chances are it’s still running at compat…
-
(Continuing my Scooby theme 😊, I am on a roll) Zoinks! I feel like I’ve been chasing this ghost for years, and Microsoft finally pulled off the mask. With SQL Server 2025, we finally have what so many of us have been asking for forever: a free Standard Developer Edition. Up until now, if you wanted a dev copy of SQL Server, your only choice was Developer Edition—which is basically Enterprise Edition dressed up for testing. Sounds great, right? Except here’s the problem: most production environments don’t run Enterprise. They run Standard Edition. And that mismatch has caused more mysteries than the Creeper sneaking around the amusement park. (yes, I…
-
If there’s one thing I’ve learned in consulting, it’s that SQL Server, and other database performance tuning isn’t just about faster queries—it’s directly tied to your bottom line in the cloud. Databases, because of their large memory and IO footprint are some of the most expensive cloud resources. Every extra read, every bloated execution plan, every oversized tier you’re running? That’s money disappearing faster than Scooby Snacks at a midnight ghost chase. So, grab your Scooby-Doo hat, because it’s time to solve the mystery of runaway cloud costs. Clue 1: Start with Query Store – Your Map of Clues Every good mystery starts with clues, and in SQL Server (and…
-
Over the years, I’ll admit, SQL Server has come a long way in making life easier for database administrators and with each version it keeps getting better and better. The installation process bakes in more best practices than ever, default settings are smarter, and cloud offerings like Azure SQL and managed instances take a lot of the heavy lifting off our plates. Backups, high availability, patching—all of these are more streamlined than they used to be. It’s tempting to think this means DBAs don’t need to know the “nuts and bolts” or “how things work under the hood” anymore. But here’s the problem: I am seeing a real gap in…
-
One of the biggest impacts on resource consumption for Azure SQL DB are repeated data pulls by the application layer. No matter how fast those queries execute calling the same procedure or issuing the same SQL statements hundreds, thousands, or million times a day can wreak havoc on database performance. Death by a thousand cuts can easily bring a system to its knees. Sometimes it’s hard for DBAs to troubleshoot these actively as the execution of the statements happens so quickly they don’t even show in tools like sp_whoisactive. It’s not until you begin to dive into things like Query Performance Insights or Query Store that you start to see…
-
When using a Geo Replicated Azure SQL Database Readable Secondary there are a few things to consider when it comes to performance tuning. Check out this episode of Data Exposed: MVP Edition as we discuss what you need to keep in mind with Microsoft’s Anna Hoffman, @AnalyticAnna.
-
SQL Server 2017 (compatibility 140) brought us many Intelligent Query Processing (IQP), formally known as Adaptive Query Processing, features that improve performance on workloads straight out of the box with no code changes to implement. One of those features introduced was Adaptive Joins. In this feature the join operator is dynamically determined at runtime by the optimizer, which defines a threshold number of rows and then chooses between a Nested Loop or Hash Match join operator. This operator switch can produce a better join and improve performance of your queries without you having to lift a finger. Not all queries will qualify for this new feature. The feature only applies…
-
Sometimes as a DBA, I am lazy and want the ability to execute all of my tasks in one place. Lucky for me I discovered the other day that I can change my Azure SQL Database Service Level Object options within SQL Server Management Studio (SSMS) without ever having to go to the Azure Portal. By right clicking on your database properties and choosing the Configure SQL page you can change your Subscription and Azure Location. Even more and what is really cool is I can also scale up and down my Edition (which is generally referred to as Service Tier outside of SSMS), Size and Storage Tiers as well.…
-
Index Maintenance is an important aspect of database health. Above and beyond regular index rebuilds and reorganizations it is important to understand the usage of the indexes on your database. Cleaning up unused indexes can save a lot of overhead on Insert/Update/Delete operations. To achieve that goal, I typically run a script like the one shown below and check out whether or not an index has had any seeks or scans against it as a starting point in my cleanup regiment. SELECT d.name, OBJECT_NAME(i.[object_id]) AS [ObjectName], i.[name] AS [IndexName], s.user_seeks, s.user_scans FROM sys.indexes AS i LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s ON i.[object_id] =…
