Search results matching tags 'internals' and 'Parallelism'

Parallel Execution Plans Suck

Paul White — Wed, 02 May 2012 17:57:00 GMT

Summary: A deep dive into SQL Server parallelism, and a potential performance problem with parallel plans that use TOP.

There was an interesting question asked by Mark Storey-Smith on dba.stackexchange.com back in October 2011. He was looking at the execution plan for a query that counts a million rows from a virtual auxiliary table of numbers. The query below is a slightly-modified version of the one in the original post:

     WITH

        N1 (n) AS

        (

            -- 10 rows

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1

        ),

        N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R), -- 100 rows

        N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R), -- 10,000 rows

        N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R), -- 100,000,000 rows

        N5 AS (SELECT L.n FROM N4 AS L CROSS JOIN N1 AS R), -- 1,000,000,000 rows

        N6 AS 

        (

            SELECT TOP (1000000) n

            FROM N5

        )

    SELECT 

        COUNT_BIG(*)

    FROM N6

    OPTION (RECOMPILE, MAXDOP 1);

This particular virtual numbers table is capable of producing up to a (short-scale) billion rows, via a number of cross joins, but the final common table expression N6 limits it to one million with the TOP clause. The query plan is just a sequence of cross joins of the ten in-memory rows defined by the first common table expression, N1 (click to enlarge):

As usual, the Constant Scan operator is used to generate rows in memory without accessing a physical table, but this one has an interesting property: the ‘virtual table’ contains ten rows, but no columns. The query itself only counts rows, it does nothing at all with any column in those rows, and the optimizer contains logic to only project columns that are needed later in the query plan. If you look at the query plan in SSMS or SQL Sentry Plan Explorer, you will see that the Constant Scans have a blank output columns list; they project no columns at all.

Execution Plans Suck

The plan above does illustrate an important concept in plan-reading: execution plans start executing at the left-most node. People are often told to read execution plans from the top-right, and that is fine if you just want to follow the flow of data – so long as you bear in mind that the flow of program control starts at the root (far left).

Rob Farley (blog | twitter) sums this up by saying “execution plans suck”; a reference to the fact that rows are pulled up the tree by parent operators requesting rows, one at a time, from the its child operator(s). Query execution follows a demand-based pipeline model (except in batch mode in parallel 2012 column-store plans, but that is a tangent we will not be pursuing today). I’m not going to labour the point, but if you are interested to understand this better, take a look at my previous post on the topic, or Brad Schulz’s entertaining overview.

Let’s look at the execution plan (with runtime statistics) changing the TOP specification from one million to one hundred to make it easier to see what’s going on:

I’m just showing part of the plan for clarity. There are another four Constant Scans off to the right that all produce one row. If query plans really did start executing at the top right, we would expect one row from the right-most Constant Scan, ten rows from its parent, and one hundred at the next operator up the tree (working right to left). As it is, the expected pattern (one, then ten, then one hundred) appears closest to the Top operator. This only makes sense if a row at a time is sucked up the plan from the root. The pipelined (row-by-row) model also explains why execution stops after 100 rows; the Top operator in the plan simply stops requesting a new row from its immediate child at that point.

The Question

Back to the main thrust of today’s post. The question arose when Mark ran his query with parallelism enabled:

    WITH

        N1 (n) AS

        (

            -- 10 rows

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1

        ),

        N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R), -- 100 rows

        N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R), -- 10,000 rows

        N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R), -- 100,000,000 rows

        N5 AS (SELECT L.n FROM N4 AS L CROSS JOIN N1 AS R), -- 1,000,000,000 rows

        N6 AS 

        (

            SELECT TOP (1000000) n

            FROM N5

        )

    SELECT 

        COUNT_BIG(*)

    FROM N6

    OPTION (RECOMPILE, MAXDOP 2);

That produces an actual execution plan like this (click to enlarge):

The question relates to the actual number of rows shown entering and leaving the Gather Streams exchange operator:

As expected, one million rows leave the exchange operator, but the plan shows 1,004,588 rows entering it. So the question is, are the row counts wrong, or if not, where did the rows go?

The Answer

