Jeff Moden (6/30/2015)
GilaMonster (6/30/2015)
Jeff Moden (6/30/2015)
It seems like a lot of folks don't understand that a non-clustered index is a duplication of data that not only affects queries and space on disk but also affects backups, time to restore, space on tape, and time for nightly index and stats maintenanceAnd the buffer pool. The pages of the index and the table are different pages, so worse case you can have double the table's size of memory used up by one table.
Heh... totally forgot about that. That's one of the worst effects especially on smaller systems with less RAM. Thanks, Gail.
I've seen them actually more than double the table. I've seen NC indexes that total more than 5x the main table size! If someone was following DTA almost blindly, you could see how that could happen.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.