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

• Moved by Kalman TothModerator Thursday, February 06, 2014 5:12 PM Not db design

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 utilize the clustered index as presently defined.  If you do have a number of queries which use the key the current 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 when you add the new clustered index.

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.

• Proposed as answer by Satheesh VariathMicrosoft community contributor Tuesday, February 04, 2014 4:19 AM

Few things

How many records are available in the tables? Are you seeing any performance issues? 

Just because its showing missing index, you do not need to create an index. That would not be a good idea. May be optimizer finds the scan on the clustered index would be much better option in your case. 

Including all this columns will definitely increase the size of the index... But if you really see performance degradation I would suggest  you to create a CI on A and B column but there is a big BUT.... If you want us to provide  you with more accurate suggestion please provide a table structure + show us typical queries running against the tab

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

Covering index is good in certain cases for business critical queries:

http://www.sqlusa.com/bestpractices/coveringindex/

Negatives: while it speeds up the target query, it can slow down the other queries and large size.

For a single query, the Database Engine Tuning Advisor usually says: cover it. Don't follow it blindly. You can't cover every query.

Optimization bible:

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

I believe that your query returns enough columns that SQL Server determines that it is more efficient to scan your table than to find the rows in your index and then to look up the value in the table.

I would consider Ed's solution of creating a clustered index on these two columns.  Before you do that, make sure that no queries are using the existing clustered index to do a seek.

>I would consider Ed's solution of creating a clustered index on these two columns

I don't see how it would help: "have tried creating a composite index ..of (A,b) but SQL Server does not use it  and is saying that"

Clustered indexes are helpful in business critical range searches.

Post data/index DDL and business critical queries for additional assistance.