I've recently begun working on indexing the various views in our DB. Often, some of these tables are only used for joins, and rarely are they used in where or order by statements. What this means, is that we are creating multiple indexes (or one large relatively less efficient index) which are all keyed on the primary key. I should clarify that there are no where or order by containing anything from this table and there are no key-lookups in the views I am having issues with.
If SQL has multiple indexes that are all keyed on tbl.ID, how does it make the choice to use one index over the other? I find that it can vary between views even if the data pulled from the index will be the same. Generally, there is not a huge gain/loss in efficiency in choosing one index over the other; leading me to believe that it chooses a 'good enough' index and moves on (since there are many keyed off of the same column).
Further, should creating multiple indexes with the same key be avoided for this reason?
The specific problem i am currently dealing with has the view choosing an index like this:
CREATE NONCLUSTERED INDEX [index1] ON a.tbl ([ID]) INCLUDE (number,name,year, ...)
Where there are 14 columns in the include (it's unfortunate, but required based on our current query structure)
Over the more efficient index:
CREATE NONCLUSTERED INDEX [index2] ON a.tbl ([ID]) INCLUDE (number,name,year, ...)
Where there are only 5 columns in the include.
Both indexes have the information that the view needs, but one is obviously much smaller and more efficient to use. The larger index needs to exist to cover a massive view that calls all of the columns in it (roughly half of the table).
It is probably worth mentioning that I could choose to create one massive index as above, which covers almost every view slightly less efficiently than these smaller indexes do. From my testing, it seems that adding these additional indexes does not add a significant cost to updating the tables; and so I chose to create them for the small performance gains. In general, they are used as they are needed, but in certain cases, the optimizer chooses a less efficient option for no apparent reason.