Our production server is running SQL Server 2008 SP3.
I’ve read a post on “cost threshold for parallelism” - http://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/ And I have a few questions.
We have a relatively high amount of CXPACKET wait events on our production server (in average of 85%). The server has default values for “cost threshold for parallelism” and “Max Degree of Parallelism”
I started to explore the possibility of increasing the value of “cost threshold for parallelism”. I suspect the indeed the value is too low, but I wanted to get as much information as possible before I change anything and not just change it to 25 or 50 because that’s what the some of the internet posts suggested.
I’ve read this nice post about how to determine the value: http://sqlknowitall.com/determining-a-setting-for-cost-threshold-for-parallelism/
The median cost in my server was 63.
How can I monitor my server’s status “before” and “after” I change to settings, in order to verify the effect of the value on the system’s throughput?
Thanks in advance, Roni.