I want to learn how to analyze parallel query plans but I don't have access to a multi-processor server. Is there a way to simulate a parallel plan on a uni-processor server for testing and development purposes?
Yes, you can use the undocumented –P switch when you start SQL Server 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. SQL Server Books Online (BOL) topic "Using Startup Options" has more information about using startup options.
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. When SQL Server processes one query in multiple steps at the same time, that's a parallel plan. 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, 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 SQL Server's affinity mask option 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, Microsoft Product Support Services (PSS) doesn't support this switch and it works only on SQL Server 2000 Developer and Enterprise Editions.
