Hi All,
On one of our newly migrated sup-prod azure vm which is running on SQL Server 2012 EE, we are seeing high 100% CPU. On further looking at server parameters, we see that "maxdop=0" and "cost threshold for parallelism=5" which is by default. From task mgr and perfmon it is evident that sustained 94% of CPU is driven by SQL Server. The VM has 1 socket 8 logical cpu's. Also, CXPACKET is the top 2 contibutor of waits (i..e 75%). Its an OLTP application. So, we tweaked the values as follows
maxdop set to 4 and cost threshold for parallelism is set to 50.
Now question, is does it apply for ongoing processes /currently running spids?
or these settings will be available for newly spawned threads?
Reason for asking this question is, even after making this changes, still we are seeing sustained high cpu with little difference of 1 or 2 percent.
Regards,
Sam
April 7, 2020 at 9:17 am
This was removed by the editor as SPAM
April 7, 2020 at 9:24 am
new queries only - so if you are seeing high cpu you will need to use a few dmv's to figure out which queries are taking that cpu
new queries only - so if you are seeing high cpu you will need to use a few dmv's to figure out which queries are taking that cpu
We captured those queries Fred. However, I want to know the effect is immediate or for fresh queries. Thank you very much for clarification.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply