How and when to restrict parallelism in SQL Server

Parallelism helps speed up SQL Server query results, unless SQL Server attempts to optimize the query for parallel execution first. Learn how and when to restrict parallelism here.

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 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

Dig deeper on Windows Storage Systems Hardware

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchServerVirtualization

SearchCloudComputing

SearchExchange

SearchSQLServer

SearchWinIT

SearchEnterpriseDesktop

SearchVirtualDesktop

Close