Semi-join Transformation

In several of my prior posts, I’ve given examples of semi-joins.  Recall that semi-joins essentially return a row from one input if we can find at least one matching row from the other input.  Here is a simple example: create table T1 (a int, b int) create table T2 (a int, b int)   set…

0

Introduction to Partitioned Tables

In this post, I’m going to take a look at how query plans involving partitioned tables work.  Note that there is a big difference between partitioned tables (available only in SQL Server 2005) and partitioned views (available both in SQL Server 2000 and in SQL Server 2005).  I will look at the query plans for…

5

Parallel Hash Join

SQL Server uses one of two different strategies to parallelize a hash join.  The more common strategy uses hash partitioning.  In some cases, we use broadcast partitioning; this strategy is often called a “broadcast hash join.” Hash Partitioning The more common strategy for parallelizing a hash join involves distributing the build rows (i.e., the rows…

6

Parallel Nested Loops Join

SQL Server parallelizes a nested loops join by distributing the outer rows (i.e., the rows from the first input) randomly among the nested loops threads.  For example, if we have two threads running a nested loops join, we send about half of the rows to each thread.  Each thread then runs the inner side (i.e.,…

6

Parallel Scan

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. How does parallel scan work? The threads that…

3

The Parallelism Operator (aka Exchange)

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 the threads. The iterator that’s really two iterators The exchange iterator is unique in that it is really…

3

Index Union

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 table T (a int, b int, c int, x char(200)) create unique clustered index Ta…

0

Introduction to Parallel Query Execution

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 be used to a run a bigger query (one that processes more data)…

5

Decorrelating Subqueries

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 int, b int, c int)   select *…

1

Scalar Subqueries

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) from T2 where T2.b < T1.b) The subquery in this example uses an…

1