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…
-
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.
-
Lots of exciting things are happening with Data Saturdays and we want you, the #SQLFamily, involved. Last week, Rob Sewell (B|T) announced the new automation and setup process for events. This week I am announcing a new logo and branding initiative. With the generous donation from Denny Cherry and Associates Consulting, DCAC, we have commissioned 99Designs artists to create a new logo for branding. The Data Saturdays Admins reviewed over 220 submissions these were considered the likeability, accessibility, inclusiveness, and ease of multi-use. We have narrowed down the choices to these four. Please take a moment to click the link below and rate your favorite, we will use your influence…
