Understanding and Controlling Parallel Query Processing in SQL Server

Writers: Don Pinto, Eric Hanson

Technical Reviewers: Kevin Cox, Thomas Kejser, Jay (In-Jerng) Choe

Published: October 2010

Applies to: SQL Server 2008 R2

Summary: Data warehousing and general reporting applications tend to be CPU intensive because they need to read and process a large number of rows. To facilitate quick data processing for queries that touch a large amount of data, Microsoft SQL Server exploits the power of multiple logical processors to provide parallel query processing operations such as parallel scans. Through extensive testing, we have learned that, for most large queries that are executed in a parallel fashion, SQL Server can deliver linear or nearly linear response time speedup as the number of logical processors increases. However, some queries in high parallelism scenarios perform suboptimally. There are also some parallelism issues that can occur in a multi-user parallel query workload. This white paper describes parallel performance problems you might encounter when you run such queries and workloads, and it explains why these issues occur. In addition, it presents how data warehouse developers can detect these issues, and how they can work around them or mitigate them.

 

To review the document, please download the Understanding and Controlling Parallel Query Processing in SQL Server Word document.

• Ask a question in the SQL Server 2008 Forums
• Send Feedback