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 most important consideration when defining a concatenated index is how to choose the column order so the index can be used as often as possible. To demonstrate, I will use the  StackOverflow2010  database.  Feel free to follow along with my examples. If you haven't, please checkout  Tools I Used During Query Performance Troubleshooting  to see how to setup to obtain execution plan and the number of logical reads. Let's consider two queries: Query 1: SELECT   ID FROM     dbo . Posts WHERE    OwnerUserId   =   26837         AND   PostTypeId   =   2   Query 2: SELECT   ID FROM     dbo . Posts WHERE    O...

  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. To demonstrate concepts about key lookups, I will use the  StackOverflow2010  database.  Feel free to follow along with my examples. If you haven't, please check out my post  Tools I Used During Query Performance Troubleshooting  to see how to setup environment to obtain execution plans and track logical reads. Now, let's consider the following index and query: CREATE   INDEX   IX_PostTypeID    ON   dbo . posts   ( PostTypeId ) ; SELECT   ID ,         Title ,         LastEditDate ,         Score FROM     dbo . Posts WHERE    posttypeid   =   3   The index...

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