As you may know, the answer lies in the fact that exchanges contain buffers. For efficiency reasons, rows are not passed across threads one by one as in the normal model discussed above. Instead, producer threads (on the right side of the exchange operator) pack rows one at a time into packets, and push completed packets across the exchange. Consumer thread(s) on the left side of the exchange retrieve rows one at a time from the current packet on demand, re-establishing the demand-based pipeline model. The internal class name for the packets, by the way, is Class eXchange PACKET – which gives us the familiar CXPACKET moniker.

Exploring the detail at little more, we can see from the execution plan that the two independent producer threads to the right of the exchange pack a total of 499,225 + 505,363 = 1,004,588 rows into packets, and the single consumer thread (thread zero) retrieves just the one million rows needed by the Top operator:

So, the actual row counts shown on the execution plan are correct, and the ‘missing rows’ are accounted for by rows that were added to packets but not ultimately needed by the consumer. After the one millionth row is passed by the Top to the Stream Aggregate (and remember all these things happen one row at a time), the next time the Top gets control, it starts the process of shutting down the pipeline below it, rather than asking the Gather Streams exchange for another row. Glossing over the finer details a little, instead of the Top calling a GetRow() method on the Gather Streams iterator, it calls a Close() method instead.

SQL Server 2008+

On SQL Server 2005 that is the end of the story. On SQL Server 2008 and later (including 2012), there is more. Let’s run the query one more time, but this time with a degree of parallelism of three, rather than two. Sometimes, we will get a plan that contains this sort of row count arrangement at the Gather Streams:

Much the same as before, a few thousand extra rows are processed by the producer threads than are ultimately needed by the consumer. That’s fine, of course, the threads in a parallel plan execute independently, so there are bound to be small timing differences that lead to this sort of effect. Every so often, however, executing this query on SQL Server 2008 or above will produce a result like this:

Whoa. Nearly 335 million rows entering the exchange – and the query now runs for 50 seconds or so, having run consistently for around 200ms previously. Looking at the per-thread actual row counts is revealing:

If everything were perfectly balanced, we might expect each of the three producer threads to process around 333,000 rows. Indeed, thread 1 and thread 2 are in this ballpark area, but thread 3 ground through 334 million rows on its own! I should mention that there is nothing special about thread 3 (you are as likely to find the huge row count on thread 1 or thread 2, the labels are arbitrary). Indeed, the problem can occur on any or all threads, as a second example run at DOP 3 shows below:

This time two threads went rogue, resulting in over 667 million rows being processed in a total execution time of 67 seconds.

Parallelism Problems?

There are other parallelism (exchange) operators in the plan, though we have concentrated only on the final Gather Streams operator so far. By the way, Gather Streams is also known as a Start Parallelism operator – a name that might surprise you since it seems to mark the end of parallelism in the plan, rather than the start. Remember that execution plans suck. The ‘final’ Gather Streams is in fact the first operator to start executing, and it is responsible for starting the parallel workers and attaching them to the threads that were reserved for this plan at the very start of execution. Anyway, I digress. Let’s look at the next exchange in the plan – reading left to right of course. This is a Repartition Streams exchange operating in Round Robin distribution mode:

This shows that 1,000 rows arriving on the producer side of the exchange, and a total of 668 on the consumer side. Not shown in the diagram above, the producer side has 340 rows on thread 1, and 330 rows on each of thread 2 and thread 3. Note that these are not the same threads as the ones we saw numbered the same way before. The numbering scheme is restarted for each independent parallel zone in the plan (and zones are bounded by a parallelism operator of one sort or another). Anyway, the point is that the rows are pretty evenly distributed on the producer side of the exchange.

On the consumer side (row counts illustrated above), things are rather different. Thread 1 (in this zone) processes 334 rows, thread 2 gets 333, and thread 3 gets only one. Now these are the same threads as shown in the 667 million row diagram. I mentioned that parallel zones are bordered by parallelism operators; the current zone is bordered by the Repartition Streams on its right side, and by the Gather Streams on its left. The same three threads are consumers at the Repartition Streams, and producers at the Gather Streams operator:

There is a clear relationship between the thread row counts at the consumer side of the Repartition Streams (334, 333, and 1 row) and the row counts at the producer side of the Gather Streams (334 million, 333 million, 338,614 rows). The two problematic threads have multiplied their row counts by a factor of one million – precisely the effect of the six cross joins in this parallelism zone. The Constant Scan virtual tables contain ten rows each, and multiplying by ten, six times over, gives a factor of one million.

