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