Will the Query Optimizer Ignore A Fragmented Index? Scenario: I have a heavy OLTP table with an index.  I see many inserts, updates and deletes and the index fragments heavily within a day or less.  While on day one of the index build, the optimizer uses the index, day two or three, the optimizer skips it entirely. This is for exactly the same query.

Question in my head: why would some query plans skip the index, since the index is created to help optimize these plans?

Question for this post: can the optimizer skip a heavily fragmented index, such as a scenario where we have 1 billion records and an index is built, then two hours later, all billion records are removed and we have five hundred million new records?  

I'm beginning to think that adding an index to this table won't help at all, because of the nature of the table (data in quickly, data out quickly), but just want to understand why one day one, the optimizer will use the index in its plans, but day two, it won't.
AFAIK the optimizer is not aware of index fragmentation. This can be a problem if it picks a plan that scans a fragmented index.

The optimizer is aware of the allocated data size, though. If the index pages have a lot of free space (possibly due to internal fragmentation) this makes the index less likely to be used. 50% empty space means twice the amount of IO to scan. For random access that should not matter to any significant extent, though.

This is not a huge effect, though. It might explain what you are seeing.

If this small effect flips the query plan to not use the index then the index was never super great in the first place in the eyes of the query optimizer. This might be a hint that you can improve it.

Also, the optimizer seems to have a guess for how much of the index is cached in the buffer pool. There are some references to that in the XML execution plans. I have no detailed knowledge of that.


  I'm beginning to think that adding an index to this table won't help at all


I wouldn't go that far. Maybe all you need is a rebuild or a drop-DML-create sequence in the right places? Or, maybe this is just a query tuning problem (ask a new question with the actual execution plan included).