Rogue Row Goals

Thread 3 in the example above ends up processing 338,614 rows. This number has no special significance, except it shows that this thread did not run this portion of the plan to completion. If it had, the single row it started with would have ended up as one million rows by the time it had been cross-joined six times with the ten-row Constant Scan table.

This is the row goal in action (if you need a details on exactly what a row goal is, please see my previous post). Essentially, though, things like a TOP operator or a FAST n hint set a row goal. Plans normally run to completion, but row goals modify this, producing a plan that tries to optimize for a certain number of rows rather than the full potential set. The TOP operator is even more special. As discussed briefly before, TOP can bring execution to an early end – instead of continuing to ask for rows from its child iterator, it calls the Close() method instead. This call filters down the tree, and execution comes to an early end. (Strictly, row goals affect optimizer choices and plan costing rather than being associated with the early end of execution, but I like the phrase ‘rogue row goal’).

So, thread 3 did not run to completion – it responded to the early Close() call and only processed 338 thousand of the one million rows it could have produced if left alone. Threads 1 and 2 never received the Close() call, or chose to ignore it. These two rogues went on to process their full potential row set – 667 million rows – despite the fact that the TOP had seen the million rows it needed and was waiting for operations elsewhere in the parallel plan to stop. We can see this by looking at the sys.dm_os_waiting_tasks DMV while the long-running query is executing:

The output is split across two lines for readability, and shows execution context zero blocked on a CXPACKET wait by both execution context 1 and execution context 3. Execution context zero is always thread zero – the so-called coordinator thread that runs the serial part of the execution plan to the left of the left-most Gather Streams operator. Put another way, context zero always runs the part of the plan before (reading left to right) the Start Parallelism operator (and therefore always runs a serial plan).

OK, so CXPACKET means the thread is involved in a parallelism-related wait. The extra detail in the resource_description column tells us that the wait is occurring at the node id 2. Checking the execution plan, we see that node 2 is indeed the Gather Streams exchange:

The wait type of e_waitPortClose means the consumer is waiting for the port to close. If you are wondering what a port is in this context, I will just say the parallelism architecture is more complex than just CXPACKET – the wiring includes a CXPort class, a CXPipe class, a CXTransLocal (local transport) class and a CXTransRemote class (for the Parallel Data Warehouse edition). There is also a linked map structure that shows how the various pipes, ports, and transports connect together. Closing the port is one step in shutting down part of a parallel plan which is running on the other side of an exchange (via a transport, pipe, and port). The stack trace below shows thread zero waiting for the port to close:

The important things are that (a) this problem does not occur in SQL Server 2005; and (b) a number of changes were made to the internal parallelism implementation in SQL Server 2008. These changes seem to have introduced a bug, where the consumer can wait for the port to close, but one or more producers either ignore the request, or fail to check for it, and go on to process the whole potential result set instead of stopping early.

Not Just Nested Loops

Fellow SQLblogger Joe Chang (blog) suggested in the comments that this problem may only occur with parallel nested loops joins. The script below reproduces the problem with parallel hash joins:

    WITH

        N1 (n) AS

        (

            -- 10 rows

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

            SELECT 1

        ),

        N2 AS (SELECT L.n FROM N1 AS L JOIN N1 AS R ON L.n % 1 = R.n % 1), -- 100 rows

        N3 AS (SELECT L.n FROM N2 AS L JOIN N2 AS R ON L.n % 1 = R.n % 1), -- 10,000 rows

        N4 AS (SELECT L.n FROM N3 AS L JOIN N3 AS R ON L.n % 1 = R.n % 1), -- 100,000,000 rows

        N5 AS (SELECT L.n FROM N4 AS L JOIN N1 AS R ON L.n % 1 = R.n % 1), -- 1,000,000,000 rows

        N6 AS 

        (

            SELECT TOP (1000000) n

            FROM N5

        )

    SELECT 

        COUNT_BIG(*)

    FROM N6

    OPTION (RECOMPILE, MAXDOP 4, QUERYTRACEON 8649);

The full execution plan is rather large, but the key part is shown below (click to enlarge):

Yes, that is 700 million rows entering the Gather Streams exchange (click to enlarge):

The bug does not occur in every query plan with Top and parallelism, but the choice of nested loops join is not the cause.

