patternsqlMinor
SQL Server Windows NT 64 bit Sudden High CPU Spike
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
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
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
-
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.
-
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.