Long running query - out of ideas

Question

Long running query - out of ideas

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

TangoVictor SSChampion Points: 10975 More actions · Answer 1

paul,

Thanks for the explanation. That is very helpful. If you wouldn't mind to answer one last question. Would you ever make a NC index (in my case) with 35 included columns (essentially the whole table)?

Personally that wouldn't make sense from the aspect of storage since you would be duplicating the table in an index, that leads to the question how many columns to you add to a NC index?

Paul White SSC Guru Points: 150468 More actions · Answer 2

tvanharp (9/4/2009)
Thanks for the explanation. That is very helpful.

Thank you.

tvanharp (9/4/2009)
Would you ever make a NC index (in my case) with 35 included columns (essentially the whole table)?

I might. It would depend on the circumstances. By creating an NC index with a few key columns and all of the remaining columns INCLUDEd in the index, we would essentially be creating a second clustered index - but with key columns sorted in a different order.

If you have plenty of RAM, storage space isn't an issue, the data rarely changes (perhaps the database or file group is read-only for reporting purposes), and you have performance-critical queries which need to access full row data in specific sorted orders (possibly to optimize a merge join, a stream aggregate GROUP BY, or a ranking function) then this can be a valuable optimization.

In a more normal scenario, it is fairly rare to INCLUDE more than a handful of narrow columns in a non-clustered index. It all depends on your requirements though. Whatever you decide, be sure you can demonstrate measurable benefits before deployment 🙂

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi