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

Patrick Hackl 0 Reputation points
2024-06-27T11:04:32.5166667+00:00

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.

sql

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!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,273 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
65 questions
{count} votes

3 answers

Sort by: Most helpful
  1. yunus emre ISIK 181 Reputation points
    2024-06-28T04:51:05.1233333+00:00

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

    0 comments No comments

  2. MikeyQiaoMSFT-0444 1,750 Reputation points
    2024-06-28T09:58:18.77+00:00

    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.

    0 comments No comments

  3. Patrick Hackl 0 Reputation points
    2024-07-02T05:07:07.9333333+00:00

    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.

    0 comments No comments