Final Thoughts

The potential for poor performance and excessive processor usage here is obvious; and the chance of hitting the race condition gets worse at higher DOP. With eight threads per parallel zone (DOP 8), I hit this issue almost every time on SQL Server 2008, 2008 R2, and 2012. Because this behaviour does not occur on SQL Server 2005, but does on 2008 and later, I have filed this as a bug on Connect:

https://connect.microsoft.com/SQLServer/feedback/details/740234/poor-performance-with-parallelism-and-top

How Parallelism Works in SQL Server

Paul White — Sat, 05 Mar 2011 08:28:00 GMT

You might have noticed that January was a quiet blogging month for me. Part of the reason was that I was working on a series of articles for Simple Talk, examining how parallel query execution really works. The first part is published today at:

http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/.

This introductory piece is not quite as deeply technical as my SQLblog posts tend to be, but I hope there be enough interesting material to make it worth a read.

Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_Kiwi

Myth: SQL Server Caches a Serial Plan with every Parallel Plan

Paul White — Thu, 04 Nov 2010 08:23:00 GMT

Many people believe that whenever SQL Server creates an execution plan that uses parallelism, an alternative serial plan is also cached. The idea seems to be that the execution engine then decides between the parallel and serial alternatives at runtime.

I’ve seen this on forums, in blogs, and even in books. In fairness, a lot of the official documentation is not as clear as it might be on the subject. In this post I will show that only a single (parallel) plan is cached. I will also show that SQL Server can execute a parallel plan on a single thread…

Before diving into the demonstration, I want to quickly run through some background information about the SQL Server plan cache.

Compiled Plans

Queries are expensive to compile and optimize, so SQL Server uses caching to improve efficiency through plan reuse. The server optimizes an entire batch all at once, and the result is known as a compiled plan (or sometimes ‘query plan’).

The dynamic management view sys.dm_exec_cached_plans contains one row for each compiled plan, with a plan_handle that uniquely identifies the compiled plan among those currently in cache (plan handles can be reused over time). This plan handle can be passed to the dynamic management function sys.dm_exec_query_plan to show the compiled plan in XML format. When displayed in Management Studio, we can click on this XML representation to view the familiar graphical plan.

A compiled plan is a compile-time object – no user or runtime context is stored. You might find it helpful to think of the compiled plan as a template – or perhaps as being similar to the estimated execution plans seen in Management Studio.

Execution Contexts

An execution context (or ‘executable plan’) - internally known as an MXC – is generated when a compiled plan is prepared for execution. Execution contexts contain specific runtime information, for a single execution, for a single user.

thinking of the compiled plan as a template, an execution context is the concrete executable object derived by filling in run-time details like parameter values, local variables, details of objects created at runtime, and state information (for example, which statement within the batch is currently executing). The following image, reproduced from the Plan Caching in SQL Server 2008 White Paper, shows the relationship:

If five users concurrently run the same batch, there might be a single compiled plan, but there will always be five execution contexts containing runtime information for each of the separate executions. Execution contexts never exist without a parent compiled plan.

Execution contexts can be cached, but they are much cheaper to recreate than a compiled plan, which can only be regenerated by compiling a SQL query. Execution contexts can be regenerated directly from the compiled plan. Execution contexts that are not in use can be reinitialized with contextual information for a new user and runtime conditions.

You can find more information in the Books Online entry Execution Plan Caching and Reuse. For those of you that want to explore the very deep internals of plan cache structures, there is an excellent blog entry by the SQL Server Programmability and API Development Team.

The Demonstration Script

As usual, I’m going to use a query against the AdventureWorks sample database, using SQL Server 2008 SP1 CU10 (build 10.0.2799). The test rig I use in this post is best run on 2008 – it will execute on 2005, but there are important issues with that, which I will cover later. It will not run correctly on Express Edition (any version).

     DECLARE @PID INTEGER,

            @Qty INTEGER;

    SELECT  @PID = P.ProductID,

            @Qty = SUM(TH.Quantity)

    FROM    Production.Product P

    JOIN    Production.TransactionHistory TH

            ON  TH.ProductID = P.ProductID

    WHERE   P.ProductID % 27 = 0

    GROUP   BY

            P.ProductID

    ORDER   BY

            SUM(TH.Quantity) ASC;

