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

    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

    Repeatable Read Isolation Level

    • 24 Comments
    In my last two posts, I showed how queries running at read committed isolation level may generate unexpected results in the presence of concurrent updates. Many but not all of these results can be avoided by running at repeatable read isolation level...
  • Craig Freedman's SQL Server Blog

    Serializable vs. Snapshot Isolation Level

    • 18 Comments
    Both the serializable and snapshot isolation levels provide a read consistent view of the database to all transactions. In either of these isolation levels, a transaction can only read data that has been committed. Moreover, a transaction can read the...
  • 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

    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

    Read Committed Isolation Level

    • 14 Comments
    SQL Server 2000 supports four different isolation levels: read uncommitted (or nolock), read committed, repeatable read, and serializable. SQL Server 2005 adds two new isolation levels: read committed snapshot and snapshot. These isolation levels determine...
  • Craig Freedman's SQL Server Blog

    Partitioned Tables in SQL Server 2008

    • 13 Comments
    In this post , I introduced how SQL Server 2005 implements query plans on partitioned tables. If you've read that post or used partitioned tables, you may recall that SQL Server 2005 uses a constant scan operator to enumerate the list of partition ids...
  • Craig Freedman's SQL Server Blog

    GROUPING SETS in SQL Server 2008

    • 12 Comments
    In my last two posts, I gave examples of aggregation WITH ROLLUP and CUBE . SQL Server 2008 continues to support this syntax, but also introduces new more powerful ANSI SQL 2006 compliant syntax. In this post, I'll give an overview of the changes. ...
  • 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

    Aggregation WITH ROLLUP

    • 11 Comments
    In this post, I'm going to discuss how aggregation WITH ROLLUP works. The WITH ROLLUP clause permits us to execute multiple "levels" of aggregation in a single statement. For example, suppose we have the following fictitious sales data. (This is the same...
  • Craig Freedman's SQL Server Blog

    Sequential Read Ahead

    • 11 Comments
    Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize hardware utilization. SQL Server includes two asynchronous I/O mechanisms - sequential read ahead and random prefetching - that are designed to address this...
  • Craig Freedman's SQL Server Blog

    The PIVOT Operator

    • 10 Comments
    In my next few posts, I'm going to look at how SQL Server implements the PIVOT and UNPIVOT operators. Let's begin with the PIVOT operator. The PIVOT operator takes a normalized table and transforms it into a new table where the columns of the new table...
  • Craig Freedman's SQL Server Blog

    Implied Predicates and Query Hints

    • 9 Comments
    In this post, I want to take a look at how two seemingly unrelated features of SQL Server can interact to cause a problem. The idea for this post came from a question submitted by a reader. Let's begin. Consider the following trivial schema and query...
  • 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

    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

    Read Committed and Updates

    • 9 Comments
    Let's try an experiment. Begin by creating the following simple schema: create table t1 (a int, b int) create clustered index t1a on t1(a) insert t1 values (1, 1) insert t1 values (2, 2) insert t1 values (3, 3) create table t2 (a int) insert...
  • Craig Freedman's SQL Server Blog

    Query Failure with Read Uncommitted

    • 8 Comments
    Over the past month or so, I've looked at pretty much every isolation level except for read uncommitted or nolock. Today I'm going to wrap up this series of posts with a discussion of read uncommitted. Plenty has already been written about the dangers...
  • 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

    Subqueries in BETWEEN and CASE Statements

    • 8 Comments
    Consider the following query: CREATE TABLE T1 (A INT, B1 INT, B2 INT) CREATE TABLE T2 (A INT, B INT) SELECT * FROM T1 WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2 Observe that the subquery in this query only needs...
  • Craig Freedman's SQL Server Blog

    ROWCOUNT Top

    • 8 Comments
    If you've looked at any insert, update, or delete plans, including those used in some of my posts, you've probably noticed that nearly all such plans include a top operator. For example, the following update statement yields the following plan: CREATE...
  • Craig Freedman's SQL Server Blog

    More on TOP

    • 8 Comments
    Last week I wrote about a special case of the TOP operator known as ROWCOUNT TOP . This week I'll take a look at some other interesting TOP scenarios. In general, TOP is a fairly mundane operator. It simply counts and returns the specified number of rows...
  • Craig Freedman's SQL Server Blog

    Aggregation WITH CUBE

    • 8 Comments
    In my last post, I wrote about how aggregation WITH ROLLUP works. In this post, I will discuss how aggregation WITH CUBE works. Like the WITH ROLLUP clause, the WITH CUBE clause permits us to compute multiple "levels" of aggregation in a single statement...
  • Craig Freedman's SQL Server Blog

    Recursive CTEs continued ...

    • 8 Comments
    In this post, I will finish the discussion of recursive CTEs that I began in my last post. I will continue to use the CTE examples from Books Online . To run these examples, you'll need to install the Adventure Works Cycles OLTP sample database . In...
  • Craig Freedman's SQL Server Blog

    Maintaining Unique Indexes

    • 7 Comments
    Consider the following schema: CREATE TABLE T (PK INT PRIMARY KEY, A INT, B INT) CREATE INDEX TA ON T(A) CREATE UNIQUE INDEX TB ON T(B) INSERT T VALUES (0, 0, 0) INSERT T VALUES (1, 1, 1) Now suppose we run the following update statement: ...
  • 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...
Page 1 of 3 (74 items) 123