My DBA Adventure

In this post, I will demonstrate how a small change in Service Broker code can significantly increase CPU usage. This post may be helpful for those troubleshooting high CPU consumption related to Service Broker if their case is similar to mine.  CPU before and after the bug in Service Broker got fixed However, the main purpose of the post is to highlight that even when following Microsoft's documentation to setup some database feature, things can still go wrong. This is why benchmarking and testing are crucial steps before deploying new features to your production system. Additionally, it is interesting to see that a small change in the code can completely alter the service broker behavior. If you'd like, you can code along with me and see the results for yourself, provided you have SQL Server installed. Architecture The service broker setup I will use is as follows: There are two services and two corresponding queues and their activation procedures: the...

The advent of multi-core processors has led many computing fields, including SQL Server, to adopt multi-threaded processing to improve performance and efficiency. Paul White wrote a great post introducing parallelism in SQL Server, which you should check out: Understanding and Using Parallelism in SQL Server - Simple Talk (red-gate.com) SQL Server optimizers can choose to run operations (such as scanning a table) using multiple threads/workers, if it believes a parallel query plan can improve performance. That’s pretty neat, isn’t it? However, like anything, it can backfire under certain conditions. In this blog, I will discuss an issue I personally encountered with parallelism, how I troubleshot it from identifying the problem to resolving it. Troubleshooting using Wait Stats As a DBA, users often provide us with vague description of issues, such as “Oh, we just noticed the server was pretty slow this morning from 5AM to 8AM.” It's our job to ask more questions an...

In an earlier blog SQL Server Performance: Troubleshooting Memory Grant Overestimation using SQL Diagnostic Manager and SQL Server execution plan (mydbadventure.blogspot.com) , I introduced how SQL Server miscalculated the required memory for processing a complex query, leading to memory contention on the server and impacting other concurrent queries. In this blog, we will explore and evaluate different approaches to resolve the issue ✨. Are you excited to follow along with me? If so, let’s dive in!!! 🤗 Introduction When troubleshooting a SQL Server system issue, I found it effective to first identify the problem, and then weigh pros and cons, and exceptions of different solutions. For example, in this case, some solutions might not work, others work but take more time, but one solution stands out as the best solution. Don’t be easily fooled by any single approach 😉. Let’s focus on understanding the problem and researching different ways to solve it. As I did a lot of res...

Have you ever wondered how SQL Server uses memory? Have you ever encountered a memory issue in SQL Server? Did you know that SQL Server isn't smart sometimes and it can overestimate and grant a significant amount of memory just for a single query to run —up to one-fifth of the server’s total memory 😱? If you are curious to learn more, read on 🤓. Introduction In SQL Server, before executing a query, a compilation phase may be triggered to generate an execution plan. An execution plan determines 3 key aspects: the order in which source tables are accessed, the operations used to retrieve data (e.g. seek, index/table scan), and the methods for joining, filtering, sorting, or aggregating data ( Execution plan overview - SQL Server | Microsoft Learn ). SQL Server can bypass the compilation phase if it finds a reusable cached plan for the query. During the compilation phase, SQL Server estimates how much data each operation (e.g. read, join, sort, aggregate) will handle and...