0

I have a query that performs joins on multiple tables. I have non-clustered index on the foreign keys of the tables and clustered index on primary keys. On analyzing the query plan, I figured that the query optimizer was choosing clustered index scan on all the tables or in some cases combination of non-clustered index scan and key look-up to fetch other non-key columns. To fix this I INCLUDED (covered) the non-key columns that were required in this query in the non-clustered indexes. As a result of this, I could see non-clustered index seeks / scans being performed as expected.

Now my question is, if I've other queries that require many other non-key columns to be a part of result set, then I might end up adding (INCLUDING) all columns to the non-clustered index to improve performance of all queries. Would this be a good idea?

Thanks.

Aaron Kurtzhals
  • 2,036
  • 3
  • 17
  • 21
Manish Mulani
  • 7,125
  • 9
  • 43
  • 45

2 Answers2

1

Its very much a case of understanding your usage. It would be really easy to add indexes for everything you could possibly query against but as with everything its a trade-off. Every index costs you time and storage - so it will likely slow down your inserts/updates, and the more you index the higher this cost.

If your usage highly favours reads over writes, then all is well and all you need to do is pay for some storage. If you need decent performance for writes too, then all you can really do is understand your application and index your most important stuff.

I highly recommend the "inside sql server" series of books (Kalen Delaney et al) - lots of reading to get through but I guarantee they'll help you understand the trade-offs you're making.

chrisb
  • 2,200
  • 1
  • 20
  • 23
0

It sounds like you included columns that were required by your WHERE clause, and you got index seeks as a result.

You can also include columns that are on your SELECT list, and this gets you a different benefit. If your index includes all of the fields that are needed by the query, including the SELECT list, then the query results can be returned straight out of the index, and it never has to go back to the table record at all.

Of course, UPDATE, INSERT, and DELETE operations then include the additional cost of index building.

You can run SQL Server Management Studio Tools > SQL Server Profiler to obtain a sample of current database activity. You can then feed this to Tools > Database Engine Tuning Advisor. If you have a lot of INSERT and UPDATE activity, the Tuning Advisor may suggest removing some indexes. If your activity is mainly SELECT statement, it is likely to suggest additional indexes.

I find the Database Engine Tuning Advisor often suggests covering indexes over many columns, such as you describe. I sometimes go along with its suggestions in this matter, but often keep the indexing limited to the key and condition columns, unless there is a specific performance problem with a specific query.

criticalfix
  • 2,870
  • 1
  • 19
  • 32