Partial Aggregation

In some of my past posts, I’ve discussed how SQL Server implements aggregation including the stream aggregate and hash aggregate operators.  I also used hash aggregation as an initial example in my introductory post on parallel query execution.   In this post, I’ll look at a partial aggregation.  Partial aggregation is a technique that SQL Server…

1

Parallel Query Execution Presentation

For those of you readers who’ve been wondering whatever happened to me, I’ve been rather busy.  Among other activities, I’ve been writing a chapter for Kalen Delaney’s upcoming fourth book in the Inside SQL Server 2005 series: Query Tuning and Optimization. I am hoping to resurrect my blog and to get things started, I’m posting…

8

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

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