Internal of Parallelism
By : Kasim Wirama, MCDBA
If you notice in execution plan, probably you would find out parallelism in some operators of an execution plan. Let’s look inside parallelism behaviour in general.
Parallelism of an operator is made up at least 2 threads. For example here, there is 2 threads, thread 1 and thread 2. Thread 1 itself is made up from thread producer and thread consumer, so does thread 2. Data from producer is pushed into consumer, so it provides load balancing when some consumers runs slowly, so producer can choose available producers to process next data. The way data exchange between consumers and producers is called partitioning.
So I can say that parallelism has a set of consumer and also a set of producer. Based on number of consumer and producer, there are 3 kinds of parallelisms:
- gather streams parallelism
It consists of a number of threads at producer and only one consumer thread.
- repartition streams parallelism
It consists of a number of threads at producer and a number of threads at consumer.
- distribute streams parallelism
It consists of a number of threads at consumer and only one producer thread.
Data is partitioned during parallelism process taking place, but it is valid only for repartition and distribute streams kind of parallelism. There are 4 types of way how parallelism partitions data into multiple consumers:
- broadcast partitioning
This type of partitioning is called when producer send all data to all consumers.
- hash partitioning
This type of partitioning is called when determining hash value for row that would be sent to particular consumers.
- round robin partitioning
This type will send data to consumer in sequence manner.
- demand partitioning
This type is pull type partitioning because producers pull data from consumers. It happens on distributed partitioned id on partitioned table.
- range partitioning
This type will partition data by evaluating value of a column of a row with range function to determine which producer will receive the data.
Regarding to parallelism, one of issue that might happen is parallel deadlock. Potential query that has parallel deadlock is on order-preserving query. Example of order preserving query is ORDER BY, or if you check of text based execution plan, you will find out parallelism operator along with its attribute like this : “Parallelism(Gather Stream, ORDER BY:<some table>.<one or more column>)”. Words that is marked as bold, it is the property of parallelism that is order-preserving. You can check whether parallel deadlock might exist at your SQL Server by querying sys.dm_os_waiting_tasks (dynamic management view), notice at wait_type for same session. If you find entry CXPACKETS, it gives you indication that parallel deadlock exists at your SQL Server. Solution for removing CXPACKETS is to reduce max DOP.
Final thing that parallelism might happen is about the number of rows in a table. When a table size is huge, for example containing 300,000 rows, parallelism will more potentially happen compared with table with size 10,000 rows.