Tempdb Performance Improvements in SQL Server 2022 are Dramatic

  By ,

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… Continue Reading

Join Denny Cherry and Associates at SQL Bits 2023

  By

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… Continue Reading

RunAsRadio- Query Performance Tuning Strategies

  By

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… Continue Reading

Quick Tip: Remove CONVERT\CAST from your WHERE\JOIN clauses

  By , ,

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… Continue Reading

Add Azure Cache for Redis to Your Azure SQL Performance Tuning Toolbox

  By ,

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… Continue Reading

Add DBCC INPUTBUFFER to Your Performance Tuning Toolbox

  By

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… Continue Reading

Using Readable Secondary in Azure SQLDB

  By ,

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.     Continue Reading

Data Saturdays Logo Contest

  By

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… Continue Reading

How SQL Server Synonyms Help Database DevOps

  By

Synonyms inside SQL Server are one of those useful but forgotten features. A synonym is a database level object that allows you to provide an alternative name for another database object such as a view, user defined table, scalar function, stored procedure, inline table valued function (tvf), or extended stored procedure. They can also be used for CLR Assembly related stored procedures, CLR tvf, CLR scalar functions or even CLR aggregate functions.  There are many practical uses for synonyms, and I’ll explain how to create them and some use cases. Read the full article here at Red-Gate’s Simple Talk Continue Reading

Identifying SQL Server Performance Problems Part 3

  By

Continuing with our Performance Tuning series as you have learned baselining and knowing your normal system performance metrics is an important part of performance tuning. In part 1 we discussed collecting vital system metrics such as CPU Utilization and Memory usage. In part 2 we took a glimpse into how we can gather those metrics and other using the different tools that are available to use like Performance Monitor, Profiler, Extended Events and DMVs. In this part 3 we will look at how identifying the appropriate waits events and corresponding resolution will be critical for resolving performance issues. But first you… Continue Reading