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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".