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. Assume we have an index on column C. A query has a  WHERE  clause that filters rows based on the values of func(C).  func () is a function. In this case, the optimizer can make use of the index, but it cannot seek on the index. 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 setup to obtain execution plan and logical reads data. Problem We have the following index on CreateionDate and a query that queries data created in 2008: CREATE   NONCLUSTERED   INDEX   [IX_CreationDate]    ON   [dbo] . [Posts]   (   [creationdate]   ASC ...

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