How to make actual execution plan better?

Question

How to make actual execution plan better?

Viewing 3 posts - 16 through 17 (of 17 total)

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

ScottPletcher SSC Guru Points: 101101 More actions · Answer 1

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 maintenance
And 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".

MadAdmin SSChampion Points: 11261 More actions · Answer 2

ScottPletcher (6/30/2015)
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.

Yes. Had a table with total size was 15GB, Clustered index was 2GB. I spent a great day drop kicking DTA_indexes.

Take into account we had been backing up an extra 12.5GB daily, moving this around into dev, staging, qa, DR environments, the fact that updating many many many indexes increase lock footprint, and that we store months of backups and 1 for each quarter at intervals before that on a NAS, and we can see how the classic argument of 'space is cheap' becomes a display of abject stupidity.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Tac11 (6/30/2015)
...
I didn't understand why execution plan suggesting me to create non-clustered index on this while the table already has a clustered index!!!

Then you need to read this article[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden