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 —…
-
-
If there’s one thing every Scooby-Doo episode teaches us, it’s that solving mysteries is a team skill. The gang doesn’t stumble onto answers by luck (well… except when Scooby and Shaggy fall into secret passages). They solve the case because they know how to spot the clues and put them together. Performance tuning in SQL Server works the same way. If you don’t know where to look—or how to interpret the trail of breadcrumbs—you’ll spend more time running from shadows than unmasking the real villain. I see this practically every day. SQL Server Always Leaves Clues Just like Velma dropping and leaving her glasses behind, SQL Server leaves clues all…
-
No Scooby-Doo story is complete without footprints leading to a hidden passage. In SQL Server 2025, those footprints point us straight toward the next big feature: optimized locking. And like any good sleuth, we’re going to follow the trail step by step. But first, flip the levers that open the secret door: Turn on ADR before you hunt. Think of Accelerated Database Recovery (ADR) as the latch that unlocks the passage. You must enable ADR before you can enable optimized locking. (And if you ever need to turn ADR off, you’ll have to disable optimized locking first—no sneaking out the window!) RCSI = brighter flashlight. For the biggest win, enable…
-
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…
-
Quick Tip Remove CONVERT/CAST from your WHERE clauses and JOINS when comparing to variables of different data types. Set their data types to match your table definitions before using them as a filter. Optimizing your queries this way will greatly reduce the amount of CPU time, reads, and I/O generated in your queries and allow your code to take better advantage of indexes. Example We are going to create a very simple stored procedure called ConvertExample. In this procedure we will see two things. One, the first procedure we create will declare two variables as VARCHAR( MAX) data types, then in the WHERE clause it will convert a table column…
-
Performance Monitoring Tools In this second post we are diving into what tools you can use to capture the important performance metrics you need to gather for baselining your environment. Be sure to read part one first. Performance Monitor First let’s start with what comes native to Windows Server platform, whether you are on an Azure SQL Virtual Machine or your standard SQL Server instance install on premises you have a native tool, called Performance Monitor (affectionately known to most as PerfMon). This reliable tool allows you to easily monitor performance metrics through a Graphical User Interface (GUI), by selecting performance counters for the operating system as well as installed applications such as SQL Server. It…
-
I regularly deliver a presentation called “Mastering TempDB”. I wrote it based on what I wish I knew about TempDB “growing up” in my career as a SQL Server DBA. It really is a breakdown of what TempDB is, how it is used, what problems actually occur within it, and how to best configure it. With advice from my colleagues, I have decided to break that down into this article to educate you. What is TempDB? TempDB is a global resource used by everyone and everything inside SQL Server. Think of it as a dumping ground for anything that doesn’t fit in memory. It is always database_id number two in…
