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 (https://blog.sqlauthority.com)
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)
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
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)
If you have SQL Server Express, then it is limited to ONE CPU, no matter what core you have!
Actually SQL Express is limited to 1 physical socket and all of the cores available on that die. You can get parallelism in SQL Express without having to do any tricks if it is running on a dual, quad, hex, or octa core CPU based server. This is documented in the following KB Article https://support.microsoft.com/en-us/help/914278).
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
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.
“Feodor’s second question asks if we can simulate Parallelism in a single-core machine.”
Use OPTION(MAXDOP 1)
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
Use OPTION(MAXDOP n) , where n is the number of processors you want to tune against
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.
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.
Hi Pinal, the query doesn’t use parallelism, the max degree of parallelism is 0.