CODEWITHSUNDEEP
sql-server
JohnnyM
JohnnyM

Reputation: 29566

How do I determine the server's MAXDOP setting in SQL Server?

I see lots of sites that show how to set it, but I just want to see what it is first. What is the command to do this?

Upvotes: 27

Views: 54498

Answers (4)

Mike
Mike

Reputation: 1876

While MAXDOP is usually set at the Instance level, it can be overridden at the Database level or the Query level. This query will capture both the Instance setting, and any Database level settings that override it.

SELECT  name, value_in_use
FROM    master.sys.configurations
WHERE   description LIKE '%max%%parallelism%'

DECLARE @command varchar(max) = 'USE ? SELECT ''?'', [value] FROM sys.database_scoped_configurations WHERE [name] = ''MAXDOP'';'

DECLARE @results TABLE
(
  Database_Name sysname,
  MaxDop sql_variant
)

INSERT  INTO @results
EXEC sp_MSforeachdb @command

SELECT * FROM @results WHERE MaxDop <> 0

Upvotes: 1

Town
Town

Reputation: 14906

For Azure SQL:

SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = 'MAXDOP';

https://learn.microsoft.com/en-us/azure/azure-sql/database/configure-max-degree-of-parallelism

Upvotes: 3

faygate
faygate

Reputation: 541

More simply if you wish to use a pure SQL script you can use the following which will give you the values for both 'cost threshold for parallelism' and 'max degree of parallelism' or many other things if you remove the WHERE clause ;)

Edited the following to limit to one row

SELECT
    name,
    value_in_use
FROM
    sys.configurations
WHERE
    description LIKE '%max%%parallelism%'

Upvotes: 44

Preet Sangha
Preet Sangha

Reputation: 65496

In Script:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'max degree of parallelism'
GO

Upvotes: 25

Related Questions