Monday, November 7, 2011

Simple Parallelism Testing

I recently started doing research on the CXPacket wait type and I needed a simple sql statement to test the MAX Degree of Parallelism and Cost Threshold for Parallelism settings. I wanted a simple statement that would not have alot of complexity that could be run on a simple workstation or laptop while at the same time invoking parallelization. My goal was to see if the parallelized query would run faster than the non parallelized query.

My machine configuration
Dell Precision M4500
Dual Socket Quad Core Intel(R) Core(TM) i7 CPU x 940 @ 2.13GHz
SQL Server sees 8 total CPU's
4GB of Ram
64-bit Windows 7 Professional Service Pack 1
SQL Server 2008 R2 Enterprise SP1

I borrowed the query below from Pinal Dave.

SELECT *
FROM
AdventureWorks.Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style

The query produces the following execution plan with the MAX Degree of Parallelism setting of 0 and Cost Threshold for Parallelism set to 5. This query took 5 seconds to complete, regardless of a clean cache.



My main objective is to see if I take half of my cpu's (4) and allow them to be used for parallelism, what's the hight cost threshold I can set before parallelism is not used. I found that if I set the Cost Threshold for Parallelism to 20, that is the highest threshold in which the query is considered for parallelism. When setting the threshold to 21, parallelism is not used. Look at the query plan below. Note, the query took 3 seconds to run, regardless of a clean cache.



What's really interesting, is that the query actually takes longer when the optimizer decides to use parallelism. This is a crucial point when researching CXPacket wait types.

No comments:

Post a Comment