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
GO
SELECT *
FROM Sales.SalesOrderDetail sod
INNER JOIN Production.Product p ON sod.ProductID = p.ProductID
ORDER BY Style
GO

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.

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'
GO

Here is the output of the same query.

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)

12 thoughts on “SQL SERVER – Parallelism Query in Database

  1. Use the option ‘cost threshold for parallelism’. It adjust the cost threshold which will trigger a parallel query. You still need to write a query that will exceed the cost.

    But setting it to 0 will cause the following query to utilize parallelism…

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

    http://msdn.microsoft.com/en-us/library/ms188603.aspx

    Like

  2. In response to Feodor’s second question…

    SQL Server’s capability of using Multiple CPU’s simultaneously to execute the Query Plan refered as Parallel Query Execution. DOP is configuration that instruct SQL Server about How many core CPU’s it can utilise for Parallel Query Execution.

    If server has only one core, Query Plan can not be executed parallel.

    Regards,
    Sajid

    Like

  3. Thank you for the kind words. For the simulation of multiple CPUs I know that there is a -Px switch, where x represents the desired number of CPUs.

    When it comes to the query you wrote above, I am very curious about the logic you used to produce it: how did you write the query and how did you know that it will execute in parallel? I am interested in this logic, so I can apply it later on in my performance tuning techniques.

    Thank you.
    Feodor

    Like

  4. Hello Pinal
    My m/c configuration is as follows:
    Computer:
    Pentium(R) Dual-Core CPU
    E5200 @ 2.50GHZ

    But the query does not use a parellal plan in my case. I have verified that the max degree of parallelism is set to 0.

    Like

  5. In my Server the configuration max degree of parallelism i set to 0, but in my Execution plan I can’t see the parallelism figure.

    Like

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #041 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s