Every Scooby-Doo episode has one thing in common — there’s always a fake ghost. Sometimes, that “ghost” is really just a guy in a mask trying to scare everyone with smoke machines and spooky sounds. In the SQL Server world, this ghost appears as the canned-query consultant. You’ve seen them. They roll in with a big name and an even bigger slide deck. They click “Run All,” hand you a 200-page report filled with screenshots and DMV outputs, and vanish into the night. They didn’t solve your mystery; they just dumped a list of clues in your lap. Or worse, you hired a big-name consulting firm, which brought on a…
-
-
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 —…
-
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…
-
One thing I’ve always loved about the Scooby-Doo cartoon is that he never solved a mystery alone. Scooby and Shaggy could sniff out a clue between snacks, but it always took the rest of the Mystery Inc. gang to crack the mystery. Velma helped with her smarts, Fred with the traps, and Daphne with spotting the overlooked details—all of them used their skills to actually catch the villain. And sometimes, they even brought in special guests, such as Batman, the Harlem Globetrotters, or Don Knotts, to help tackle the really tricky cases. Performance tuning in SQL Server works the same way. The Tunnel Vision Trap When you’re buried in a…
-
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…
-
SQL Server performance issues often stem from easy fix bottle necks that can be fixed with the right tuning strategies. This short blog will focus on the DELETE statement. The problem with DELETE statements is that it requires excessive logical reads and consumes transaction log space, even in simple recovery mode. DELETE is a row-based operation and generates large number of logical reads whereas TRUNCATE removes all of the rows of a table or partition at the storage, for a much faster and more efficient operation. Both DELETE and TRUNCATE remove data from a table, but they behave differently in terms of performance, recovery, logging, and rollback capabilities. The issue…
-
Had a great time talking with Richard on RunAsRadio about Query Performance Tuning Strategies, check it out. How do you keep your SQL queries fast? Richard chats with Monica Rathbun about her approaches to SQL Server query tuning. Monica starts with defining the problem – how do we know that the database is the performance bottleneck? The conversation dives into measuring query performance and the power of Query Store, but only on SQL Server 2016 and above, so get upgrading! Entity Framework is a standard tool for developers to automate access to SQL. Still, it can generate some pretty ugly queries, and Monica talks about different ways to improve them,…
-
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…
-
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…
-
A command I like to use when performance tuning is DBCC INPUTBUFFER. If you have ever run sp_whoisactive or sp_who2 to find out what sessions are executing when CPU is high for instance this can be a real quick life saver. At times, for me, those two options do not return enough information for what I’m looking for which is the associated stored procedure or object. Using this little helper along with the session id can easily get you that information. Let’s take a look. First, I will create a simple procedure to generate a workload. CREATE OR ALTER PROCEDURE KeepRunning AS DECLARE @i INT=1 WHILE (@i <1000) BEGIN select…
