May 29, 2013 at 4:47 pm
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
May 29, 2013 at 5:36 pm
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
May 29, 2013 at 5:41 pm
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
May 29, 2013 at 5:58 pm
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
May 29, 2013 at 6:03 pm
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
May 29, 2013 at 10:19 pm
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
May 31, 2013 at 10:04 am
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
June 4, 2013 at 5:43 am
Hi
Great Script. I was just wondering if there is a reason why the OBJName and DBName doesn't populate?
June 4, 2013 at 6:23 am
If you're looking for queries that have previously executed with parallelism, have a look here
June 4, 2013 at 9:30 am
hvermaak (6/4/2013)
HiGreat 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
June 4, 2013 at 9:30 am
SQLSACT (6/4/2013)
If you're looking for queries that have previously executed with parallelism, have a look here
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