Sigh, good catch.  It makes you wonder why the optimizer code didn't catch this.

Yes, you are correct.  Darn -- I thought I had proofread it better than that.  🙁

Cheers,

Mike

I would rephrase your first sentence to ..."why indexes should lead with equality columns and then range searches."  Inequality columns (from your query) will almost always cause a scan rather...

Great catch!  And that was with 4 other sets of eyes reviewing the article before I submitted it.

Hmmm, not sure how the code in the article got changed, but you are absolutely right.

I continue to be amazed by the overall reduction in wait times (from DPA free) for the production database.  There are still spikes and sometimes short plateaus, but overall during...

You are correct in asking about PAD_INDEX setting.  While I was still in the experimental and analysis part of this project I was trying to keep minimal variations on what...

• This reply was modified 4 years, 9 months ago by  Mike Byrd.

All of ya’lls comments are absolutely correct.  Not sure where my brain was when I wrote the original paragraph, but obviously not in this universe.  This is what I should...

Stand by for an undated script in GitHub.  You can find it with my UserName: MByrdTX

I also have listed there this 2 parter in Word format, a PowerPoint presentation I...

I'm not seeing your attachment. 🙁

Mike

Actually the script has evolved some since I wrote the article.  Have attached latest script (currently in production on one of client's databases).  (Change file extension from txt to sql)

• This reply was modified 5 years, 3 months ago by  Mike Byrd.
• This reply was modified 5 years, 3 months ago by  Mike Byrd.

Nice Jeff,

This follows some of the patterns I've found in my upcoming Dynamic Fill Factor article.  Amazing how it all ties together.

The rest of the article (minus Fig 16 & Fig 17) is below:

with now a clustered index seek on CIX_SalesOrderHeaderBig_OrderDateSalesOrderID having a query plan cost of 83.0901, 3595 logical reads,...

The article is missing the last 2 pages of Figures and text.  Not sure how SQLServerCentral lost that, but I will try and put rest of article back if they...