Flash Tip: December 7, 2005

Flash Tip: Simulating Parallel Plans
By Brian Moran, savvy@sqlmag.com

Q: I want to learn how to analyze parallel query plans, but I don't have access to a multiprocessor server. Is there a way to simulate a parallel plan on a uniprocessor server for testing and development purposes?

A: Yes. When you start an instance of SQL Server, you can use the undocumented –P switch to force it to initialize a particular number of User Mode Schedulers (UMSs). This is a valuable technique if you need to experiment with how the optimizer might choose to process a parallel plan on a machine that has one logical processor. You can use particular startup options to start SQL Server through the command line, by setting registry settings, or from the Startup Parameters screen accessible from the General tab on the Server Properties dialog box. For more information about using startup options, see "Using Startup Options" in SQL Server Books Online.

Here's some background about parallel queries and the UMS so you can understand what happens when you choose the –P switch. This topic consumes entire chapters in books about SQL Server internals, so this is just a quick overview. A parallel plan is when SQL Server processes one query in multiple steps at the same time. However, when SQL Server interacts with the OS to manage threads, individual queries don't ask the OS to schedule them on a CPU. Instead, SQL Server assigns a query (or piece of a parallel query) to a UMS and that UMS is then responsible for scheduling the thread on and off a processor. The SQL Server process asks the UMS for processing time, and then the UMS brokers that request to the OS. The UMS also tells the OS when to stop running a particular SQL Server request so that the UMS can schedule something new. The analogy is simple, but I like to think of the UMS as a traffic cop that ensures a smooth flow of SQL Server traffic on and off a logical processor.

By default, SQL Server creates one UMS for each logical processor at startup. I'm using the term logical processor to refer to the two apparent processors that the OS sees when we have one hyper-threaded physical processor. In this case, we have one physical processor, but two logical processors; SQL Server assigns a UMS to each logical processor. Ultimately, the number of UMSs that are running within that SQL Server instance sets the maximum degree of parallelism for a step within a query. You can use the affinity mask option in SQL Server to limit how many UMSs will start; however, there isn't a documented way to tell SQL Server to create more UMSs than logical processors. That's what the –P switch does. For example, starting SQL Server with the –P4 option will create four UMSs even on a machine that has only two logical processors. The same switch can create four UMSs even if the machine has only one logical processor.

I would never encourage you to use the –P switch on a production box to allow a parallel plan to have a higher degree of parallelism than the actual number of physical processors. You're not really getting extra processing power. You're letting SQL Server create more than one UMS on one processor. This will inevitably lead to a variety of performance problems if a workload stresses the system. However, this technique can be a handy way to test the types of query plans that SQL Server will create for certain types of queries based on the number of processors in the system without actually needing to have that many processors. Please note that Microsoft Product Support Services doesn't support this switch and it works only on SQL Server 2000 Developer and Enterprise Editions.

This SQL Server tip is brought to you by SQL Server Magazine. As a holiday special, when you subscribe now you will get free access to the entire SQL Server Magazine article database (more than 2,300 articles). In addition, you will save more than US$40 off the full cover price of $71.40. This offer will expire on December 31, 2005, so order today.