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

Question

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.

Answer 1

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.