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…
-
-
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…
-
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…
-
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,…
-
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…
-
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…
