SQL Server 2019 - 100% CPU and high waits after job execution

Question

We have a job running on our SQL Server 2019 which is calculating some customer statistics. Every now an then, after the job finished, the CPU usage increases to the maximum and is not resolving until a failover to the secondary node is done.

The job does basically the following:

• calculates some statistics
• truncates the tables where the statistics are saved in
• fills the tables with the new statistics
• updates the staistics of those tables

The customers can see the stastics via a website which is grapping the data via a function in our SQL Server. Normally the function call has a few reads/CPU usage, but in those cases, the call of the function has more then half a million reads and a bunch of CPU usage.

It seems, that sometimes after the job has updated the user statistics, the SQL Server can not use the best query plan for the function call. The even more strange thing is, that this behaviour is appearing since a few weeks. Before, no problems occured with the job/function.

Do you have any suggestion what we could do to resolve this problem? I am also thankful if somebody would have a contact person we could ask for help!

Thanks in advance!

Answer 1

do you have any implicit conversion in your function? and what about index usage during this operation? could you share your execution plan?

Answer 2

Hi，Patrick Hackl

First, you need to pinpoint which specific query is causing the CPU spike.

You can do this by tracking the statement execution using Monitor performance by using the Query Store or SQL Profiler.

Finally, analyze the resource consumption through the execution plan.

Answer 3

Thanks for your answers. We recognized a certain drop of the PLE. We now added some memory to the server and it seems that the problem is curred now.