RE: Deducing from Wait Stats – SQLServerCentral

SSCertifiable

Points: 6345

August 15, 2017 at 4:39 am

#1955263

John Mitchell-245523 - Monday, August 14, 2017 9:17 AM
CXPACKET just means you have parallelism. It doesn't on its own mean you have a problem with parallelism. Please don't blindly reduce parallelism just for the sake of it. How many processors do you have and what are your max degree of parallelism and cost threshold for parallelism? Are you seeing any memory pressure? Do you have anything going on that involves lots of data churn - batch processes or index maintenance, for example?
I recommend you have a look at Paul Randal's posts on wait types if you're new to this stuff.
John

Current MAXDOP is the default and the cost threshold for parallelism is also the default at 5 (considering increasing this). Some batch process and some BI reports with each reports scanning through average 20 million rows. I just inherited this mischievous monkey. Index maintenance hardly done it seems , as I see 30 % of the tables with over 90 % fragmentation. Out of the 64 GB memory , the max is at 54 GB and stats show SQL actually used about 45 GB. Thank you.