List of queries using MAXDOP

  • Gurus, currently, in our environment we configured our sql servers for MAXDOP of 4. here is our config:

    4 CPUS - each 8 Core - 16 logical processors

    Can you please help me determine if it is possible to find which queries are utilizing MAXDOP for query processing?

    Thanks

    Jagan K

    Thanks
    Jagan K

  • This code can help you to find it within Stored Procedures

    DECLARE @Search_Text nvarchar(128) = 'MAXDOP',

    @Command1 nvarchar(2000)

    SET @Command1 =

    'IF EXISTS( SELECT 1 FROM [?].sys.sql_modules m JOIN [?].sys.procedures p ON m.object_id = p.object_id

    WHERE definition LIKE ''%' + @Search_Text + '%'')

    SELECT ''[?]'' db, p.name FROM [?].sys.sql_modules m JOIN [?].sys.procedures p ON m.object_id = p.object_id

    WHERE definition LIKE ''%' + @Search_Text + '%'';'

    EXEC sp_MSForeachdb @Command1

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hello Luis, Thank you for the reply, but you misstook me for finding if there is a query hint in the sql proc Vs SQL optimizer determining if it has to do parallel processing to serve the request.

    I'm looking to see if there is any counter or any way I can find list of queries that were using parallel execution. I'm on SQL 2008R2 db version.

    Thanks
    Jagan K

  • This is a rough query of what you will need

    DECLARE @SetOptions TABLE (

    OptionDescVARCHAR(64)

    ,OptionValueINT PRIMARY KEY CLUSTERED

    ,OptionNotesVARCHAR(256))

    -- http://technet.microsoft.com/en-us/library/ms189472(v=sql.105).aspx

    INSERT INTO @SetOptions(OptionDesc,OptionValue,OptionNotes)

    VALUES ('ANSI_PADDING',1,'')

    ,('Parallel Plan',2,'')

    ,('FORCEPLAN',4,'')

    ,('CONCAT_NULL_YIELDS_NULL',8,'')

    ,('ANSI_WARNINGS',16,'')

    ,('ANSI_NULLS',32,'')

    ,('QUOTED_IDENTIFIER',64,'')

    ,('ANSI_NULL_DFLT_ON',128,'')

    ,('ANSI_NULL_DFLT_OFF',256,'')

    ,('NoBrowseTable',512,'Indicates that the plan does not use a work table to implement a FOR BROWSE operation.')

    ,('TriggerOneRow',1024,'Indicates that the plan contains single row optimization for AFTER trigger delta tables.')

    ,('ResyncQuery',2048,'Indicates that the query was submitted by internal system stored procedures.')

    ,('ARITH_ABORT',4096,'')

    ,('NUMERIC_ROUNDABORT',8192,'')

    ,('DATEFIRST',16384,'')

    ,('DATEFORMAT',32768,'')

    ,('LanguageID',65536,'')

    ,('UPON',131072,'Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled.')

    SELECT cp.size_in_bytes,cp.plan_handle,so.OptionDesc,pa.value,st.Query,OBJECT_NAME(qp.objectid) AS ObjName

    ,qp.query_plan,DB_NAME(qp.dbid) AS DBName

    FROM sys.dm_exec_cached_plans cp

    CROSS APPLY sys.dm_exec_plan_attributes (cp.plan_handle) pa

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    CROSS APPLY @SetOptions so

    CROSS APPLY (

    SELECT

    REPLACE

    (

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(

    CONVERT

    (

    NVARCHAR(MAX),

    N'--' + NCHAR(13) + NCHAR(10) + ist.text + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2

    )

    ,NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?')

    ,NCHAR(27),N'?'),NCHAR(26),N'?'),NCHAR(25),N'?'),NCHAR(24),N'?')

    ,NCHAR(23),N'?'),NCHAR(22),N'?'),NCHAR(21),N'?'),NCHAR(20),N'?')

    ,NCHAR(19),N'?'),NCHAR(18),N'?'),NCHAR(17),N'?'),NCHAR(16),N'?')

    ,NCHAR(15),N'?'),NCHAR(14),N'?'),NCHAR(12),N'?'),NCHAR(11),N'?')

    ,NCHAR(8),N'?'),NCHAR(7),N'?'),NCHAR(6),N'?'),NCHAR(5),N'?')

    ,NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?')

    ,NCHAR(0),N'') AS [processing-instruction(query)]

    FROM sys.dm_exec_sql_text(cp.plan_handle) AS ist

    FOR XML

    PATH(''),

    TYPE

    ) AS st(Query)

    WHERE CONVERT(INT,pa.value) & so.OptionValue = 2

    AND pa.attribute = 'set_options'

    ORDER BY cp.plan_handle DESC

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I will clean that code up a bit and blog about it in the near future.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Jason, Thank you very much for the quick post. I was staring at plan attributes this whole time without knowing its advantage. :Wow:

    I started anayzing the SQLs from the result set. However, I observed that the queryplan xml that it is generating is not having the whole thing. I'm playing with your SQL here and will let keep you posted if I massage the sql.

    Please dont forget to update this thread once you blog this concept.:-)

    Thanks
    Jagan K

  • jvkondapalli (5/29/2013)


    Jason, Thank you very much for the quick post. I was staring at plan attributes this whole time without knowing its advantage. :Wow:

    I started anayzing the SQLs from the result set. However, I observed that the queryplan xml that it is generating is not having the whole thing. I'm playing with your SQL here and will let keep you posted if I massage the sql.

    Please dont forget to update this thread once you blog this concept.:-)

    The query plan that this script outputs is the entire plan that is cached on your server. Notice that it is pulling it from

    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hi

    Great Script. I was just wondering if there is a reason why the OBJName and DBName doesn't populate?

  • If you're looking for queries that have previously executed with parallelism, have a look here

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx

  • hvermaak (6/4/2013)


    Hi

    Great Script. I was just wondering if there is a reason why the OBJName and DBName doesn't populate?

    That is a good question. I have seen this for a few queries in the past. When I compared them to the plan in cache I noticed that the same attributes were not populated there either. I haven't dived deep enough to find why yet. In my case, most of the time they were adhoc queries. I will try and find out why.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLSACT (6/4/2013)


    If you're looking for queries that have previously executed with parallelism, have a look here

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx

    Thanks for the info.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 1 through 10 (of 10 total)

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