6

I am not sure as to properly use non-clustered indexes. The SSMS plan estimator said to add another index when the following index was on the table already.

    CREATE NONCLUSTERED INDEX [ix_zone_fetch_shipping] ON [dbo].[tbl_shipping_rates_zones]
    (
        [iso] ASC, [mzone] ASC, [postal] ASC
    )
    INCLUDE
    (
        [region], [zone_dom], [zone_emi], [zone_pmi], [zone_fci],
        [zone_ups], [zone_fed]
    )

My query was

SELECT * FROM tbl_shipping_rates_zones WHERE postal = '10001'

My question is... should I be creating an index for all possible look up keys? I search by mzone, iso, and postal in different queries.

Thanks

5
  • 1
    Are you including every column from the table? Sep 5, 2012 at 19:04
  • Yes I am including every column.
    – Alex Erwin
    Sep 5, 2012 at 19:20
  • 1
    Including all columns in a single index is not going to help much - such an index first of all basically doubles the space requirements, and secondly, it can only ever be used for the n left-most columns - it does NOT just magically make searching for the 17th column in your table quicker.
    – marc_s
    Sep 6, 2012 at 0:33
  • 1
    And having a separate index for each column - just because you might search for it some day - is also not very good. An index does cost you - in terms of peformance on every insert, update, delete. Don't overindex - that's almost worse than having no indexes at all....
    – marc_s
    Sep 6, 2012 at 0:34
  • Thank you for those comments @marc_s, that is exactly the info I was looking for. Also, is it appropriate to say there is a good range of non-clustered indexes to have (between 5-10 for example) or is it purely relative to the needs of the table? If so, what kind of things is it relative to?
    – Jeff.Clark
    May 10, 2016 at 23:18

2 Answers 2

6

SQL Server will show you an Index it would like to use, since it estimates that index would make life easier. There is certainly no need to create an index on every searchable field, in fact doing so will make write performance substantially worse. If you have less than say 5 to 10 indexes on the given table, and you run that query all the time, you probably want to add the suggested index exactly as SSMS shows it.

JNK's Edit Below

It's also important to look at the suggested indexes together. The suggestions are all in silos, so to speak, so one entire suggested index could be replaced by adding an INCULDEd field on another index.

For instance, it's possible to have suggestions for these two indexes:

CREATE INDEX ix_IndexA ON Table (ColA, ColB)

CREATE INDEX ix_IndexB ON Table (ColA) INCLUDE (ColC)

When in fact you could meet the needs of both with:

CREATE INDEX ix_IndexC ON Table (ColA, ColB) INCLUDE (ColC)

I've seen this with much more complicated indexes (10-20 key fields, 10-20 included fields) where adding one field to the INCLUDE list will eliminate the need for the second index.

2
2

The reason it is telling you to create a new index is because the one you have is not the most optimal one possible. The query optimizer is quite selective in the way that it chooses which index to use, the following link should help you understand selectivity:

<Link>

You said above that you have included all columns within the index - essentially duplicating the table data. On this basis, what would be the harm in creating 3 separate indexes on the columns you mention? The space used would be less and the speed would be much faster. I would hazard a guess that this is what the DTA/execution plan is telling you - just in a different way.

Look at the execution plan details that are generated by the query to identify where your bottle necks are and use that as a guideline as to how to design your indexes. SQL Server is pretty good at knowing what is best - trust in what it is telling you.

I hope this helps you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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