I have a Windows Server 2008 R2 virtual machine in Amazon EC2, running SQL Server 2008 R2. Every three hours, like clockwork, the server's CPU usage will spike to 100% for about 10 minutes or so, to the point that applications attempting to connect to this SQL instance will receive timeout errors on the pre-login handshake. When the server is restarted, the exact times that this occurs change, but it is always every three hours, so I suspect it is caused by something scheduled to run at start-up and then recur every three hours.
I have been using Resource Monitor to try to locate the problem, and I have seen that the CPU usage during these times is typically split relatively evenly (fluctuating mostly in the 40s and 50s) between the sqlservr.exe and svchost.exe (netsvcs) processes. The services breakdown in resource monitor then typically shows the total service usage with a little more fluctuation, but always high (80s and 90s), split between MSSQLSERVER and one other service. However, what the other service is seems to vary every time, making it very difficult to pinpoint the issue. Services that I have seen running alongside MSSQLSERVER during these periods include:
- BFE - Base Filtering Engine
- Xensvc - Citrix tools for Virtual Machines service
- RpcEptMapper - RPC Endpoint Mapper
- DHCP - DHCP Client
- Spooler - Print Spooler
- PC Monitor - Pulseway
- wuauserv - Windows Update
- TermService - Remote Desktop Services
- Ec2Config - Ec2Config
- DcomLaunch - DCOM Server Process Launcher
- A few of our in-house services
With such a diverse list of services, I'm not really sure where to start other than SQL Server, so I've tried using some queries with system views to find high CPU queries around these times, but those that get currently running queries will block until after the spike ends, while historical data doesn't seem to show anything significant. I also tried running a trace in Profiler, but the only queries with any significant duration or CPU use in these periods are part of a process that runs every 4 minutes throughout the entire day. While this process does often spike the CPU, outside of these periods it is only for a few seconds, typically 5 or less, and it does not process any more data or do anything more intensive at these times, so I am doubtful that this is the root of the problem.
The only events I'm seeing in the Windows event log around these times worthy of any note or with any consistency are timeout errors from attempting to access the SQL instance. SQL Server's error logs also don't show anything noteworthy or consistent around these times. No agent jobs or windows scheduled tasks run on a schedule consistent with the problem.
Has anyone else seen a problem similar to this? What else can I check or do to troubleshoot this issue? Thank you in advance!