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.

  • if you're selecting those columns and you have a single column in the where filter then it's either a key lookup to get those columns or you put them into the include part of the index – Alen Jun 19 at 16:01
  • 1
    Can you give some examples of the queries you run, and how you call them? That'll have a big effect on index choice. – sp_BlitzErik Jun 19 at 16:06
  • There are a great deal of them which would be using these indices. The specific example I am working with follows a general structure of 1-2 CTEs and a select that pulls all of the information that I need. The table I am working to index currently is only a part of the final select and pulls a few columns there. I am confident that the Indexes I have created are adequate, but am more concerned with the choice in index that SQL makes. Both indices will work for the query without issue, one is just slightly more efficient. – Thomas D. Jun 19 at 16:09
  • Drop the indexes and create them in the opposite order than how you created them initially. Let's say it's a hunch that it'll be a coin flip in this case when both non-clustered indexes are covering (I don't think the heuristics count the number of columns in the include, they just validate that a lookup is not required). This is all conjecture, not a warranty on how the choices are actually made. – Aaron Bertrand Jun 19 at 16:16
  • 3
    Recently something similar was discussed here that initially seemed arbitrary - the key columns in missing index recommendations are based on the ordinal position in the table, not the ordinal position that would work best for the query. I don't think anyone intentionally wrote a query that organized the key columns ORDER BY column_id, I think they just said SELECT columns FROM (columns that should be in the key) WITH NO_ORDER_BY; and the output happens to reliably produce them in that order. "Predictably arbitrary" entered my vocabulary. – Aaron Bertrand Jun 19 at 17:02
up vote 5 down vote accepted

In my limited experimentation:

  • When the query has to perform a scan, SQL Server does the work to find the narrowest index every time.
  • When the query is expected to perform a seek, it doesn't care about index width, it just uses the last covering index created.

This kind of makes sense. If you're going to add rules to search for the perfect index in either of these cases, you should do it when it's going to save you the most. On a seek the effort spent in finding a better index when it won't really matter in terms of savings for all rows as opposed to per row is far less likely to be worth it. "Last in" is maybe intentional, with the assumption that the last covering index you created is probably the best one you've created, or it may just be arbitrary and coincidental (like the order of columns in a missing index recommendation).

Proof (well, sort of proof)

My test setup was pretty simple:

CREATE TABLE dbo.t1
(
  id int IDENTITY(1,1) PRIMARY KEY,
  sn1 sysname, tn1 sysname, cn1 sysname, typ1 sysname,
  sn2 sysname, tn2 sysname, cn2 sysname, typ2 sysname,
  sn3 sysname, tn3 sysname, cn3 sysname, typ3 sysname,
  sn4 sysname, tn4 sysname, cn4 sysname, typ4 sysname
);
GO
SET NOCOUNT ON;
GO
INSERT dbo.t1
(
  sn1,tn1,cn1,typ1,sn2,tn2,cn2,typ2,
  sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4
)
SELECT s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name,
       s.name, t.name, c.name, typ.name
FROM sys.schemas AS s
CROSS JOIN sys.objects AS t
INNER JOIN sys.all_columns AS c
ON t.[object_id] = c.[object_id]
INNER JOIN sys.types AS typ
ON c.user_type_id = typ.user_type_id;
GO

CREATE VIEW dbo.v1
AS
  SELECT id,sn1,tn1
  FROM dbo.t1;
GO

This put 13,260 rows into my table (your results will vary). Then I repeatedly created the same three indexes, in different order:

-- widest first
CREATE INDEX ix_wide ON dbo.t1(sn1) 
  INCLUDE(tn1,cn1,typ1,sn2,tn2,cn2,typ2,sn3,tn3,cn3,typ3,sn4,tn4,cn4,typ4);
GO
CREATE INDEX ix_mid ON dbo.t1(sn1) INCLUDE(tn1,cn1,sn2,tn2,cn2,typ2,sn3,tn3);
GO
CREATE INDEX ix_small ON dbo.t1(sn1) INCLUDE(tn1,cn1);
GO

-- widest last = ix_small then ix_mid then ix_wide
-- middle1 = ix_mid then ix_wide then ix_small
-- middle2 = ix_small then ix_wide then ix_mid

Then in each of those four cases I ran these two queries and investigated the plans:

DBCC FREEPROCCACHE;
GO
SELECT id,sn1,tn1 FROM dbo.v1; -- scan
GO
SELECT id,sn1,tn1 FROM dbo.v1 WHERE sn1 LIKE N'q%'; -- seek

Results:

         widest first  widest last  middle1   middle2
-------  ------------  -----------  --------  --------
   scan    ix_small      ix_small   ix_small  ix_small
   seek    ix_small      ix_wide    ix_small  ix_mid

The scan always chose the narrowest index. The seek always chose the index that was created last (since all cover). I didn't extend the tests to also include a non-covering index in the mix, because I don't suspect it will change this outcome.

Morals (well, sort of morals)

There are two morals here:

  1. Create your widest indexes first if you want your narrowest indexes to be used in seeks when they can.
  2. It's probably a narrow set of use cases where this optimization is worth it. Such as seeks that return a lot of rows and the wider covering index has very large columns that aren't in the narrower index. In these cases it might be worth explicitly specifying the index with a hint (and all the caveats that involves) rather than relying on the behavior I've observed here.

Probably some internals and heuristics I'm missing here that Paul will clarify. Also I tried to enable trace flag 302 to output index selection information, but this doesn't seem to work on modern versions of SQL Server.

  • This is a very interesting result, but it doesn't seem to be universally true; or else, there is something else afoot with respect to my view. The execution plan is using an index seek, however, the order of creation does not seem to be changing the index it chooses. I wonder if the index ID is the criteria that it uses instead of order of creation. As mentioned, it's not a huge performance gain, and we will likely not pursue it too much further; however, I am interested in finding a solution for my own knowledge. – Thomas D. Jun 20 at 18:45
  • Could be that it’s ID. I think if you create 4 indexes, then drop index id = 2, then create a new index, that new index gets id = 2. The plan would probably still pick index 4 (so “last” is really “highest ID”). Not at a PC to test but it seems clear that the decision is not very qualitative in the seek case. – Aaron Bertrand Jun 20 at 19:44

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.