max degree of parallelism

Question

Post reply

max degree of parallelism

yujinagata72

Ten Centuries

Points: 1072
More actions
July 12, 2016 at 12:17 am

#327610

Hi,
I would like to know how to get max degree of parallelism via SQL statement?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

BLOB EATER SSChampion Points: 14609 More actions · Answer 1

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

WHERE UnitPrice < 4

GROUP BY ProductID, OrderQty

ORDER BY ProductID, OrderQty

OPTION (MAXDOP 2);

yujinagata72 Ten Centuries Points: 1072 More actions · Answer 2

I mean how I can query the SQL Server settings via SQL statement, e.g. maximum degree of parallelism?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Use sp_configure. I think there's also a catalog view, called something like sys.configurations, that you can query directly.

John

BLOB EATER SSChampion Points: 14609 More actions · Answer 4

what john said

https://msdn.microsoft.com/en-GB/library/ms189094.aspx

Michael L John One Orange Chip Points: 27043 More actions · Answer 5

My question is why? What are you looking for with maxdop?

In addition to looking at max degree of parallelism, you should also look at "cost threshold for parallelism".

Far too often, the maxdop is changed to correct a perceived problem, when it's actually the cost threshold that needs to be changed.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

You can also look at the query plan and see how many threads are running from the outputs of parallel operations.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Alan Burstein SSC Guru Points: 61136 More actions · Answer 7

To get the current max degree of parallelism for a specific instance of SQL Server:

SELECT value_in_use

FROM sys.configurations

WHERE name = 'max degree of parallelism'

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001