The query itself isn’t important – it doesn’t even produce any output thanks to the variable assignments. The important thing is that it produces an interesting parallel plan, if we lower the cost threshold for parallelism a bit from the default (click to expand the image):

This plan runs every iterator except the final sort on multiple threads, and includes a number of parallel-plan-only features. There are three ‘exchanges’ (two Repartition Streams and one Gather Streams), and a bitmap. See Parallel Query Processing and Bitmap Showplan Operator in Books Online for more information on exchanges and bitmaps.

Those of you that are very familiar with parallel plans might find it interesting that the Bitmap appears below a Merge Join – these are much more frequently seen in plans featuring a Hash Join (but I digress).

Environment

The first part of the test script sets the server max degree of parallelism option to zero, affinity mask to zero, and cost threshold for parallelism to 1. These changes are simply to ensure that the optimizer produces a parallel plan for our test query.

    -- =================

    -- Environment Setup

    -- =================

    -- Connection settings

    SET     ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

    SET     NUMERIC_ROUNDABORT OFF;

    GO

    USE     AdventureWorks;

    GO

    -- Ensure advanced configuration options are available

    EXECUTE sys.sp_configure

                @configname = 'show advanced options',

                @configvalue = 1;

    -- Allow SQL Server to use all cores initially

    EXECUTE sys.sp_configure

                @configname = 'affinity mask',

                @configvalue = 0;

    -- Ensure server MAXDOP allows parallelism

    EXECUTE sys.sp_configure

                @configname = 'max degree of parallelism',

                @configvalue = 0;

    -- Reduce cost threshold for parallelism to make it easier

    -- to produce a parallel plan

    EXECUTE sys.sp_configure

                @configname = 'cost threshold for parallelism',

                @configvalue = 1;

    -- Commit the configuration changes

    RECONFIGURE;

    GO

Procedures

The second part creates two stored procedures: one to run the test query, and one to show relevant information about the plan cache:

    -- =================

    -- Test objects

    -- =================

    IF      OBJECT_ID(N'dbo.TestQuery', N'P')

            IS NOT NULL

            DROP PROCEDURE dbo.TestQuery;

    IF      OBJECT_ID(N'dbo.ShowPlanInfo', N'P')

            IS NOT NULL

            DROP PROCEDURE dbo.ShowPlanInfo;

    GO

    CREATE  PROCEDURE dbo.TestQuery

    AS

    BEGIN

            SET     NOCOUNT ON;

            DECLARE @PID INTEGER,

                    @Qty INTEGER;

            SELECT  @PID = P.ProductID,

                    @Qty = SUM(TH.Quantity)

            FROM    Production.Product P

            JOIN    Production.TransactionHistory TH

                    ON  TH.ProductID = P.ProductID

            WHERE   P.ProductID % 27 = 0

            GROUP   BY

                    P.ProductID

            ORDER   BY

                    SUM(TH.Quantity) ASC;

    END;

    GO

    CREATE  PROCEDURE dbo.ShowPlanInfo

    AS

    BEGIN

            SET     NOCOUNT ON;

            SELECT  cached_object_type = CP.cacheobjtype,

                    plan_use_count = CP.usecounts,

                    P.query_plan,

                    source_text = TXT.[text],

                    context_type = EC.cacheobjtype,

                    context_use_count = EC.usecounts,

                    ATTR.roundabort,

                    ATTR.set_options,

                    CP.plan_handle

            FROM    sys.dm_exec_cached_plans CP

            CROSS

            APPLY   sys.dm_exec_query_plan(CP.plan_handle) P

            CROSS

            APPLY   sys.dm_exec_cached_plan_dependent_objects(CP.plan_handle) EC

            CROSS

            APPLY   sys.dm_exec_sql_text(CP.plan_handle) TXT

            CROSS

            APPLY   (

                    SELECT  roundabort = (CONVERT(INTEGER, PVT.set_options) & 8192) / 8192,

                            PVT.set_options

                    FROM    (

                            SELECT  attribute,

                                    value

                            FROM    sys.dm_exec_plan_attributes(CP.plan_handle)

                            ) ATTR

                    PIVOT   (

                            MAX(value) 

                            FOR attribute IN (set_options)

                            ) PVT

                    ) ATTR

            WHERE   CP.cacheobjtype = N'Compiled Plan'

            AND     CP.objtype = N'Proc'

            AND     TXT.[text] LIKE N'%dbo.TestQuery%'

            AND     TXT.[text] NOT LIKE N'%sys.dm_exec_cached_plans%';

    END;

    GO

