# How to curb aggressive parallelism in Microsoft SQL Server

Microsoft SQL Server, like most modern database systems, can convert a query into a set of parallel instructions to improve efficiency. This is **map-reduce** before **map-reduce** was a popular programming paradigm (think Hadoop). This is done by the optimizer based on numerous information points that the system has access to at runtime. 99% of the time, this is perfectly fine, but when you find that 1% it can be very tricky to solve. In this article, I will show you one technique that I use to solve these edge cases.

This is the query I am working with:

```sql
select s.SalesRepName,
       m.material as ISBN
  from MaterialSalesRepMap m
  join SalesHierarchy s on (get_bit(EligibilityKey,Position)=1
       and SalesRepName in ('Smith,Joe','Doe,John','Doe,Jane','Lowry,Amanda'))
```

The optimizer chose to parallelize this and we can see it here

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1694017173106/7bd324a9-a327-4c82-ac9c-26de0be18b30.png align="center")

One thing to note is that when you make the collection parallel (distribute streams) you then need to funnel those threads back into a controlling thread to output (gather streams).

Statistics (pay attention to the highlighted portion):

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1694015857556/0b3f9264-feb9-4acd-ad2e-f8a78920519f.png align="center")

It turns out that 2nd **worktable** grows/shrinks based on the number of reps.

```sql
select s.SalesRepName,
       m.material as ISBN
  from MaterialSalesRepMap m
  join SalesHierarchy s on (get_bit(EligibilityKey,Position)=1
       and SalesRepName in ('Smith,Joe','Doe,John','Doe,Jane'))
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1694016480254/b03736a1-45ee-4eba-81fb-77310fe5ad85.png align="center")

MaterialSalesRepMap

* Scan count: NumReps+1
    
* logical reads: 301586\*NumReps
    

The worktable grows by millions for each additional rep. We have 3,998 'rep' entries! It was a this point that I remembered SQL Server (from 2018+) can be 'aggressive' in its choice of how many workers to use when it chooses parallelism.

There is an optimizer hint (**maxdop N)** which stands for **M**ax**D**egreeOf**P**arallelism. This directs the optimizer to use *N* number of threads when determining how many to use. Rather than guess what would be an optimal number for a query with a varying number of reps in the request, I wanted to see how efficient the system would be without it. So I turned off parallelism by saying **(maxdop 1)**.

```sql
select s.SalesRepName,
       m.material as ISBN
  from MaterialSalesRepMap m
  join SalesHierarchy s on (get_bit(EligibilityKey,Position)=1
       and SalesRepName in ('Smith,Joe','Doe,John','Doe,Jane','Lowry,Amanda'))
option (maxdop 1) 
```

As you can see this forced the optimizer to ignore any type of parallel processing.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1694017787341/25bc1352-b716-4848-b1ad-464158071003.png align="center")

The results were fantastic (and consistent)

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1694018207362/a09f2ccf-9435-4982-bcee-c398539e30bd.png align="center")

The only thing that changes is the underlined blue portion. The **Scan Count** is equal to the number of reps and the **logical reads** fluctuates slightly up or down accordingly.

If you are looking to squeeze out some additional performance or look for consistent results this would be one of those specialized tuning approaches that you can take. I have used this approach successfully in selects, inserts, and deletes.