none
Multiple indexes on a table with various combinations

    Question

  • Hi Experts,

    This post is similar to my previous one on indexing. am just trying to understand proper indexing strategy.

    Assuming that i have to use following queries frequently.

    1. select Col1, Col2, Col3 from tab where col4='some value'
    2. select Col1, Col2, Col3 from tab where col5='some value'
    3. select Col1, Col2, Col3 from tab where col6='some value'
    4. select Col1, Col2, Col3 from tab where col7='some value'
    5. select Col1, Col2, Col3 from tab where col8='some value'
    6. select Col1, Col2, Col3 from tab where col9='some value'
    7. select Col1, Col2, Col3 from tab where col10='some value'

    here, my doubt is, do we need to create "1 clustered + 6 NC covering" indexes (total 7) as we have 7 different columns in the where clause in 7 different select statements? Or, is there any better way of doing this?

    Thanks,

    Shiva

    Saturday, March 15, 2014 12:51 PM

Answers

  • >am just trying to understand proper indexing strategy.

    There is no such a thing. It is an art.

    Generally you can use covering index for a business critical query. But that may slow down other queries because the covering index usually wide! You need to check the execution plans for each query involved.

    It is better to apply narrow fast indexes that way other queries will not slow.

    Remember STATISTICS! It should be updated nightly:

    http://www.sqlusa.com/articles/query-optimization/

    The database engine uses the STATISTICS for the execution plan preparation not the index.

    For best performance the indexes should be narrow (fast) and STATISTICS up-to-date.

    Missing/fragmented/wide indexes & out-of-date STATISTICS are detrimental for performance.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012






    Saturday, March 15, 2014 1:10 PM
  • Yes, it may help your query execution.

    But, there is no hard rule for index creation.Just because of the column is using in where clause, you do not need to create index on the column. May be after creating an index you would not see the index is being used for query execution. That depends on lots of factors like your index size/no.if query execution/selectivity of the column etc. Apart there are some guidelines for indexes. Please refer the below link:

    index creation guidelines


    • Marked as answer by ShivaGS Saturday, March 15, 2014 1:26 PM
    Saturday, March 15, 2014 1:11 PM

All replies

  • >am just trying to understand proper indexing strategy.

    There is no such a thing. It is an art.

    Generally you can use covering index for a business critical query. But that may slow down other queries because the covering index usually wide! You need to check the execution plans for each query involved.

    It is better to apply narrow fast indexes that way other queries will not slow.

    Remember STATISTICS! It should be updated nightly:

    http://www.sqlusa.com/articles/query-optimization/

    The database engine uses the STATISTICS for the execution plan preparation not the index.

    For best performance the indexes should be narrow (fast) and STATISTICS up-to-date.

    Missing/fragmented/wide indexes & out-of-date STATISTICS are detrimental for performance.


    Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012






    Saturday, March 15, 2014 1:10 PM
  • Yes, it may help your query execution.

    But, there is no hard rule for index creation.Just because of the column is using in where clause, you do not need to create index on the column. May be after creating an index you would not see the index is being used for query execution. That depends on lots of factors like your index size/no.if query execution/selectivity of the column etc. Apart there are some guidelines for indexes. Please refer the below link:

    index creation guidelines


    • Marked as answer by ShivaGS Saturday, March 15, 2014 1:26 PM
    Saturday, March 15, 2014 1:11 PM
  • here, my doubt is, do we need to create "1 clustered + 6 NC covering" indexes (total 7) as we have 7 different columns in the where clause in 7 different select statements? Or, is there any better way of doing this?

    You will need at least 7 indexes to avoid a table/index scan for each of the queries.  The non-clustered indexes do not necessarily need to be covering.  As Kalman and ShivaGS mentioned, the optimal solution depends much on the particulars of your data and workload.  Maybe a scan is ok if the table is small and a query run infrequently.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Saturday, March 15, 2014 2:45 PM