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.
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.