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. Throughout my SQL Server Index series, I have emphasized the importance of accurate statistics in helping SQL Server choose the best execution plan. I've mentioned that if a query has accurate indexes and correct cardinality estimates , the optimizer's choices are usually optimal. However, I haven't discussed the scenario when row estimates are inaccurate , leading the optimizer to make suboptimal choices. This post will cover some examples of that. 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 I setup to obtain execution plan and logical reads info.) Problem Consider the follow...

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