THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

Content now hosted at
More of my SQL Server technical articles are at

September 2010 - Posts

  • A Tale of Two Index Hints

    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 that both hints can result in a scan. If that is the case, you might wonder if there is any effective difference between the two. This blog entry explores that question, and highlights an optimizer quirk that can result in a much less efficient query plan when using INDEX(0). I’ll also cover some stuff about ordering guarantees. Read More...
  • Inside the Optimizer: Plan Costing

    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 overall cost is ultimately selected for execution. Costing Details Graphical execution plans show the total estimated cost of an iterator, and the I/O and CPU cost components: In the simplest cases (like that shown above) the total estimated cost of an iterator is a simple sum of the I/O and CPU components. In other circumstances, the final cost may be modified by other considerations – perhaps as a result of a row goal. The I/O and CPU costs are calculated based on cardinality estimates, data sizes, statistical information, and the type of iterator. Iterators may also be costed differently depending on context. For example, a Merge Join iterator has a zero estimated I/O cost when performing a one-to-many join. When running in many-to-many mode, a worktable in tempdb is required, and an associated I/O cost appears: Read More...
Privacy Statement