There is much more to query tuning than reducing logical reads and adding covering nonclustered indexes.  Query tuning is not complete as soon as the query returns results quickly in the development or test environments.  In production, your query will compete for memory, CPU, locks, I/O and other resources on the server.  Today’s ...

If you look up Table Hints in Books Online, you’ll find the following statement: If a clustered index exists, INDEX(0) forces a clustered index scan and INDEX(1) forces a clustered index scan or seek. If no clustered index exists, INDEX(0) forces a table scan and INDEX(1) is interpreted as an error. The interesting thing there is ...

Summary: A detailed look at costing, and more undocumented optimizer fun. The SQL Server query optimizer generates a number of physical plan alternatives from a logical requirement expressed in T-SQL.  If full cost-based optimization is required, a cost is assigned to each iterator in each alternative plan, and the plan with the lowest ...

  I'll be presenting a session at SQLSaturday #44 in Huntington Beach, the first SQLSaturday on Southern California. The event takes place on Saturday, April 24 at the Golden West College on 15744 Goldenwest St, Huntington Beach, CA 92647.. For more information visit the following ...

Last year when I presented my session regarding the Query Optimizer at the PASS Summit and at a couple of local user groups, I was asked how the estimated CPU and I/O costs in an execution plan are calculated. Since Microsoft does not publish how these costs are calculated all I could say was that each operator cost depends on the operator ...

Did you ever wanted to know which execution plans the Query Optimizer would generate for your queries should your tables have millions of records? You can actually generate those plans by using the undocumented ROWCOUNT and PAGECOUNT options of the UPDATE STATISTICS statement. These options can be used on small or empty tables and can be ...

Did you know that the Database Engine Tuning Advisor (DTA) uses the Query Optimizer to help you to create indexes, indexed views, and partitions for your databases? The DTA uses the Query Optimizer to estimate the cost of queries so it can select the choices with the lowest estimated cost. But, how can the Query Optimizer estimate the cost of a ...

I am excited that this is going to be my second time speaking at the PASS Summit. I have been attending PASS every year since 2003: a few times in Seattle, but also in Orlando (just after hurricane Jeanne), Dallas (just after hurricane Rita), and Denver. My session, How the Query Optimizer Works, is scheduled for Wednesday, November 4, 1:30pm - ...

  I will be speaking at the Orange County SQL Server Professionals User Group this Thursday, October 1st, 2009. The topic is “How the Query Optimizer Works”. So if you are in the Orange County or Los Angeles area please stop by and say hello. The meeting starts at 6:30 PM. More details and directions can be found here Orange County SQL ...

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