Our high usage OLTP server has MAXDOP = 0 and Cost threshold = 5. (12 cores)
Top wait is CXPacket
I am wanting start tweaking this with MAXDOP = 5 and Cost threshold = 50 (Good hardware)
Looking into how many cached plans actually use parallelism, I arrived at this query.
SELECT 'Cached_Plans' QrySrc,
OBJECT_NAME(ObjectID, DBID) ObjName,
FROM sys.dm_exec_cached_plans ECP
CROSS APPLY sys.dm_exec_text_query_plan(ECP.Plan_Handle,0,-1) EQP
INNER JOIN sys.dm_exec_procedure_stats EPS
ON EQP.[DBID] = EPS.Database_ID
AND EQP.ObjectID = EPS.[Object_ID]
WHERE DBID < 32767
AND EQP.Query_Plan LIKE '%Parallelism%' COLLATE SQL_Latin1_General_CP1_CI_AS
Is this query acturate? It only returns 2 from the 165 stored procedures on this server.
If this is accurate then that means that I have to review all the SPs ensuring nothing is forcing serial plans, as per Paul Whites (SQLKiwi) blog
It's Friday afternoon..... My excuse for the hastily written question. :-D