August 28, 2017 at 3:44 am
Hi,
Have some questions related to parallelism in SQL Server.
I work in datawarehouse environment. We are using SQL 2016 Enterprise Edition. I can see parallelism operators are taking more Cost. i.e 36%. And I can see the top most waittype for that query is CXPACKET with 686469 milliseconds. Looking at the parallesim I feel that the query is expensive enough and so sql server has choosen to run the query in parallel.
Questions I was asking to myself is,
1. When do we determine excessive parallelism is happening and it is bad for query and we can try reducing parallel threads? Any pointers how to determine how many parallel threads ideally we should setup.I mean how can I make decision to set max degree of parallelism ? (what value to be set for maxdop)?
Currently, In our environment there are 16 CPU's installed on box with 128 GB memory.
2. Secondly, what should be the ideal value to set for 'cost threshold for parallelism'?
3. How to tell looking at the actual execution plan, parallelism is bad for a specific query? any pointers to watch out for in the plan?
Thanks,
Sam
August 28, 2017 at 7:19 am
vsamantha35 - Monday, August 28, 2017 3:44 AMHi,Have some questions related to parallelism in SQL Server.
I work in datawarehouse environment. We are using SQL 2016 Enterprise Edition. I can see parallelism operators are taking more Cost. i.e 36%. And I can see the top most waittype for that query is CXPACKET with 686469 milliseconds. Looking at the parallesim I feel that the query is expensive enough and so sql server has choosen to run the query in parallel.
Questions I was asking to myself is,
1. When do we determine excessive parallelism is happening and it is bad for query and we can try reducing parallel threads? Any pointers how to determine how many parallel threads ideally we should setup.I mean how can I make decision to set max degree of parallelism ? (what value to be set for maxdop)?
Currently, In our environment there are 16 CPU's installed on box with 128 GB memory.
2. Secondly, what should be the ideal value to set for 'cost threshold for parallelism'?
3. How to tell looking at the actual execution plan, parallelism is bad for a specific query? any pointers to watch out for in the plan?
Thanks,
Sam
What's the NUMA configuration for your server and what's the affinity mask ?
Do you have any baseline for waits stats to compare , for your environment ?
Can follow this document from Microsoft for the MAXDOP settings recommended and one by Kendra Little and one by Shaun Stuart;
https://www.brentozar.com/archive/2013/12/q-can-high-maxdop-make-a-query-slower/
http://shaunjstuart.com/archive/2012/07/changing-sql-servers-maxdop-setting/
August 28, 2017 at 7:38 am
vsamantha35 - Monday, August 28, 2017 3:44 AMHi,Have some questions related to parallelism in SQL Server.
I work in datawarehouse environment. We are using SQL 2016 Enterprise Edition. I can see parallelism operators are taking more Cost. i.e 36%. And I can see the top most waittype for that query is CXPACKET with 686469 milliseconds. Looking at the parallesim I feel that the query is expensive enough and so sql server has choosen to run the query in parallel.
Questions I was asking to myself is,
1. When do we determine excessive parallelism is happening and it is bad for query and we can try reducing parallel threads? Any pointers how to determine how many parallel threads ideally we should setup.I mean how can I make decision to set max degree of parallelism ? (what value to be set for maxdop)?
Currently, In our environment there are 16 CPU's installed on box with 128 GB memory.
2. Secondly, what should be the ideal value to set for 'cost threshold for parallelism'?
3. How to tell looking at the actual execution plan, parallelism is bad for a specific query? any pointers to watch out for in the plan?
Thanks,
Sam
'Cost Threshold to Parallelism' default value of 5 corresponds to the machines used about 2 decades ago I suppose . Please test it with a larger value incrementing from 30 to 50 gradually.
September 1, 2017 at 7:51 am
Is this for INSERT Operations you are seeing this issue.
If so from the top of my head Bob Duffy @sqlbits gives an example of more cores for large INSERT operations with 16cores or more will be slower and suffering the kind of problems you are having.
Also he states that Read Ahead I/O Size works best @512K. Fragmentation affects the size Lower
I'll find the link and post it to you. As usual I have watched it this week and now I can't find it. Give me a while.
Very good video if you have time to watch it all
September 1, 2017 at 8:11 am
https://www.linkedin.com/in/duffybob/
Can't find it but he is on linkedin.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply