March 29, 2022 at 7:41 pm
I have store proc runs every 30 mins during business hours on the secondary replica which is in asynchronous mode. In the morning it runs longer than normal run time. I agree it has overnight changes but not a lot. I do notice the correlation of CPU usage is high during the high run time and couldn't see any other query that is taking the resources for this to run longer. Any thoughts?
March 30, 2022 at 1:39 pm
Capture wait statistics for the individual query. Also, get the execution plan when it's slow and when it's fast and compare the two.
"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
March 30, 2022 at 4:06 pm
I am seeing the CXCONSUMER and CXPACKET wait types along with High CPU.
March 30, 2022 at 4:17 pm
I am seeing the CXCONSUMER and CXPACKET wait types along with High CPU.
You are seeing these waits for the individual query? Or is this for the instance?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 30, 2022 at 4:20 pm
Would changing the query to run with option recompile would help? I have dropped the MAXDOP would increasing that would help?
March 30, 2022 at 4:23 pm
For the individual query.
March 30, 2022 at 4:33 pm
Would changing the query to run with option recompile would help? I have dropped the MAXDOP would increasing that would help?
You're guessing and hoping for a magic bullet.
How many CPU's are in this server, and what is MAXDOP set to? By changing these values, you are changing the entire server and everything that runs on it.
How have you determined that these 2 wait types are actually bad? You can change maxdop for the individual query in the OPTIONS clause. Have you tried that with various settings?
What makes you think that OPTION RECOMPILE will help? Have you identified parameter sniffing as the culprit? If so, can rewriting the query into a few different queries change that?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 30, 2022 at 4:41 pm
Literally no magic switch or setting that's going to improve performance most of the time, like Michael says.
In addition to his questions, what is your cost threshold for parallelism. Adjusting that is frequently a better choice than messing with MAXDOP.
"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
March 30, 2022 at 4:51 pm
Thanks. Changing the setting is relatively easy and does not require a server restart to take effect. I agree like will cause data or procs to leave their caches so things could be a slight bit slower for a short while after this. Currently I have 12 CPU's and Maxdop is set to 4 so I was thinking of changing from 4 to 8. Since it's a secondary server read only replica not sure if there is any issue other than slowness.
But I like the you're thought Michael try with individual query first for Maxdop. Currently the cost threshold of parallelism is set to 5 which is very low does that need to be changed?
March 30, 2022 at 5:03 pm
Thanks. Changing the setting is relatively easy and does not require a server restart to take effect. I agree like will cause data or procs to leave their caches so things could be a slight bit slower for a short while after this. Currently I have 12 CPU's and Maxdop is set to 4 so I was thinking of changing from 4 to 8. Since it's a secondary server read only replica not sure if there is any issue other than slowness.
But I like the you're thought Michael try with individual query first for Maxdop. Currently the cost threshold of parallelism is set to 5 which is very low does that need to be changed?
5 is the default, and a google search will provide a lot of different articles on it. I usually start at 50 and go up or down from there. As an example, the servers doing the OLTP transactions are typically higher, and the servers doing a reporting or more of an OLAP type of load is usually lower.
Check out this link, and the embedded links in the article:
https://www.sqlskills.com/blogs/jonathan/tuning-cost-threshold-for-parallelism-from-the-plan-cache/
Is it possible to post the actual execution plan?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 30, 2022 at 5:52 pm
How about estimate? Do you think the plan should get from actual server?
March 30, 2022 at 5:54 pm
Agreed. 5 is too low. You can query the plans in cache to get a sense of the costs. I have a blog post on it.
"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
March 30, 2022 at 7:21 pm
Does update statistics help on secondary replica?
March 30, 2022 at 7:31 pm
Does update statistics help on secondary replica?
You have an availability group, and the secondary replica is read-only. Statistics cannot be updated on a secondary replica.
And your symptoms do not point to stale statistics
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 30, 2022 at 7:37 pm
Actually we have a database which is not part of availability group and it's reading the data from secondary replica and inserting into the database which is not a part of AG group. Based on execution plan the Est cost I noticed is when it is inserting rows into that database. How can you improve the performance?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply