Some time ago I have been involved in index optimization exercise: applications change, queries evolve, sometimes there is a need to change existing indices – or adjust them a bit – to embrace the changes. Sounds like a routine stuff to me. Sounds like little to be worried about. Drop the old index. Build the new index instead. Neat and easy.
So I thought – before getting alerted by someone that when you re-create indices it is important to preserve index creation order to ensure it does not influence index selection by the optimizer.
My initial reaction was (yes) – are you nuts? How in the world index creation order (traced by indid) may possibly influence optimizer? If it indeed does index maintenance becomes a complete nightmare. Consider you have 10 indices on a table and you have to change structure for index with indid = 2. This will mean rebuilding all indices with indid > 2 just because index with indid = 2 is outdated! Large table? That sucks!
To my knowledge, the only influence indid has on optimizer is in selection of the indices that qualify for a query.
If you remember, when you trace optimizer’s index selection (old good trace flag 302) you can see that for each table optimizer runs through all the qualifying indices in indid order. So you’ll see something like this:
Beginning selection of qualifying indexes for table ‘X’,
Estimating selectivity of index ‘X.I1’, indid N
Estimating selectivity of index ‘X.I2’, indid N+M
All the rest of optimizer choices are based on selectivity, cluster ratio, &c. Indid bearing stops in the initial order of inspecting qualifying indices.
And yet – I can’t stop thinking that if someone has faced situation when index creation order has influenced the way optimizer approached a query there should be something in it. I’ve tried playing with SARG quality (giving the optimizer a chance to blunder over values it has to guess for indices with better selectivity that have been created last). I’ve tried playing with optimization timeout. Nothing seems to have helped: index selection has been based on selectivity &c. Sounded like a pretty strange bug to me.
Did any of you have faced this in your experience? Everyone has experienced optimizer making wrong choice (either because stats are bad, or because – it just happened to work strange + case opened). Otherwise why should there be a trace flag to force optimizer ignore explicit index forcing. But indid??? I’d be (unpleasantly) surprised to hear of it. At least for one customer it turned index maintenance into a pure nightmare. Are there more around?