Windows Vista
Windows 7
Windows Azure
Windows Server
Windows Phone
Windows Server

SQL SErver 2008 R2 : Parallel Query Processing

11/18/2011 3:17:58 PM
The query processor in SQL Server 2008 includes parallel query processing—an execution strategy that can improve the performance of complex queries on computers with more than one processor.

SQL Server inserts exchange operators into each parallel query to build and manage the query execution plan. The exchange operator is responsible for providing process management, data redistribution, and flow control. The exchange operators are displayed in the query plans as the Distribute Streams, Repartition Streams, and Gather Streams logical operators. One or more of these operators can appear in the execution plan output of a query plan for a parallel query.

Whereas a parallel query execution plan can use more than one thread, a serial execution plan, used by a nonparallel query, uses only a single thread for its execution. Prior to query execution time, SQL Server determines whether the current system state and configuration allow for parallel query execution. If parallel query execution is justified, SQL Server determines the optimal number of threads, called the degree of parallelism, and distributes the query workload execution across those threads. The parallel query uses the same number of threads until the query completes. SQL Server reexamines the optimal degree of parallelism each time a query execution plan is retrieved from the procedure cache. Individual instances of the same query could be assigned a different degree of parallelism.

SQL Server calculates the degree of parallelism for each instance of a parallel query execution by using the following criteria:

  • How many processors does the computer running SQL Server have, and how many are allocated to SQL Server?

    If two or more processors are allocated to SQL Server, it can use parallel queries.

  • What is the number of concurrent active users?

    The degree of parallelism is inversely related to CPU usage. The Query Optimizer assigns a lower degree of parallelism if the CPUs are already busy.

  • Is sufficient memory available for parallel query execution?

    Queries, like other processes, require resources to execute, particularly memory. Obviously, a parallel query demands more memory than a serial query. More importantly, as the degree of parallelism increases, so does the amount of memory required. The Query Optimizer carefully considers this in developing a query execution plan. The Query Optimizer could either adjust the degree of parallelism or use a serial plan to complete the query.

  • What is the type of query being executed?

    Queries that use several CPU cycles justify using a parallel execution plan. Some examples are joins of large tables, substantial aggregations, and sorting of large result sets. The Query Optimizer determines whether to use a parallel or serial plan by checking the value of the cost threshold for parallelism.

  • Are a sufficient number of rows processed in the given stream?

    If the Query Optimizer determines that the number of rows in a stream is too low, it does not execute a parallel plan. This prevents scenarios where the parallel execution costs exceed the benefits of executing a parallel plan.

Regardless of the answers to the previous questions, the Query Optimizer does not use a parallel execution plan for a query if any one of the following conditions is true:

  • The serial execution cost of the query is not high enough to consider an alternative parallel execution plan.

  • A serial execution plan exists that is estimated to be faster than any possible parallel execution plan for the particular query.

  • The query contains scalar or relational operators that cannot be run in parallel.

Parallel Query Configuration Options

Two server configuration options—maximum degree of parallelism and cost threshold for parallelism—affect the consideration for a parallel query. Although doing so is not recommended, you can change the default settings for each. For single processor machines, these settings are ignored.

The maximum degree of parallelism option limits the number of threads to use in a parallel plan execution. The range of possible values is 0 to 32. This value is configured to 0 by default, which allows the Query Optimizer to use up to the actual number of CPUs allocated to SQL Server. If you want to suppress parallel processing completely, set the value to 1.

The cost threshold for parallelism option establishes a ceiling value the Query Optimizer uses to consider parallel query execution plans. If the calculated value to execute a serial plan is greater than the value set for the cost threshold for parallelism, a parallel plan is generated. This value is defined by the estimated time, in seconds, to execute the serial plan. The range of values for this setting is 0 to 32767. The default value is 5. If the maximum degree of parallelism is set to 1, or if the computer has a single processor, the cost threshold for parallelism value is ignored.

You can modify the settings for the maximum degree of parallelism and the cost threshold for parallelism server configuration options either by using the sp_configuresp_configure system stored procedure via SSMS or via SQLCMD, as follows: system stored procedure or through SSMS. To set the values for these options, use the

USE master
exec sp_configure 'show advanced options', 1
exec sp_configure 'max degree of parallelism', 2
exec sp_configure 'cost threshold for parallelism', 15

To set these configuration options via SSMS, right-click the SQL Server instance in the Object Explorer and then click Properties. In the Server Properties dialog, select the Advanced page. The parallelism options are near the bottom, as shown in Figure 1.

Figure 1. Setting SQL Server parallelism options.

Identifying Parallel Queries

You can identify when a parallel execution plan is being chosen by displaying the graphical execution plan in SSMS. The graphical execution plan uses icons to represent the execution of specific statements and queries in SQL Server. The execution plan output for every parallel query has at least one of these three logical operators:

  • Distribute Streams— Receives a single input stream of records and distributes multiple output streams. The contents and form of the record are unchanged. All records enter through the same single input stream and appear in one of the output streams, preserving the relative order.

  • Gather Streams— Assembles multiple input streams of records and yields a single output stream. The relative order of the records, contents, and form is maintained.

  • Repartition Streams— Accepts multiple input streams and produces multiple streams of records. The record contents and format are unchanged.

Figure 2 shows a portion of a sample query plan that uses parallel query techniques—both repartition streams and gather streams.

Figure 2. A graphical execution plan of a query using parallel query techniques.

Parallel Queries on Partitioned Objects

SQL Server 2008 provides improved query processing performance for partitioned objects when running parallel plans including changes in the way parallel and serial plans are represented, and enhancements to the partitioning information provided in both compile-time and runtime execution plans. SQL Server 2008 also automates and improves the thread partitioning strategy for parallel query execution plans on partitioned objects.

In addition to the performance improvements, query plan information has been improved as well in SQL Server 2008, now providing the following information related to partitioned objects:

  • The partitions accessed by the query, available in runtime execution plans.

  • An optional Partitioned attribute indicating that an operation, such as a seek, scan, insert, update, merge, or delete, is performed on a partitioned table.

  • Summary information that provides a total count of the partitions accessed. This information is available only in runtime plans.

Other -----------------
- SQL SErver 2008 R2 : Other Query Processing Strategies
- Configuring and Using Active Directory Certificate Services (part 4) - Protecting Your AD CS Configuration
- Configuring and Using Active Directory Certificate Services (part 3) - Considerations for the Use and Management of AD CS & Working with Enterprise PKI
- Configuring and Using Active Directory Certificate Services (part 2) - Finalizing the Configuration of an Online Responder
- Configuring and Using Active Directory Certificate Services (part 1) - Finalizing the Configuration of an Issuing CA
- Windows Server 2003 : The Security Configuration Wizard
- Windows Server 2003 : Understanding Security Considerations
- Microsoft Content Management Server : Moving Postings
- Microsoft Content Management Server : Copying Postings
- Upgrading to Systems Management Server 2003 - Upgrading a Primary Site & Upgrading a Secondary Site
Top 10 video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Trailer game
Natural Miscarriage
Windows Vista
Windows 7
Windows Azure
Windows Server
Game Trailer