Who is overriding MAXDOP 1 on the instance?

In Monday’s newsletter I discussed an email question I’d been sent and promised to blog about it.

The question was “how can latch waits for ACCESS_METHODS_DATASET_PARENT occur when the instance MAXDOP is set to 1?” This shouldn’t happen, as that latch is only used for coordination between threads performing a parallel table/index scan. However, just because the instance MAXDOP is set to 1, that doesn’t mean no parallelism can occur.

Anyone can override the instance MAXDOP setting using a query hint.

There are two ways to prevent this:

  • Set the instance cost threshold for parallelism setting to be extremely high, so no serial plans can be deemed expensive enough to run in parallel
  • Make everyone run within a Resource Governor (Enterprise Edition only) workload group that has the MAX_DOP set to 1

Or you can figure out where the query hint is being used and remove it.

In this post I’m going to show you a simple Extended Events session that will capture information about what’s causing CXPACKET waits (you can’t have ACCESS_METHOD_DATASET_PARENT latch waits without CXPACKET waits happening too) and then refine it to use a less expensive event.

First off I’ll set my MAXDOP to 1:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'max degree of parallelism', 1;
RECONFIGURE WITH OVERRIDE;
GO

SELECT [name], [value_in_use] FROM sys.configurations WHERE [name] LIKE '%degree%';
GO
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'max degree of parallelism' changed from 0 to 1. Run the RECONFIGURE statement to install.
name                       value_in_use
-------------------------- -------------
max degree of parallelism  1

Then I’ll check for CXPACKET waits (using my waits query) after running the following query, that scans a 6.7 million row table (you can get the SalesDB database from here):

SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000;
GO

No CXPACKET waits.

But if I add a MAXDOP query hint and then check for waits:

SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8);
GO
WaitType            Wait_S  Resource_S Signal_S WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
------------------- ------- ---------- -------- --------- ---------- --------- -------- -------- ---------------------------------------------
LATCH_EX            0.20    0.16       0.05     93        79.61      0.0022    0.0017   0.0005   https://www.sqlskills.com/help/waits/LATCH_EX
CXPACKET            0.05    0.05       0.00     16        20.00      0.0032    0.0032   0.0000   https://www.sqlskills.com/help/waits/CXPACKET

And the instance MAXDOP was successfully overridden.

Now I’ll set up a simple Extended Events session to track down the offending code (based on the query from here). It’s very important that you query the sys.dm_xe_map_values DMV to find the correct number to use in the query for the CXPACKET wait, as these numbers often change from release to release, and even in Service Packs. For instance, CXPACKET was 191 in SQL Server 2014 RTM, but is 190 in the 2014 build I’m using.

Be very careful about running this in production, as the event will fire for *every* wait that occurs and so will likely affect your workload throughput, even though it’ll short-circuit if the wait isn’t CXPACKET. I’ll show you a better event to use lower down.

IF EXISTS (
    SELECT * FROM sys.server_event_sessions
        WHERE [name] = N'InvestigateWaits')
    DROP EVENT SESSION [InvestigateWaits] ON SERVER
GO

CREATE EVENT SESSION [InvestigateWaits] ON SERVER
ADD EVENT [sqlos].[wait_info]
(
    ACTION (
        sqlserver.client_hostname,
        sqlserver.nt_username,
        sqlserver.sql_text)
    WHERE [wait_type] = 190 -- CXPACKET only
    AND [opcode] = 1 -- Just the end wait events
)
ADD TARGET [package0].[ring_buffer]
WITH
(
    MAX_MEMORY = 50 MB,
    MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

ALTER EVENT SESSION [InvestigateWaits] ON SERVER STATE = START;
GO

And then when I run the select statement again I can look in the ring buffer and see the events. I put in a DISTINCT to minimize the number of lines of output. The code is:

SELECT 
    DISTINCT ([data1].[value] ('(./@timestamp)[1]', 'datetime')) AS [Time],
    [data1].[value] ('(./action[@name="client_hostname"]/value)[1]', 'VARCHAR(MAX)') AS [Host],
    [data1].[value] ('(./action[@name="nt_username"]/value)[1]', 'VARCHAR(MAX)') AS [User],
    [data1].[value] ('(./action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') AS [Statement]
FROM (
    SELECT CONVERT (XML, [target_data]) AS data
    FROM sys.dm_xe_session_targets [xst]
    INNER JOIN sys.dm_xe_sessions [xs]
        ON [xst].[event_session_address] = [xs].[address]
    WHERE [xs].[name] = N'InvestigateWaits') AS t
CROSS APPLY data.nodes('//event') n (data1);
GO
Time                    Host       User            Statement
----------------------- ---------- --------------- ---------------------------------------------------------------------------------
2017-02-16 00:20:16.937 APPLECROSS APPLECROSS\Paul SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8);
2017-02-16 00:20:16.987 APPLECROSS APPLECROSS\Paul SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8);
2017-02-16 00:20:16.937 APPLECROSS                 SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8);

