DaveWentzel.com            All Things Data



Parallelism in Execution Plans
Query should run faster but may consume more resources
1 thread:  10 sec run time, 10 CPU/sec
2 threads:  6 sec run time, 12 CPU/sec
Cost Threshold for Parallelism
  • minimum query plan threshold for considering queries for parallel execution
  • default 5:  consider increasing to 20-50 for new systems.  The number is the number of estimated seconds the query must take before considering a parallel plan. 

Limit MAXDOP to 4

Verify or limit parallelism on Xeon system with hyperthreading enabled. 

Exchange Operators

act to split the work into streams of data, marshal it to other operators, then merge the results together.  An execution plan that uses parallelism is stored in the plan cache twice, one version that does not use parallelism and one that does.  When the query is executed again the number of threads used previously is examined.  The query engine determines if it can use the same number of threads and then chooses the plan to use. 

Parallelism Operator

--This operator performs the distribute streams, gather streams, repartition streams

--PARTITION COLUMNS:() predicate contains a CSV list of columns being partitioned. 

--ORDER BY:() predicate lists the columns for which the sort order is preserved during partitioning. 

--Performance Ramifications

     --Always need to test using various MAXDOP settings

     --Every environment needs to be tested

     --In general, parallelism should not be used in OLTP applications

     --In general, parallelism should be used in DSS applications

Some SQL items appear to always force serial plans:


dynamic cursors

CLR UDFs with data access

Simulating an SMP System

If you only have a single processor test machine but want to test some of the effects of an SMP system you can use -Pn to the startup properties of the instance.  Obviously any performance numbers you get from this should be taken with a grain of salt, but it will provide you with parallel query plans to examine. 


Execution Plans Home