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