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.

SQL SERVER - Parallelism Query in Database Parallelism

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.

SQL SERVER - Parallelism Query in Database maxdopcheck

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 (https://blog.sqlauthority.com)

Previous Post
SQLAuthority News – SQL Data Camp, Chennai, July 17, 2010 – A Huge Success
Next Post
SQLAuthority News – Microsoft SQL Server 2008 R2 Report Builder 3.0

Related Posts

13 Comments. Leave new

  • 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

    https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188603(v=sql.105)

    Reply
  • 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

    Reply
  • Nakul Vachhrajani
    August 6, 2010 12:14 pm

    Hello!

    Yes, simulating parallelism on a single-core machine is possible via the use of a startup parameter ).aspx). This was as a question on SQLServerCental way back (May 04, 2007)

    Reply
  • If you have SQL Server Express, then it is limited to ONE CPU, no matter what core you have!

    Reply
  • Feodor Georgiev
    August 6, 2010 1:24 pm

    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

    Reply
  • 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.

    Reply
  • “Feodor’s second question asks if we can simulate Parallelism in a single-core machine.”
    Use OPTION(MAXDOP 1)

    Reply
  • Benjamin Nevarez
    August 7, 2010 2:48 am

    Regarding simulating parallel plans you can use the –P switch as described here

    https://docs.microsoft.com/en-us/previous-versions/cc825369(v=msdn.10)

    But use it carefully as it is an undocumented feature and at least I have not tested myself in a really long time.

    Regards,

    Benjamin

    Reply
  • Use OPTION(MAXDOP n) , where n is the number of processors you want to tune against

    Reply
  • Rodrigo Amaral
    April 3, 2013 3:50 am

    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.

    Reply
  • On SQL Server 2016 Express, the given query does not appear to be going parallel. I checked the execution plan and hovering over the SELECT reveals ‘Degree of Parallelism: 0’, and of course, no parallelism figures.
    MAXDOP is set to 0.

    Reply
  • Hi Pinal, the query doesn’t use parallelism, the max degree of parallelism is 0.

    Reply

Leave a Reply

Menu