My DBA Adventure

Firstly, I recommend watching this  How to Think Like the Engine - YouTube  by Brent Ozar on YouTube (all parts). It is incredibly helpful foundational material before you begin your SQL performance tuning journey. The SQL Server optimizer   is responsible for determining which operations —such as scans or seeks — should be used to query the least number of 8KB pages. However, the optimizer sometimes needs a DBA's intervention. For example, an ideal index for a query might not exist yet. In such cases, it is up to the DBA to create the index so that the optimizer can make use of it. Similarly, inaccurate row count estimations on the execution plan can lead to suboptimal choices by the optimizer. As DBAs, we may need to update statistics or tune the code depending on the cause of the misestimations. When we have accurate indexes and correct row estimations , the optimizer's choices are usually optimal, and we can stop our part of tuning the query. In this post, I will...

In this blog, I’ll list and briefly review some of the resources and tools that I genuinely find valuable — the ones I often return to, whether for learning or hands-on use. I’m especially thankful to be part of a DBA community filled with talented people who generously share their knowledge and experience. I’ll continue adding more great content to this list as I come across resources/tools that have helped me throughout my career journey. 🔧 Performance Tuning Blogs and Videos Brent Ozar Unlimited – How to Think Like the Engine Watch on YouTube A must-watch series that breaks down how SQL Server processes queries internally by showing how data is stored on disk as 8KB pages. It’s a foundational class for anyone getting into query performance tuning. I always enjoy watching Brent Ozar’s videos—he’s humorous, knowledgeable, and a great communicator. His lessons are packed with interesting examples. Always ⭐⭐⭐⭐⭐ from me! Erik ...

Today, I will show you why good cardinality estimate is essential for an efficient execution plan and how I troubleshoot inaccurate cardinality estimates. I will use the database StackOverflow2010 to demonstrate. INSERT 50,000 rows I insert 50,000 rows with OwnerUserID 26837 into the table. Assuming this is a real life workload! DECLARE   @date   AS   DATETIME   =   Getdate ( ) ; INSERT   INTO   posts              ( body ,               lastactivitydate ,               creationdate ,               score ,               viewcount ,               posttypeid , ...

1. Execution Plan - An execution plan in SQL Server is a detailed roadmap created by the SQL Server Query Optimizer to determine the most efficient way to execute a query. It shows which operators being used (e.g. scans, seeks, nested join, hash match, ...), data volume, access order, indexes being used, row estimates, and other valuable information that helps understand how the optimizer views and optimizes queries. - There are two types of execution plan: estimated execution plan and actual execution plan. An Actual Execution Plan includes both estimated and run time properties, thus I prefer using actual execution plans for troubleshooting. An actual execution plan allows me to identify if there is a discrepancy in the estimated number of rows and the actual value. - To view actual execution plans, we enable  Include Actual Execution Plan on the tool bar and execute queries. - However, if we encounter a situation when we can't run queries to obtain actual execution...

𝖳𝗁𝗂𝗌 𝗐𝖾𝖾𝗄, 𝖨 𝖾𝗇𝖼𝗈𝗎𝗇𝗍𝖾𝗋𝖾𝖽 𝖺 𝗊𝗎𝖾𝗋𝗒 𝗉𝖾𝗋𝖿𝗈𝗋𝗆𝖺𝗇𝖼𝖾 𝗂𝗌𝗌𝗎𝖾 𝖽𝗎𝖾 𝗍𝗈 𝗍𝗁𝖾 𝗎𝗌𝖾 𝗈𝖿 𝖺 𝗆𝗎𝗅𝗍𝗂-𝗌𝗍𝖺𝗍𝖾𝗆𝖾𝗇𝗍 𝗍𝖺𝖻𝗅𝖾𝖽 𝗏𝖺𝗅𝗎𝖾𝖽 𝖿𝗎𝗇𝖼𝗍𝗂𝗈𝗇 (𝖬𝖲𝖳𝖵𝖥). 𝖳𝗁𝖾 𝗊𝗎𝖾𝗋𝗒 𝗉𝖾𝗋𝖿𝗈𝗋𝗆𝗌 𝖼𝗋𝗈𝗌𝗌 𝖺𝗉𝗉𝗅𝗒 𝗍𝗈 𝖺 𝖬𝖲𝖳𝖵𝖥. 𝖠𝖿𝗍𝖾𝗋 𝗆𝗈𝖽𝗂𝖿𝗒𝗂𝗇𝗀 𝗍𝗁𝖾 𝖿𝗎𝗇𝖼𝗍𝗂𝗈𝗇 𝗍𝗈 𝖺𝗇 𝗂𝗇𝗅𝗂𝗇𝖾 𝗍𝖺𝖻𝗅𝖾-𝗏𝖺𝗅𝗎𝖾𝖽 𝖿𝗎𝗇𝖼𝗍𝗂𝗈𝗇 (𝖨𝖳𝖵𝖥), 𝗍𝗁𝖾 𝗉𝖾𝗋𝖿𝗈𝗋𝗆𝖺𝗇𝖼𝖾 𝗂𝗆𝗉𝗋𝗈𝗏𝖾𝖽 𝖻𝗒 𝟣𝟪𝗑.  𝖠𝗌 𝗍𝗁𝖾 𝗇𝖺𝗆𝖾 𝗌𝗎𝗀𝗀𝖾𝗌𝗍𝗌, 𝖬𝖲𝖳𝖵𝖥 𝖼𝗈𝗇𝗍𝖺𝗂𝗇𝗌 𝗆𝗎𝗅𝗍𝗂𝗉𝗅𝖾 𝗌𝗍𝖺𝗍𝖾𝗆𝖾𝗇𝗍𝗌 𝗐𝗂𝗍𝗁𝗂𝗇 𝗍𝗁𝖾 𝖿𝗎𝗇𝖼𝗍𝗂𝗈𝗇, 𝗐𝗁𝗂𝗅𝖾 𝖺𝗇 𝖨𝖳𝖵𝖥 𝗂𝗌 𝖺 𝗌𝗂𝗇𝗀𝗅𝖾 𝗌𝗍𝖺𝗍𝖾𝗆𝖾𝗇𝗍. 𝖢𝗈𝗇𝗌𝖾𝗊𝗎𝖾𝗇𝗍𝗅𝗒, 𝖲𝖰𝖫 𝖲𝖾𝗋𝗏𝖾𝗋 𝗁𝖺𝗇𝖽𝗅𝖾𝗌 𝗍𝗁𝖾𝗆 𝖽𝗂𝖿𝖿𝖾𝗋𝖾𝗇𝗍𝗅𝗒.   𝖲𝖰𝖫 𝖲𝖾𝗋𝗏𝖾𝗋 𝗍𝗋𝖾𝖺𝗍𝗌 𝖺𝗇 𝖨𝖳𝖵𝖥 𝗌𝗂𝗆𝗂𝗅𝖺𝗋𝗅𝗒 𝗍𝗈 𝖺 𝗏𝗂𝖾𝗐, 𝗂𝗇𝗅𝗂𝗇𝗂𝗇𝗀 𝗍𝗁𝖾 𝗅𝗈𝗀𝗂𝖼𝖺𝗅 𝗊𝗎𝖾𝗋𝗒 𝗈𝖿 𝗍𝗁𝖾 𝖿𝗎𝗇𝖼𝗍𝗂𝗈𝗇 𝗂𝗇𝗍𝗈 𝗍𝗁𝖾 𝗈𝗎𝗍𝖾𝗋 𝗊𝗎𝖾𝗋?...

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