Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

I am trying to improve this (sub-) query being part of a larger query:

select SUM(isnull(IP.Q, 0)) as Q, 
        IP.OPID 
    from IP
        inner join I
        on I.ID = IP.IID
    where 
        IP.Deleted=0 and
        (I.Status > 0 AND I.Status <= 19) 
    group by IP.OPID

Sentry Plan Explorer pointed out some relatively expensive Key Lookups for table dbo.[I] performed by the query above.

Table dbo.I

```
CREATE TABLE [dbo].[I] (
[ID] UNIQUEIDENTIFIER NOT NULL,
[OID] UNIQUEIDENTIFIER NOT NULL,
[] UNIQUEIDENTIFIER NOT NULL,
[] UNIQUEIDENTIFIER NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NOT NULL,
[] CHAR (3) NOT NULL,
[] CHAR (3) DEFAULT ('EUR') NOT NULL,
[] DECIMAL (18, 8) DEFAULT ((1)) NOT NULL,
[] CHAR (10) NOT NULL,
[] DECIMAL (18, 8) DEFAULT ((1)) NOT NULL,
[] DATETIME DEFAULT (getdate()) NOT NULL,
[] VARCHAR (35) NULL,
[] NVARCHAR (100) NOT NULL,
[] NVARCHAR (100) NULL,
[] NTEXT NULL,
[] NTEXT NULL,
[] NTEXT NULL,
[] NTEXT NULL,
[Status] INT DEFAULT ((0)) NOT NULL,
[] DECIMAL (18, 2) NOT NULL,
[] DECIMAL (18, 2) NOT NULL,
[] DECIMAL (18, 2) NOT NULL,
[] DATETIME DEFAULT (getdate()) NULL,
[] DATETIME NULL,
[] NTEXT NULL,
[] NTEXT NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] DATETIME NULL,
[] VARCHAR (50) NULL,
[] DATETIME DEFAULT (getdate()) NOT NULL,
[] VARCHAR (50) NOT NULL,
[] DATETIME NULL,
[] VARCHAR (50) NULL,
[] ROWVERSION NOT NULL,
[] DATETIME NULL,
[] INT NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] NVARCHAR (50) NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] UNIQUEIDENTIFIER NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] TINYINT DEFAULT ((0)) NOT NULL,
[] UNIQUEIDENTIFIER NULL,
[] DECIMAL (18, 2) NULL,
[] DECIMAL (18, 2) NULL,
[] DECIMAL (18, 2) DEFAU

Is the WHERE-JOIN-ORDER-(SELECT) rule for index column order wrong?

At the least it is incomplete and potentially misleading advice (I didn't bother to read the whole article). If you're going to read stuff on the Internet (including this), you should adjust your amount of trust according to how well you already know and trust the author, but always then verify for yourself.

There are a number of "rules of thumb" for creating indexes, depending on the exact scenario, but none are really a good substitute for understanding the core issues for yourself. Read up on the implementation of indexes and execution plan operators in SQL Server, go through some exercises, and come to a good solid understanding of how indexes can be used to make execution plans more efficient. There is no effective shortcut to attaining this knowledge and experience.

In general, I can say that your indexes should most often have columns used for equality tests first, with any inequalities last, and/or provided by a filter on the index. This is not a complete statement, because indexes can also provide order, which may be more useful than seeking directly to one or more keys in some situations. For example, ordering can be used to avoid a sort, to reduce the cost of a physical join option like merge join, to enable a stream aggregate, find the first few qualifying rows quickly...and so on.

I'm being a little vague here, because selecting the ideal index(es) for a query depends on so many factors - this is a very broad topic.

Anyway, it is not unusual to find conflicting signals for the 'best' indexes in a query. For example, your join predicate would like rows ordered one way for a merge join, the group by would like rows sorted another way for a stream aggregate, and finding the qualifying rows using the where clause predicates would suggest other indexes.

The reason indexing is an art as well as science is that an ideal combination is not always logically possible. Choosing the best compromise indexes for the workload (not just a single query) requires analytic skills, experience, and system-specific knowledge. If it were easy, the automated tools would be perfect, and performance-tuning consultants would be much less in demand.

As far as missing index suggestions are concerned: these are opportunistic. The optimizer brings them to your attention when it tries to match predicates and required sort order to an index that does not exist. The suggestions are therefore based on particular matching attempts in the specific context of the particular sub-plan variation it was considering at the time.

In context, the suggestions always make sense, in terms of reducing the estimated cost of data access, according to the optimizer's model. It does not do a wider analysis of the query as a whole (much less the wider workload), so you should think of these suggestions as a gentle hint that a skilled person needs to look at the available indexes, with the suggestions as a starting point (and usually no more than that).

In your case, the (Status) INCLUDE (ID) suggestion probably came about when it was looking at the possibility of a hash or merge join (example later). In that narrow context, the suggestion makes sense. For the query as a whole, maybe not. The index (ID, Status) enables a nested loop join with ID as an outer reference: equality seek on ID and inequality on Status per iteration.

One possible selection of indexes is:

CREATE INDEX i1 ON dbo.I (ID, [Status]);
CREATE INDEX i1 ON dbo.IP (Deleted, OPID, IID) INCLUDE (Q);

...which produces a plan like:

I am not saying these indexes are optimal for you; they happen to work to produce a reasonable-looking plan to me, without being able to see statistics for the tables involved, or the full definitions and existing indexing. Also, I know nothing of the wider workload or real query.

Alternatively (just to show one of the myriad additional possibilities):

CREATE INDEX i1 ON dbo.I ([Status]) INCLUDE (ID);
CREATE INDEX i1 ON dbo.IP (Deleted, IID, OPID) INCLUDE (Q);

Gives:

Execution plans were generated using SQL Sentry Plan Explorer.

StackExchange Database Administrators Q#139474, answer score: 19