question

Heisenberg avatar image
0 Votes"
Heisenberg asked OlafHelper-2800 commented

cpu usage spikes high when an active process runs for a long time.

If a sql server session running for long time (active) state. can it cause a CPU spike. There is a process in our database that reads transactions from the transaction log using fn_dblog and transfer those changes to mysql database tables. Sometimes process runs for long time 2-3 hours and at that time CPU utilization hits very high. so may question is if a active process runs for a long time can it cause cpu spike as compared to one running for 5-10 mins. Another point to note that we are using resource governor so that any sessions from this particular user should not be using more than 20% cpu.

sql-server-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sometimes process runs for long time 2-3 hours and at that time CPU utilization hits very high

We don't know what the process do in detail and how your database design look like, so it's not possible to even guess if/why it may cause high CPU utilization.
You have to provide more information.



0 Votes 0 ·

1 Answer

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered

Hi SQLServerBro,

Could you please check if there is blocking and check waits? Long-running queries may be waiting for resources or operations to complete.
You can use the Activity Monitor to check the blocking and waits when the process is active.
Please refer to this article about capturing the queries causing high CPU which might be helpful.

Best Regards,
Amelia


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.