Help with Examining Parallel-Query Plans

Help with Examining Parallel-Query Plans

If you don't actually have a multiprocessor machine on which you can test the code in the main article, that doesn't mean that you'll never see a DegreeOfParallelism greater than one. You can simulate multiple processors by using a special flag when you start SQL Server.

To do so, in SQL Server Configuration Manager, expand the node for SQL Server 2005 Services. Right-click your SQL Server service and choose Properties. On the Advanced tab, expand the Startup Parameters value, and add -Pn after the last parameter, where n is a number greater than 1, to tell SQL Server to start up n schedulers, as Figure A shows below. (I frequently use -P4.)

Be sure to add a semicolon after the last parameter on the line, and don't leave any spaces after the semicolon or between the dash and the P. Click Apply, then start or restart your SQL Server service. Of course, you should never use this technique on a production machine; it's only for examining query plans for parallel queries, not for actually seeing the performance impact of parallel queries.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.