Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Are you including every column from the table? –  Mr.Brownstone Sep 5 '12 at 19:04
    
Yes I am including every column. –  Alex Erwin Sep 5 '12 at 19:20
    
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 '12 at 0:33
    
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 '12 at 0:34

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
Would the ideal scenario be to no create any indexes and then run the statements and see what SSMS suggests? –  Alex Erwin Sep 5 '12 at 19:23
    
I would suggest creating no indexes other than your primary keys. Then run your "normal" set of queries against the data, and see what it wants to have. –  Max Vernon Sep 5 '12 at 19:26
    
You may want to look at the system DMVs related to indexes. See toadworld.com/platforms/sql-server/w/wiki/… for more detail! –  Max Vernon Sep 5 '12 at 19:28
    
Thanks for your help! –  Alex Erwin Sep 5 '12 at 19:29
1  
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. –  JNK Sep 5 '12 at 19:32

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:

http://blogs.msdn.com/b/bartd/archive/2011/01/25/query_5f00_tuning_5f00_key_5f00_terms.aspx

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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