Conor vs. UDFs in Joins

In my previous post, I reviewed the basic logic behind why the Optimizer chooses one join algorithm over another.  In this post, I’ll go one step further and discuss what happens when the basic logic for joins is combined with the logic for how UDFs work. (Apologies – I need to find the code plugin…

3

Conor vs. Join Algorithms

I got a question from a customer today related to join algorithms.  This is a good, general topic, so I will post a few notes on this in the hopes that it is interesting to anyone out there who doesn’t understand how the costing formulas are put together in SQL Server. (His actual topic is…

3

Conor vs. Anti-Semi-Join Reordering

I was asked to comment on a post about the order of WHERE NOT EXISTS (<subquery>) in a query and its impact on a query plan. Specifically this thread: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/278c272b-5ebb-4fda-8985-49927bbd3799   Broadly, SQL Server does attempt to make it so that the order in which you write a query does not matter in terms of…

4

Conor vs. Index Fragmentation in Plan Selection

(more SQL Bits questions) Does the SQL Server Query Optimizer consider index fragmentation in plan selection?    No, it does not directly care.  The optimizer does currently consider the number of pages consumed by the index.  So, a heavily fragmented index with lots of unused space might be considered less optimal than an otherwise equivalent…

0

Conor vs. Left Outer Join Reordering

A question from two of the MVPs: Are these two queries conceptually the same? select a.id, b.id, c.id from A left join C on A.id = C.id left join B on A.id = B.id;   select a.id, b.id, c.id from A left join B on A.id = B.id left join C on A.id = C.id;…

6

Conor vs. Index SARGability with complex scalar expressions

In my prior post, I introduced the concept of runtime constants.  Now let’s take this and apply the concept further to the topic of matching scalar predicates to indexes.   I will paraphrase a customer question I was mailed after SQL Bits last week: If I have a predicate like this: WHERE (SomeDateField >= DATEADD(DAY,-1,GETDATE()))…

0

Conor vs. Runtime Constant Functions

(more questions resulting from SQL Bits – some of these answers will be a bit more terse than my usual verbose self :))   A customer asked a different question and I need to introduce a concept to you before I can answer that question, so I will rephrase their question and then do another…

3

Conor vs. the Search Framework in SQL Server’s Optimizer

I got a few requests for additional topics where one can read about the SQL Server query optimizer’s search framework (which happens to be called Cascades).  I will warn you that most of these are academic papers.  Some of these use different terminology than the SQL Server Books Online and therefore may or may not…

1

Conor vs. Optional Parameters

One question I received at SQL Bits had to do with a reasonable programming practice that has an unintended performance impact.   SELECT <something> FROM T WHERE (@p is NULL or col = @p)   This pattern is common and we call it the “optional parameter problem”  (we name our problems :)).  In this case,…

2

Conor vs. Icelandic Volcano/SQLBits 6

Greetings all.  I just had a fascinating week.  I was invited to go give a talk at the SQLBits conference in London, England and I flew over on Wednesday/Thursday for a talk on Friday.  I was supposed to leave on Saturday but found myself stranded until the following Wednesday due to volcanic ash in the…

1