THE SQL Server Blog Spot on the Web

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

Paul White: Page Free Space

A technical SQL Server blog from New Zealand.

Browse by Tags

All Tags » Internals » Iterators

  • Beware Sneaky Reads with Unique Indexes

    A few days ago, Sandra Mueller (twitter | blog) asked a question using twitter’s #sqlhelp hash tag: “Might SQL Server retrieve (out-of-row) LOB data from a table, even if the column isn’t referenced in the query?” Leaving aside trivial cases (like selecting a computed column that does reference the LOB data), one might be tempted to say that no, SQL Server does not read data you haven’t asked for. In general, that’s quite correct; however there are cases where SQL Server might sneakily retrieve a LOB column… Read More...
  • 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...
  • Iterators, Query Plans, and Why They Run Backwards

    SQL Server uses an extensible architecture for query optimisation and execution, using ‘iterators’ as basic building-blocks. Iterators are probably most familiar in their graphical showplan representation, where each icon represents a single iterator. They also show up in XML query plan output as RelOp nodes. Each iterator performs a single simple function, such as applying a filtering condition, or performing an aggregation. It can represent a logical operation, a physical operation, or (most often) both. For example, ‘Aggregate’ is a logical operation, and Stream Aggregate and Hash Aggregate are physical operations. Similarly, ‘Inner Join’ is a logical operation; Nested Loops a physical one. Both graphical plans and XML showplan output show these properties: Read More...
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement