SQL SERVER – Parallelism Query in Database

I recently came across two interesting questions asked by Feodor over here. He has asked very interesting questions. Please check them as follows:

If I have a dual core computer and I would like to get a query executed with parallelism in order to test it, how would I do that? You can use the AdventureWorks database and let me know if you can get a query to execute in parallel.

I am running machine which has 2 different cores. I was able to reproduce the parallel query using following T-SQL Script.

USE AdventureWorks
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID

When I ran the above query I was able to reproduce the parallel query plan for the same. Following is the image of the same.

Parallelism SQL SERVER   Parallelism Query in Database

I would like to get your input if you get a parallel plan too when you run above query. If you do not get one, do let me know but before you post here, check that your “max degree of parallelism” is set to 0 or more than 1. You can check your “max degree of parallelism” by running the following query.

SELECT name, value
FROM sys.configurations
WHERE name = 'max degree of parallelism'

Here is the output of the same query.

maxdopcheck SQL SERVER   Parallelism Query in Database

Additionally, Feodor’s second question asks if we can simulate Parallelism in a single-core machine. If you know the answer, please feel free to comment on this blog post.

Reference : Pinal Dave (http://blog.SQLAuthority.com)