Craig Freedman's SQL Server Blog

A discussion of query processing, query execution, and query plans in SQL Server.

Blog - About

About Craig Freedman's WebLog

About my blog

I am using this blog to write about how query processing works in SQL Server.

Some of the topics that I write about:

  • How to read and understand query plans and query operators.
  • How different query plans affect performance.
  • The tradeoffs made by the query optimizer when choosing query plans.
  • How subtle changes to a query can affect the choice of plan and impact performance.

About me

I've been developing commercial database engines and working on query processing for more than 10 years.  I've worked for the SQL Server team for more than 5 years and am a co-author of Inside Microsoft SQL Server 2005: TM 2005: Query Tuning and Optimization" href="http://www.microsoft.com/MSPress/books/8565.aspx" mce_href="http://www.microsoft.com/MSPress/books/8565.aspx">Query Tuning and Optimization.  You can learn more about me at this SQLServerCentral.com interview.

  • Craig Freedman's SQL Server Blog

    Parallel Scan

    • 3 Comments
    In this post, I’m going to take a look at how SQL Server parallelizes scans. The scan operator is one of the few operators that is parallel “aware.” Most operators neither need to know nor care whether they are executing in parallel; the scan is an exception...
  • Craig Freedman's SQL Server Blog

    The Parallelism Operator (aka Exchange)

    • 3 Comments
    As I noted in my Introduction to Parallel Query Execution post , the parallelism (or exchange) iterator actually implements parallelism in query execution. The optimizer places exchanges at the boundaries between threads; the exchange moves the rows between...
  • Craig Freedman's SQL Server Blog

    Index Union

    • 0 Comments
    I was planning to continue writing about parallelism this week (and I will continue another time in another post), but I received an interesting question and chose to write about it instead. Let’s begin by considering the following query: create...
  • Craig Freedman's SQL Server Blog

    Introduction to Parallel Query Execution

    • 5 Comments
    SQL Server has the ability to execute queries using multiple CPUs simultaneously. We refer to this capability as parallel query execution. Parallel query execution can be used to reduce the response time of (i.e., speed up) a large query. It can also...
  • Craig Freedman's SQL Server Blog

    Decorrelating Subqueries

    • 1 Comments
    In my previous post , we saw some examples where the optimizer is able to take a query with a correlated subquery and rewrite it as a join. For instance, we saw how this simple “in” subquery: create table T1 ( a int , b int ) create table T2 ( a...
  • Craig Freedman's SQL Server Blog

    Scalar Subqueries

    • 1 Comments
    A scalar subquery is a subquery that returns a single row. Some scalar subqueries are obvious. For instance: create table T1 ( a int , b int ) create table T2 ( a int , b int ) select * from T1 where T1 . a > ( select max ( T2 . a...
  • Craig Freedman's SQL Server Blog

    Hash Aggregate

    • 6 Comments
    In my prior two posts, I wrote about the stream aggregate operator. Stream aggregate is great for scalar aggregates and for aggregations where we have an index to provide a sort order on the group by column(s) or where we need to sort anyhow (e.g., due...
  • Craig Freedman's SQL Server Blog

    Stream Aggregate

    • 7 Comments
    There are two physical operators that SQL Server uses to compute general purpose aggregates where we have a GROUP BY clause. One of these operators is stream aggregate which as we saw last week is used for scalar aggregates . The other operator is hash...
  • Craig Freedman's SQL Server Blog

    Aggregation

    • 9 Comments
    Aggregation refers to the collapse of a larger set of rows into a smaller set of rows. Typical aggregate functions are COUNT, MIN, MAX, SUM, and AVG. SQL Server also supports other aggregates such as STDEV and VAR. I’m going to break this topic down...
  • Craig Freedman's SQL Server Blog

    Subqueries: ANDs and ORs

    • 5 Comments
    In my “Introduction to Joins” post , I gave an example of how we can use a semi-join to evaluate an EXISTS subquery. Just to recap, here is another example: create table T1 ( a int , b int ) create table T2 ( a int ) create table T3 ( a int ...
  • Craig Freedman's SQL Server Blog

    Subqueries in CASE Expressions

    • 5 Comments
    In this post, I’m going to take a look at how SQL Server handles subqueries in CASE expressions. I’ll also introduce some more exotic join functionality in the process. Scalar expressions For simple CASE expressions with no subqueries, we can just...
  • Craig Freedman's SQL Server Blog

    Summary of Join Properties

    • 7 Comments
    The following table summarizes the characteristics of the three physical join operators which I described in my three prior posts. Nested Loops Join Merge Join Hash Join Best for … Relatively small inputs...
  • Craig Freedman's SQL Server Blog

    Hash Join

    • 8 Comments
    When it comes to physical join operators, hash join does the heavy lifting. While nested loops join works well with relatively small data sets and merge join helps with moderately sized data sets, hash join excels at performing the largest joins. Hash...
  • Craig Freedman's SQL Server Blog

    Merge Join

    • 14 Comments
    In this post, I’ll describe the second physical join operator: merge join (MJ). Unlike the nested loops join which supports any join predicate, the merge join requires at least one equijoin predicate. Moreover, the inputs to the merge join must be sorted...
  • Craig Freedman's SQL Server Blog

    Nested Loops Join

    • 29 Comments
    SQL Server supports three physical join operators: nested loops join, merge join, and hash join. In this post, I’ll describe nested loops join (or NL join for short). The basic algorithm In its simplest form, a nested loops join compares each...
  • Craig Freedman's SQL Server Blog

    Introduction to Joins

    • 9 Comments
    Joins are one of the most important operations performed by a relational database system. An RDBMS uses joins to match rows from one table with rows from another table. For example, we can use joins to match sales with customers or books with authors...
  • Craig Freedman's SQL Server Blog

    Index Examples and Tradeoffs

    • 6 Comments
    The optimizer must choose an appropriate “access path” to read data from each table referenced in a query. The optimizer considers many factors when deciding which index to use, whether to do a scan or a seek, and whether to do a bookmark lookup. These...
  • Craig Freedman's SQL Server Blog

    Seek Predicates

    • 12 Comments
    Before SQL Server can perform an index seek, it must determine whether the keys of the index are suitable for evaluating a predicate in the query. Single column indexes Single column indexes are fairly straightforward. SQL Server can use single...
  • Craig Freedman's SQL Server Blog

    Bookmark Lookup

    • 15 Comments
    In my last post, I explained how SQL Server can use an index to efficiently locate rows that qualify for a predicate. When deciding whether to use an index, SQL Server considers several factors. These factors include checking whether the index covers...
  • Craig Freedman's SQL Server Blog

    Scans vs. Seeks

    • 6 Comments
    Scans and seeks are the iterators that SQL Server uses to read data from tables and indexes. These iterators are among the most fundamental ones that we support. They appear in nearly every query plan. What is the difference between a scan and a seek...
  • Craig Freedman's SQL Server Blog

    Properties of Iterators

    • 6 Comments
    In this post, I’ll give an overview of three interesting properties of iterators that can affect query performance: memory, non-blocking vs. blocking, and dynamic cursor support. Memory All iterators require some small fixed amount of memory to...
  • Craig Freedman's SQL Server Blog

    Viewing Query Plans

    • 2 Comments
    In my last post, I explained that SQL Server executes a query by compiling it into an iterator tree which we call a query plan. So, if we want to understand iterators and query execution better, we need a way to look at query plans. Fortunately, SQL Server...
  • Craig Freedman's SQL Server Blog

    The Building Blocks of Query Execution

    • 3 Comments
    What are iterators? SQL Server breaks queries down into a set of fundamental building blocks that we call operators or iterators. Each iterator implements a single basic operation such as scanning data from a table, updating data in a table, filtering...
  • Craig Freedman's SQL Server Blog

    Why am I starting this blog?

    • 5 Comments
    About two years ago I presented a talk to a couple of SQL Server user groups where I explained in general terms how SQL Server executes queries. I explained how to read showplan and how some of the most common operators (such as index scans and seeks...
Page 3 of 3 (74 items) 123