Category: parallelism

Understanding parallelism on SQL Server

Parallelism refers to multiple processors cooperating to execute a single query at the same time.

Parallel execution involves the overhead of synchronizing and monitoring the tasks. So, that’s why parallel plans are considered expensive operations.

The query optimizer determines whether a parallel plan should be used based on the configuration and the query cost.

  • A configuration value, max degree of parallelism determines how many CPUs can be used to execute a query. Also, using query hint can set how many CPUs can be used for that specific query.
  • Cost threshold for parallelism determines the cost that a query must meet before a parallel query plan will even be considered. Query cost is determined based on the amount of data the query optimizer estimates to be read to complete the operation.

In earlier versions of SQL Server, it was common to disable parallel queries on systems that were primarily used for transaction processing. It’s common to see MAXDOP 1 for OLTP environments. I prefer to raise the cost threshold for parallelism so a parallel plan is only considered for higher cost queries.

Let’s see an example.

I executed the query below and I got parallelism for that. My query cost was 374.148, remember this cost is always estimated.

SELECT * FROM dbo.FactInternetSales AS FIS
LEFT JOIN dbo.DimCustomer AS DC ON DC.CustomerKey = FIS.CustomerKey
LEFT JOIN dbo.DimProduct AS DP ON DP.ProductKey = FIS.ProductKey
LEFT JOIN dbo.DimProductSubcategory AS DPS ON DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
ORDER BY DP.StandardCost
OPTION(RECOMPILE)

parallel

Increasing the cost threshold for parallelism value to 400 you are not going to see any change in the query, because the real cost isn’t 374, that cost is for only 1 thread.

In my case the max degree of parallelism value is 2, so multiplying 374 x 2 = 748

sp_configure

Setting the value to 800 and run the same query again you can see no parallelism and the cost is 691.208, so it fits in the cost value.

parallel2

If your CXPACKET wait counter is high, try to increase the cost threshold for parallelism and not set MAXDOP to 1 because some queries even in a transaction database will have the benefit to run in parallel.