June 18, 2018 at 4:55 am
Hi All
One of our critical database servers is facing high CPU utilization during a particular time every weekend . I try to capture few sql statements running hourly over weekend and couldn't find anything exceptional yet . High cpu cost is coming from a query which is running 300+ times every min but this sp is usually running quite fast and is constantly running throughout the day . i saw no database maint jobs running during same time when cpu is spiking up . considering this system very critical what is the best way to go ahead to drill down what is causing high cpu and in result affecting this SP performance . can it be because of SP taking bad execution plan although i doubt that . Any extended event which can capture this info with minimal load on this critical machine or anything alternative way to go ahead would be appreciated . Thanks in advance
June 18, 2018 at 5:45 am
muzikfreakster - Monday, June 18, 2018 4:55 AMHi All
One of our critical database servers is facing high CPU utilization during a particular time every weekend . I try to capture few sql statements running hourly over weekend and couldn't find anything exceptional yet . High cpu cost is coming from a query which is running 300+ times every min but this sp is usually running quite fast and is constantly running throughout the day . i saw no database maint jobs running during same time when cpu is spiking up . considering this system very critical what is the best way to go ahead to drill down what is causing high cpu and in result affecting this SP performance . can it be because of SP taking bad execution plan although i doubt that . Any extended event which can capture this info with minimal load on this critical machine or anything alternative way to go ahead would be appreciated . Thanks in advance
The first and most important thing is to find and list everything running on the server during the high CPU spikes, both within the SQL Server and the OS.
😎
I recommend also capturing the wait stats delta for the period, often gives a good hint.
June 18, 2018 at 6:36 am
70% of the time wait type is dbmirror_dbm_mutex and this is an undocumented wait type . should i be worried? i am using sql 2012
June 18, 2018 at 6:53 am
muzikfreakster - Monday, June 18, 2018 6:36 AM70% of the time wait type is dbmirror_dbm_mutex and this is an undocumented wait type . should i be worried? i am using sql 2012
Is the instance mirrored?
😎
If it is, what is happening on the mirror at the time of the CPU spikes?
June 18, 2018 at 9:11 am
You have a stored procedure that runs 300 times per minute? Unless that stored procedure is responding to a GUI for individual customer requests, I'm thinking that's actually a serious RBAR problem that needs to be addressed sooner than later.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 18, 2018 at 12:51 pm
muzikfreakster - Monday, June 18, 2018 6:36 AM70% of the time wait type is dbmirror_dbm_mutex and this is an undocumented wait type . should i be worried? i am using sql 2012
There are a few bits of information on that wait type - and with everyone saying who knows if you should be worried. But worth a read just to better understand the wait type:
DBMIRROR_DBM_MUTEX: The world of Redo Operations
Should you ignore DBMIRROR_DBM_MUTEX?
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply