HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server Windows NT 64 bit Sudden High CPU Spike

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
spikesuddenbitsqlhighwindowsservercpu

Problem

I am facing a strange behavior of my SQL Server 2016 web edition. SQL Server Windows NT 64 bit suddenly jump to use 90% of the CPU and then it goes down after 5 sec, then the spike comes again after 3 to 5 mins (normal usage is just aroud 2% to 5% of cpu)

My server specs

  • Windows Server 2012 R2 Standard Evaluation (9600 build)



  • MS SQL Server 2016 Web



  • Intel Xeon E3-1245v5



  • 64GB DDR4 ECC (2400 MHz)



  • 3x480 GB SSD (Micron)



Server is up to date with latest updates installed and MS SQL Server is on default settings.
How can I find what is causing this. Please help me. Thanks

Please check the screenshots

Solution

There are a few ways to approach this.

-
Download sp_WhoIsActive and run it when you notice a CPU spike, or log it to a table and query the table looking at the CPU column (maybe ORDER BY CPU DESC).

-
Examine the plan cache using a free script -- I co-author one called sp_BlitzCache. By default, it will return the top 10 CPU consuming queries in your server's plan cache.

-
Buy a monitoring tool like SentryOne Performance Advisor and use it to examine your server during a spike.

Context

StackExchange Database Administrators Q#173519, answer score: 6

Revisions (0)

No revisions yet.