February 19, 2020 at 3:31 pm
We are experiencing memory issue in one of the server where we observed LATCH_EX is being caused by ACCESS_METHODS_DATASET_PARENT latch type and also CX wait type . I checked the maxdop and cost of threshold parallesim values and are configured as default values .
Will increase those values will solve the issue if not how to solve the issue
Thanks
February 19, 2020 at 4:07 pm
We are experiencing memory issue in one of the server where we observed LATCH_EX is being caused by ACCESS_METHODS_DATASET_PARENT latch type and also CX wait type . I checked the maxdop and cost of threshold parallesim values and are configured as default values .
Will increase those values will solve the issue if not how to solve the issue
The default values for those typically need to be changed. Whether it solves your issues or not depends on what you see with testing the changes. It may, it may not. You need to test the settings, changes you make as there is no one size fits all. The issues you are having could be related to other things as well such as bad statistics, mixed workloads, etc. The following articles are good to read about these settings:
MAXDOP of Confusion (Dear SQL DBA Episode 8)
Why You Should Change the Cost Threshold for Parallelism
Why Cost Threshold For Parallelism Shouldn’t Be Set To 5
Sue
February 20, 2020 at 12:35 pm
And, to determine what value to set your Threshold to, I'd take this approach or something like it.
However, that's for parallelism, which is much more often seen a choke for CPU, not memory. I'd suggest using some of the techniques here to see if your system is under stress, and what is the cause. SQL Server will always use all the memory you give it. People frequently see this as an issue and assume that it's the source of any performance woes. However, it's normal behavior. So, the way to track down what's happening is to look beyond the simple measure of memory as outlined in the article.
Also, most problems are code or structure related. I strongly recommend capturing query performance metrics as the best way to understand performance issues. Extended Events or Query Store are the two best methods.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply