Since I will be speaking about the Query Optimizer at the coming PASS Summit, I have been preparing my presentation and at the same time blogging about it. This time I will describe the Missing Indexes feature, seen from the point of view of the Query Optimizer.   We know that it is the job of the Query Optimizer to find an efficient ...

  This post shows how the Query Optimizer uses statistics to estimate the selectivity of expressions during query optimization.   You can also use this as a second part of my last post, The Query Optimizer and Parameter Sniffing. Here I will show you the advantage of the use of statistics when the Query Optimizer can “sniff” the ...

  As we all know, the SQL Server Query Optimizer uses statistics to estimate the cardinality and selectivity of predicates of a query to produce an optimal execution plan. The Query Optimizer accomplishes this by first inspecting the values of the query parameters. This behavior is called parameter sniffing and it is a good thing. Getting an ...

One of the most interesting tools that you can use to gain additional knowledge on how the Query Optimizer works is the sys.dm_exec_query_optimizer_info DMV. This view contains cumulative query optimizer statistics since the SQL Server instance was started and it can also be used to get optimization information for a specific query or ...

In my previous post I talked about contradiction detection as one of the early steps in query optimization. Another interesting step performed during query optimization is the automatic matching of computed columns. Although computed columns have been available in previous versions of SQL Server, the automatic matching feature was introduced until ...

One of the many interesting topics I found while working as technical editor of the SQL Server 2008 Internals book was the concept of contradiction detection. So, in this post I will to try to expand on this topic and provide some additional examples.     Contradiction detection is performed by SQL Server during the early steps of the ...