Cool – so that works, but the session will likely affect workload performance, as I mentioned above. A better event to use is degree_of_parallelism, which was introduced in SQL Server 2012 and only fires once per batch execution, rather than once for every wait that occurs.

The updated event session is:

CREATE EVENT SESSION [InvestigateWaits] ON SERVER
ADD EVENT [sqlserver].[degree_of_parallelism]
(
    ACTION (
        sqlserver.client_hostname,
        sqlserver.nt_username,
        sqlserver.sql_text)
    WHERE [dop] > 1 -- parallel plans
)
ADD TARGET [package0].[ring_buffer]
WITH
(
    MAX_MEMORY = 50 MB,
    MAX_DISPATCH_LATENCY = 5 SECONDS)
GO

And the code to parse the XML, and sample output from my query is:

SELECT 
    [data1].[value] ('(./@timestamp)[1]', 'datetime') AS [Time],
    [data1].[value] ('(./data[@name="dop"]/value)[1]', 'INT') AS [DOP],
    [data1].[value] ('(./action[@name="client_hostname"]/value)[1]', 'VARCHAR(MAX)') AS [Host],
    [data1].[value] ('(./action[@name="nt_username"]/value)[1]', 'VARCHAR(MAX)') AS [User],
    [data1].[value] ('(./action[@name="sql_text"]/value)[1]','VARCHAR(MAX)') AS [Statement]
FROM (
    SELECT CONVERT (XML, [target_data]) AS data
    FROM sys.dm_xe_session_targets [xst]
    INNER JOIN sys.dm_xe_sessions [xs]
        ON [xst].[event_session_address] = [xs].[address]
    WHERE [xs].[name] = N'InvestigateWaits') AS t
CROSS APPLY data.nodes('//event') n (data1);
GO
Time                    DOP Host       User            Statement
----------------------- --- ---------- --------------- ---------------------------------------------------------------------------------
2017-02-16 00:36:37.347 8   APPLECROSS APPLECROSS\Paul SELECT * FROM [SalesDB].[dbo].[Sales] WHERE [Quantity] > 10000 OPTION (MAXDOP 8);

Pretty cool, eh?

You can mess around with more complex code that does things like grab the tsql_stack action and then parses it out, and more information to identify the culprit, like the application name – however complex you want to get – but now you know the base event session to capture when the query hint is being used.

Enjoy!

4 thoughts on “Who is overriding MAXDOP 1 on the instance?

  1. Nice Paul!

    Is it possible has occurrence of cxpacket when either my maxdop =1, threshold is very high,without hints and no resource governor running ? I’m facing such of situation !

    Thank you.

  2. Hi Paul,

    was been wondering this for quiet sometime, finally have time to try and as my thought, its not working as per BOL stated (will honour the max_dop in workload group) and based on your statement

    Anyone can override the instance MAXDOP setting using a query hint.

    Set the instance cost threshold for parallelism setting to be extremely high, so no serial plans can be deemed expensive enough to run in parallel
    Make everyone run within a Resource Governor (Enterprise Edition only) workload group that has the MAX_DOP set to 1

    create the workload group with max_dop = 1 (all_wg)
    create FUNCTION dbo.all_wg() RETURNS sysname
    WITH SCHEMABINDING
    AS
    BEGIN
    DECLARE @workload_group_name AS sysname
    IF (SUSER_NAME() = ‘test_user’)
    SET @workload_group_name = ‘all_wg’
    RETURN @workload_group_name
    END
    go
    ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.all_wg);
    go
    ALTER RESOURCE GOVERNOR RECONFIGURE;
    go

    — login with test_user

    — check the group_id from sys.dm_exec_sessions for test_user session

    — load dummy data
    select * into #res from sys.objects
    while @@rowcount < 100000
    begin
    insert into #res
    select * from #res
    end

    –query hint with maxdop 4, execute with actual plan from ssms, parallelism still everywhere
    select * from #res where name like '%da%' or name like '%c%'
    union
    select * from #res where name like '%ba%' or name like '%e%'
    option(maxdop 4)

    I would like to fail/overwrite any request with maxdop query hint, no parallelism allow, we have a server that host multiple small user databases , some developer trying to use parallelism, and for the time being I need to capture manually from dmv.

    I was looking for possibility using any traceflag, found no luck.

    Appreciate for any suggestions or comments.

    cheers!

    1. It still generates plans with parallel operators, but if you look at the operator properties, you’ll see the number of threads used is limited to 1. The optimizer doesn’t know about the run-time MAX_DOP limit.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Some thoughts on courage

(This is also the Ponderings – editorial – in today’s SQLskills newsletter.) I want to start out this post by sincerely thanking everyone who emailed

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.