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…
-
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…
-
Tempdb is always a topic for me whether it’s in my sessions or blogs I have written. However, I’ve never been so excited about it then I am when it comes to the dramatic performance changes introduced in SQL Server 2022. THEY HAVE SOLVED ONE OF OUR BIGGEST PERFORMANCE BOTTLE NECKS, System page latch concurrency. In SQL Server 2019 they addressed what’s known as metadata contention, when pages that belong to systems object take page latches while updating tables that track table metadata by introducing memory optimized tempdb. Additionally, the product team made improvements to object allocation contention. This is the contention for metadata pages used to manage space allocation…
-
I am very excited and lucky to be speaking once again at SQLBits along with my DCAC colleagues. SQLBits, the largest Data Platform conference in Europe, held this year in Newport Wales March14th – 18th. It is a conference for leading data professionals with over 300 sessions from speakers all over the world. It is a huge honor to have been selected to share my performance tuning knowledge with attendees. My session, Performance Tuning Azure SQL Database Have you moved to a cloud database like Azure SQL Database and are having performance issues? While the Azure SQL services running in Azure are similar to SQL Server, they are key differences…
-
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…