The TestQuery stored procedure is a simple wrapper for the test SQL statement shown earlier.

The ShowPlanInfo stored procedure uses dynamic management views and functions to show the following information about plan cache objects:

cached_object_type – Always ‘Compiled Plan’ for our test procedure
plan_use_count – The number of times the compiled plan has been used (or reused)
query_plan – The XML representation of the plan. Click on this in SSMS to show it in graphical form
source_text – The SQL batch that produced the plan (the CREATE PROCEDURE statement in our case)
context_address – The memory address of each execution context derived from the compiled plan
context_type – Always ‘Executable Plan’ (aka execution context)
context_use_count – The number of times the execution context has been reinitialized and reused
roundabort – The setting of the NUMERIC_ROUNDABORT connection used when creating the compiled plan
plan_handle – The compiled plan identifier (guaranteed to be unique per compiled plan in cache)

The Tests

The third part of the script runs three tests:

The test query with affinity mask set to zero (parallel execution on all available logical CPUs)
The test query with affinity mask set to one (execution on single logical CPU zero)
The test query after changing a connection SET option (to generate a new compiled plan)

    -- =================

    -- Tests

    -- =================

    -- Reset

    CHECKPOINT;

    DBCC    DROPCLEANBUFFERS;

    DBCC    FREEPROCCACHE;

    GO

    -- Test 1: Parallel plan running on all available cores

    EXECUTE dbo.TestQuery;

    EXECUTE dbo.ShowPlanInfo;

    GO

    -- Set affinity mask to limit SQL Server to one core

    EXECUTE sys.sp_configure

                @configname = 'affinity mask',

                @configvalue = 1;

    RECONFIGURE;

    GO

    -- Test 2: Reusing the cached parallel plan, but running on a single scheduler

    EXECUTE dbo.TestQuery;

    EXECUTE dbo.ShowPlanInfo;

    GO

    -- Test 3:

    -- Changing a SET option

    SET     NUMERIC_ROUNDABORT ON;

    EXECUTE TestQuery;

    EXECUTE dbo.ShowPlanInfo;

    SET     NUMERIC_ROUNDABORT OFF;

    GO

Test 1 – Normal Parallel Execution

Running Test 1 with the SSMS option ‘Include Actual Execution Plan’ on, produces the parallel plan shown previously:

Selecting the connector running from the Clustered Index Scan to the Hash Match (Partial Aggregate) and looking in the SSMS Properties window, we see that this part of the plan executed on eight threads:

The XML version of the graphical show plan contains elements that confirm the parallel execution:

For reasons that will become apparent shortly, I also want to highlight the information shown for the line connecting the Bitmap iterator to the Sort:

The output from the plan information procedure is (click to enlarge):

This shows a single cached compiled plan, with a single associated execution context. Click on the XML query plan in SSMS to see the graphical representation of the compiled plan (notice the lack of any runtime information in a compiled plan).

Test 2 – Limited to a Single Logical CPU

Before running Test 2, the script sets the affinity mask to limit SQL Server to using a single execution unit. Prior to SQL Server 2005, changing this option required a service restart, but the change is now dynamically applied in all editions except Express Edition.

Importantly, unlike the max degree of parallelism and cost threshold for parallelism configuration options, changing the affinity mask does not clear the procedure cache. This allows the plan cached by Test 1 to be potentially reused by Test 2.

Running Test 2 (again with actual execution plan on) we again get a parallel plan:

The first surprise, perhaps, is that we see a parallel plan at all – after all, we have limited SQL Server to a single CPU. The little yellow circles are still there, as are the Exchange and Bitmap iterators. Closer inspection reveals some important differences. Here’s the information shown in the SSMS Properties panel for the line connecting the Clustered Index Scan to the Hash Match (Partial Aggregate):

Instead of the per-thread row counts we saw in Test 1, we see a single count for ‘All threads’. This is exactly what we see when a serial plan is executed.

The XML show plan information contains:

The degree of parallelism is shown as zero, again exactly as for a serial plan. The individual iterator information still shows Parallel=”true”, but the run time information shows a single thread of execution. We can go further and monitor DMVs like sys.dm_os_tasks, but suffice it to say that the parallel plan shown does indeed execute on a single thread.

Another important difference can be seen by examining the tool-tips shown when hovering over the other connecting lines in the actual execution plan. All the connectors originating from a parallelism-specific iterator show only estimated information. As a reminder, the parallelism-specific iterators are the two Repartition Streams, the Gather Streams, and Bitmap iterators. For comparison with Test 1, here is the tool-tip shown for the connector following the Bitmap, as before. Notice that the Actual Number of Rows information is missing.

The output from the plan information procedure is:

There is still just a single cached compiled plan and a single execution context, but the usage counts for both have increased from 1 to 2. This shows that SQL Server was able to reuse the parallel compiled plan, but execute it on a single thread.

Running a Parallel Compiled Plan Serially

SQL Server can derive a serial execution context from a parallel compiled plan at runtime. It does this by removing the parallel-plan-specific operators. This results in a serial execution context with the same plan shape as the parallel compiled plan. This explains why the actual execution plan shown in SSMS lacks runtime information for the parallel operators – they were not present in the execution context.

SQL Server can do this because it is always possible to convert a parallel compiled plan to serial form – though the resulting serial plan may not be the optimal serial plan.

The reverse is not true, however: a serial compiled plan cannot have parallel operators dynamically inserted to produce a parallel execution context.

Test 3 – After Changing a SET Option

This test changes the connection SET option NUMERIC_ROUNDABORT to ON, before running the test procedure. The idea here is that SQL Server will be unable to reuse the cached plan (see Query Tuning Recommendations in Books Online). In fact, we receive a serial plan, with a very different shape from the parallel version seen previously:

The output from the plan information procedure confirms that a new serial compiled plan was cached, and a new execution context was created:

The point here is to show that the optimal serial plan is very different from the optimal parallel plan. If SQL Server really had cached two optimized plans (one serial, one parallel) for our query, we would expect Test 2 to use the optimal serial plan, rather than a derivation of the parallel one.

Clean-Up

    -- =================

    -- Clean up

    -- =================

    EXECUTE sys.sp_configure

                @configname = 'affinity mask',

                @configvalue = 0;

    EXECUTE sys.sp_configure

                @configname = 'cost threshold for parallelism',

                @configvalue = 5;

    RECONFIGURE;

    GO

    DROP PROCEDURE  

            dbo.TestQuery,

            dbo.ShowPlanInfo;

    GO

You may need to modify the values shown if you had previously changed your server settings from the defaults.

Issues with SQL Server 2005

The ability to dynamically change the affinity mask setting was introduced in SQL Server 2005. As is sometimes the case with new features, engine support was not quite perfect first time around. If you run the test script on SQL Server 2005 you will probably not see the illustrated results.

The problem is that the engine does not quite handle things correctly for the first execution of a cached compiled plan after the affinity mask option is changed. For example, the execution of Test 2 (with affinity mask changed to 1) will result in a multiple threads running on a single scheduler (logical CPU). The second, and subsequent, executions after changing the affinity mask will run correctly and as shown above (a single thread using a parallel plan).

So, the workaround is to disregard the results from the first execution of the TestQuery stored procedure after any change to the affinity mask setting (or more precisely the required call to RECONFIGURE). All rather unfortunate, but there we are.

The second limitation with SQL Server 2005 comes with Test 3. Although a new compiled plan is generated and cached, you will not see the serial plan shown. The optimizer produces a parallel compiled plan, despite the setting of the affinity mask. The parallel plan is nevertheless executed serially if affinity mask remains set to limit execution to a single logical CPU. The behaviour of the optimizer changed between 2005 and 2008 in this respect – opinions may vary on which is the better approach.

Summary

The result of a single query compilation is a single compiled plan, either serial or parallel
The execution engine can dynamically derive a serial execution context from a parallel cached compiled plan

If anyone is able to show serial and parallel versions of a plan being cached from a single compilation, please let me know via email or in the comments section below.

Acknowledgements

My thanks go to Adam Machanic (blog | twitter) and Gail Shaw (blog | twitter) for their assistance. Any errors that remain are entirely my own, naturally.

Paul White
email: SQLkiwi@gmail.com
twitter: @SQL_kiwi