none
Execution Plan suggests adding all non key columns in the Create INCLUDE

    Question

  • Hello,

    SQL Sertver 2008 R2 EP is suggesting to create a composite index with INCLUDE all columns.

    I have some  basic understanding on INCLUDE when creating an index.

    I have 70 some columns and  need to return all columns( on a report) and I have something like WHERE A = 'x' and B= 'Y'  and have tried creating a composite index ..of (A,b) but SQL Server does not use it  and is saying that 

    Missing Index ( Impact 73) create a non clustered index (A,B) and is listing to include all columns 

    I need to display all 70 some columns and may be that's why SQL Server is asking to include all columns by creating a composite index

    Any suggestions? 

    Thanks

    Monday, February 03, 2014 8:48 PM

Answers

  • There are several things you can try, the simplest would be to add a table hint to your query to force it to seek on the non-clustered index (A,b) you created.  That could possibly improve the performance of your query but it could also slow the performance of your query.

    The second option would be to create the index as SQL Server is suggesting which would effectively double the space that your table consumes.

    The third option is that you can drop the clustered index on your table and create a new clustered index on the composite (A, B) columns which would eliminate the need for a non-clustered index containing all the columns in an include clause.  The problem with this option is that it could adversely affect queries which currently utilize it.  If you do have a number of queries which use the key the clustered index is based on you could create a non-clustered index on that key so those queries would have an appropriate index to use.

    What I've found in my travels is that many times people create clustered indexes using keys which are seldom if ever used.  It's possible you can change the table's clustered and not skip a beat.

    • Marked as answer by SSISNewbie100 Wednesday, February 05, 2014 2:13 PM
    Monday, February 03, 2014 9:02 PM
  • If you select a substantial number of rows from a very large table, then a noncovering nonclustered index is not very efficient, because it would lead to many (relatively inefficient) row lookups. In those cases, the optimizer will typically use a table scan or clustered index scan.

    If there is no ORDER BY clause, then this tends to perform pretty well. If there is an ORDER BY clause on you "A"  and "B" columns, then the required sort on top of the table scan can be quite expensive. So in a case like this, it is very important if there is an ORDER BY clause, and if so, what is in it (including the order of the listed columns).

    If your database is dedicated for reporting, then simply adding this covering nonclustered index can be a good idea. If applicable, make sure that you include all ORDER BY columns as key columns in the index, and that you use the same order as in the ORDER BY clause. Also include the columns of the WHERE clause as keys. Include all other columns in the INCLUDES section.

    If your database is shared for OLTP purposes, then you shouldn't add such index. In that case, you should reconsider your clustered index, or consider to add a dedicated reporting database.


    Gert-Jan

    • Marked as answer by SSISNewbie100 Wednesday, February 05, 2014 2:13 PM
    Tuesday, February 04, 2014 6:11 PM

All replies

  • There are several things you can try, the simplest would be to add a table hint to your query to force it to seek on the non-clustered index (A,b) you created.  That could possibly improve the performance of your query but it could also slow the performance of your query.

    The second option would be to create the index as SQL Server is suggesting which would effectively double the space that your table consumes.

    The third option is that you can drop the clustered index on your table and create a new clustered index on the composite (A, B) columns which would eliminate the need for a non-clustered index containing all the columns in an include clause.  The problem with this option is that it could adversely affect queries which currently utilize it.  If you do have a number of queries which use the key the clustered index is based on you could create a non-clustered index on that key so those queries would have an appropriate index to use.

    What I've found in my travels is that many times people create clustered indexes using keys which are seldom if ever used.  It's possible you can change the table's clustered and not skip a beat.

    • Marked as answer by SSISNewbie100 Wednesday, February 05, 2014 2:13 PM
    Monday, February 03, 2014 9:02 PM
  • If you select a substantial number of rows from a very large table, then a noncovering nonclustered index is not very efficient, because it would lead to many (relatively inefficient) row lookups. In those cases, the optimizer will typically use a table scan or clustered index scan.

    If there is no ORDER BY clause, then this tends to perform pretty well. If there is an ORDER BY clause on you "A"  and "B" columns, then the required sort on top of the table scan can be quite expensive. So in a case like this, it is very important if there is an ORDER BY clause, and if so, what is in it (including the order of the listed columns).

    If your database is dedicated for reporting, then simply adding this covering nonclustered index can be a good idea. If applicable, make sure that you include all ORDER BY columns as key columns in the index, and that you use the same order as in the ORDER BY clause. Also include the columns of the WHERE clause as keys. Include all other columns in the INCLUDES section.

    If your database is shared for OLTP purposes, then you shouldn't add such index. In that case, you should reconsider your clustered index, or consider to add a dedicated reporting database.


    Gert-Jan

    • Marked as answer by SSISNewbie100 Wednesday, February 05, 2014 2:13 PM
    Tuesday, February 04, 2014 6:11 PM
  • Thanks Gert-Jan .  There is no dedicated db for report server.
    Wednesday, February 05, 2014 2:37 PM