Parallelism Overview
Parallelism Overview
By : Kasim Wirama, MCDBA
When you observe execution plan for some query that processes many rows, you will find operator with small arrow at graphical execution plan. For example, you have Index Scan operator with double small arrow icon at the icon. It shows that the operator has been parallelized in its scanning. Parallelism would potentially happen in hyperthreading, multi core, or multi processor environment.
Parallelism usually is used to process query with many rows (bigger query) with same amount of time as smaller query does. Though the response time is maintained short with parallelism, but it gives overhead to CPU, thus it is not preferable in high concurrent OLTP system because it will lower down database throughput.
In lower level, optimizer will partition input data into required and available CPU to output the result as quick as possible. Each CPU gives one thread and each thread will execute same aggregate function, join, etc . They executes independently among another.
Optimizer choose between serial and parallel plan based on cheaper cost. Here are configuration settings that acts as cost guideline for optimizer :
1. Affinity Mask setting that allows optimizer to use multi processor. By default the value is 0, it means optimizer can use all available processor.
2. Max degree of parallelism setting. By default the value is zero or more than one, it means optimizer can generate parallelism. If the value is one, only serial plan could be generated by optimizer.
3. Cost threshold for parallelism setting. The higher the value, the less likely optimizer will choose parallel plan if the serial plan has cost less than cost threshold.
You can find these settings by running this SQL command at SQL Server Management Studio :
EXEC sp_configure ‘show advanced options’, 1;
RECONFIGURE;