Why does the query optimizer choose this way?

Question

Why does the query optimizer choose this way?

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: 101085 More actions · Answer 1

If the overall number of pages to be read is small either way, it's just not worth the optimizer's time to keep going.

SQL naturally favors the clustered index. Overall that's a good thing. And yet another reason to very carefully select the clus index keys, rather than defaulting them to identity.

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

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

ScottPletcher (9/2/2016)
If the overall number of pages to be read is small either way, it's just not worth the optimizer's time to keep going.
SQL naturally favors the clustered index. Overall that's a good thing. And yet another reason to very carefully select the clus index keys, rather than defaulting them to identity.

Many of our tables are very wide, Scott. The difference between reading the clustered index and a narrow ordinary index tailored to a family of similar queries is 10 to 20 fold.

^{“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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

SQL naturally favors the clustered index.

As a general rule it favors the most efficient index.

My environment is similar to Chris' database. We have some very wide tables, and yes they are normalized thank you. Designing narrow, appropriately ordered covering indexes to support our most used queries makes an enormous difference in performance.

The answer is going to be "It depends", but this thread has it's answer and there's no sense hijacking it to have a debate, so just for fun, I started this new thread.

Should a primary key be a natural key or an identity?

I've been in one camp for years, but am always willing to hear opposing arguments.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills