How and when to restrict parallelism in SQL Server

Parallel plans are SQL Server queries that have been optimized to run in multiple threads across multiple processors. Most of the time this means the queries execute and return results faster. But sometimes SQL Server attempts to optimize the query for parallel execution and ends up with something that runs worse, not better. SQL Server does do a good deal of auto tuning (i.e., it spawns less parallel queries if CPU usage is high), but it cannot always predict when something is going to wind up running slower or less efficiently in multiple threads. Although this rarely happens, it's useful to know how to restrict parallelism when it degrades query performance.

One option is to go on a per-query basis. SQL Server 2000 has a query hint OPTION (MAXDOP x), which restricts the maximum amount of parallelism for that particular query. If you set OPTION (MAXDOP 1), for instance, you confine the processing for that query to a single processor. If multiple users were running the same query, each query would be parceled out to a different processor, but would not spawn more than one thread. More threads mean more parallelism, but they also mean more overhead due to context switching and higher memory usage.

You can also enforce degrees of parallelism throughout SQL Server by using the "Max degree of parallelism" option. (This option is found in SQL Server Enterprise Manager, under Properties for the server in question, and then in the Processor tab under "Specify the number

Requires Free Membership to View

of processors to use for parallel execution.") If this is set to 0, then SQL Server splits queries across all available processors; if it's set to 1, queries are confined to one processor at a time But again multiple instances of the same query will be parceled across all available processors.

Note that this is not the same as explicitly restricting SQL Server to only use a specific processor (also settable in the Processor tab). This almost never needs to be set unless you're using another program on the same machine that's also computationally intensive, and you want to confine each to its own processor.

Serdar Yegulalp is the editor of the Windows 2000 Power Users Newsletter. Check out his Windows 2000 blog for his latest advice and musings on the world of Windows network administrators -- please share your thoughts as well!

Thanks to Erland Sommarskog (esquel@sommarskog.se) for the details in this particular tip.

This was first published in December 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.