In some of our previous blog posts, we had covered parallel execution plans for queries and how to check for parallel query executions in Oracle. You can read more on those posts – here and here. However, if you want to simulate the effect of multiprocessor machines on the execution plans when you have only a single processor machine, there are ways to do that so that during development, the developer can gauge the impact properly. In this blog post, we will cover how you can simulate parallel query execution plans in SQL Server.
In SQL Server, there is an un-documented start-up switch: “-P”. What this switch does is that it initializes a particular number of User Mode Schedulers (UMS). There are a couple of ways that you can use this switch – via the registry, via the Start-up parameters screen for the instance propertie. You can get more detailed information from BOL under the topic of “Using startup options”. As you might already know, at the time of the start-up of the instance, SQL Server allocates one UMS per logical processor – logical because if it is a single processor but is hyper-threaded, that amounts to two logical processors. The number of UMSs that are running for a given instance is what then sets the maximum degree of parallelism for a given instance of SQL Server. So, say you want to simulate the effect of a quad processor box on your development laptop, you can simulate that using the -P4 as the start-up switch. The engine will them allocate 4 UMSs to the instance and will consider those for the parallel query execution plans. That way, you can simulate the effect of parallelisms on your queries. Read the blog posts from above to see when parallel queries are good and when they are not. This switch works only with the Developer and Enterprise Editions though and is valid for both SQL Server 2000 and 2005.
Also, please note that this is only for testing purposes – never do that on your production servers. This does not give you any extra processing power – this is just to simulate and test for the type of query plans that SQL Server will/can create for some queries based on the number of processors in the system.