0

Setup

Microsoft SQL Server 2008 on Windows Server 2008 (Yes I know I need to upgrade). The server is accessed by a single Linux/Apache web server.

Problem

Starting about a week ago and slowly getting worse a spike in CPU on the database server has occurred every 5min (on the hour%5 minute). This went unnoticed until it started causing the web application to timeout on connections to it a few days ago. We reviewed our Datadog feed and discovered this slowly more aggressive CPU spike.

The CPU spikes are usually 5-10% CPU for about 3seconds. But occasionally jump to 100% and lock the server up completely causing the timeouts. During these 100% spikes the server is locked for about 10sec on the rdp connection and about 1/2 sec for database connections which is just long enough to cause a few ~20 timeouts before being freed up.

Windows task manager confirmed this is caused by the SQL Server Process.

My Attempts

  • The clear pattern makes me think cron job. We only have 1 cron active and it runs at the top of the hour.
  • Restarting the database and restarting the server itself changed nothing.
  • I ran sql server profiler during one of the spikes and no queries were run during the spikes that would justify them nor was their an excessive number of queries, nor was the disk activity showing an expensive query like it normally would.
  • Windows Task Scheduler has nothing at 5min increments.
  • Datadog sends every 1 minute and does not access the SQL server process directly.
  • Windows logs show no unusual activity on the 5 minute increment.
  • Auto-shrink is not turned on, nor do we have any automated SQL tasks except nightly backups. (Yes I know I need to set up index cleanup tasks but I haven't gotten around to it yet.).
  • Disk activity does not appear to be affected by the CPU spike.
  • No other users are logged into the system. I am using RDP and have nothing running except SQL Server Management Studio.
  • The server has no access outside of the network except RDP on a non-standard port.
  • Hardware host is reliable/stable and not likely to be a factor.
  • I ran sp_WhoIsActive during the spikes and nothing useful was provided. Different databases was active each time, and cpu on the database was not high enough to explain the issue. This and profiler seem to tell me this is process related not database related.

My Suspicions

I suspect SQL Server is running some kind of background defrag, optimize, index clean, auto-shrink task that is built into the server and running into problems. But I can find no documentation or actual evidence of such a background task existing nor would I know how to verify this.

It may also be possible that some other program is actually causing the issue but is not showing up in task manager, or is being errantly grouped with SQL Server. I don't know how I would identify such a thing.

I am pulling my hair out and have run out of idea on what to look for. Any thoughts are appreciated.

11
  • 1
    "This went unnoticed until it started causing the web application to timeout on connections to it a few days ago" and "I ran sql server profiler during one of the spikes and no queries were run during the spikes that would justify them" - How do you know this is definitively the cause of your web application timeouts? What is the CPU percentage usage at when it spikes? You should use something like sp_WhoIsActive when there's a spike to see everything that's actively running on the server, what resources it's consuming, and what it's waiting on.
    – J.D.
    Aug 28, 2022 at 16:11
  • @J.D. The cpu spikes were initially around 10%. They now occasionally jump to 100% locking the server up completely for about 10seconds (rdp) and causing timeouts on connections incoming for about 1/10th of a second. Timeout is 1/2 second. so that means actual server downtime is about 6/10 of a second. Aug 28, 2022 at 16:35
  • @J.D. sp_WhoIsActive did not give any useful info. Different databases active each time, none of the active suspicious or consuming enough resources to justify the spike. Of course I can only run it during the smaller spikes as during the larger spikes I lose connectivity. Aug 28, 2022 at 16:46
  • Maybe it's checkpoints? Aug 28, 2022 at 17:19
  • 1
    Probably nothing to do with SQL Server at all. Either your hard drive is showing its age, or someone has installed some malware on your system. I strongly suspect the former: a hard drive stall can easily cause the whole operating system to freeze. Check SQL Server error logs for FlushCache messages. Aug 28, 2022 at 19:19

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.