Optimizing my SQL queries - picking the right indexes

Question

I have a basic table as follows.

create table Orders
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Company VARCHAR(3),
    ItemID INT,
    BoxID INT,
    OrderNum VARCHAR(5),
    Status VARCHAR(5),
    --about 10 more columns, varchars and ints and dates
)

I'm trying to optimize all my SQL since I am getting a fair few deadlocks and some slowness - but I'm no expert on this sort of thing!

I created a few indexes:
Clustered on the ID (Primary Key).
Non-Clustered index on ([ItemID])
Non-Clustered index on ([BoxID])
Non-Clustered index on ([Company],[OrderNum],[Status])
Maybe 1 or 2 more on some other columns

But I'm not 100% happy with the results.

SELECT * FROM Orders WHERE ItemID=100

Gives me an index seek + a key lookup and a Nested loop (Inner join). I can see why - but don't know if I should do anything about it. They key lookup is 97% of the batch which seems bad!

Every query used will pull back every column in the table, but I don't like the idea of including every column in the index.

I'm making a change now to query everything on the [Company] field. Every query will be using it, because results should never contain more than 1 value. So they will all change:

SELECT * FROM Orders WHERE ItemID=100    --Old
SELECT * FROM Orders WHERE Company='a' and ItemID=100    --New

But the execution plan of that gives me exactly the same as not including company (which does surprise me!).

• Why are the two execution plans above the same? (I have no index on [company] at the moment)

• Is it worth adding [Company] to all my indexes since it seems to make 0 different to the execution plan?

• Should I instead just add 1 single index to [Company] and keep the original indexes? - but will that mean every query will have 2 seeks?

• Is it worth 'including' all other columns in my indexes to avoid the key lookup? (making the index a tonne bigger, but potentially speeding it up?) i.e.

  CREATE NONCLUSTERED INDEX [IX_Orders_MyIndex] ON [Orders]
  ( [Company] ASC, [OrderNum] ASC, [Status] ASC )
  INCLUDE ([ID],[ItemID],[BoxID],
  [Column5],[Column6],[Column7],[Column8],[Column9],[Column10],etc)
  

That seems messy if I did it on 4 or 5 indexes.

Basically I have 4-5 queries which run quite often (some selects and updates) so I want to make it as efficient as possible. All queries will use the [company] field, and at least 1 other. How should I go about it.

Any help appreciated :)

Answer 1

In your execution plan, you say that lookup takes 97% of the batch.

In this case it doesn't mean anything because an index seek is very fast and you didn't have that much operation to be done.

That lookup is actually the record you read based on the index you have specified.

Why are the two execution plans above the same? (I have no index on [company] at the moment)

Non-Clustered index on ([Company],[OrderNum],[Status])

This index will be considered only if Company, OrderNum and Status appear in your where clause.

Concatenated indexes generates a key that would look like this 0000000000000 when you pass only company it creates an incomplete key that requires using wildcard for the other to values.

It would look a little like this : key like 'XXX%' this logic will require an index scan which is time consuming.

The optimizer will determine that it's preferable to first seek and rows from the ItemID index and then scan these to match any with the required company.

Is it worth adding [Company] to all my indexes since it seems to make 0 different to the execution plan?

You should consider having a Company index instead of adding it to all your indexes. Composite index could speed things up by reducing the number of nested loops, but you have to think then thoroughly.

The order of the fields you add to such an index is very important, they should be ordered by uniqueness to allow a better seek. Also, you should never add a field that might not be used in a query.

Should I instead just add 1 single index to [Company] and keep the original indexes? - but will that mean every query will have 2 seeks?

Having more than one index seek is not all that bad, they are usually paralleled and only the result of both are matched together.

Is it worth 'including' all other columns in my indexes to avoid the key lookup? (making the index a tonne bigger, but potentially speeding it up?)

It is worth when it's only a few fields that could be optional in the where clause or when you have queries that select only those fields when you are using the specified index.

Last notes

All indexes are not equal, comparing string (varchar) is not the same as comparing numbers (integer, datetime, bytes, etc).

Also, keeping them clean helps a lot, if your indexes are fragmented, they will be next to useless in terms of performance gain.