I have a very strange problem we're seeing on SQL Server 2005 x64 SP4 running on Windows Server 2003 x64 SP2.
The CPU is spiking to 50% every 8 seconds with sqlservr.exe being the process using the CPU (diagnosed via sysinternals tools). All none essential SQL Server services (agent, ssis etc) are stopped and no external services or connections are being made to the server yet it still continues to spike every 8 seconds. If the service is restarted the issue stops until the memory consumption of the process hits between 700mb-800mb, then it starts again.
A couple of profiler samples have shown that a number of Scan:Started events run when the CPU spike is recorded, querying the object id of 28 in the master database suggests that sys.sysdbreg is getting queried every 8 seconds in line with the CPU spike.
use Adam Machanic's - whoisactive - http://sqlblog.com/blogs/adam_machanic/archive/2009/03/30/who-is-active-v8-40-now-with-delta-power.aspx to locate the process and take your investigation further.
Do you have a lot of databases on the server? Is there adequate RAM?
[Edit - after your comment]
[Edit - after more comment]
I would say monitoring is in play here as well.
From BOL we find that:
So it appears some process is interrogating your instance to get an up to date listing of the databases it contains. Though every 8 seconds is excessive IMHO.
answered Mar 31, 2011 at 05:28 AM
OK. How about this.
Do you have a server-side trace running? If not, set one up and capture all the queries called. I see you mention profiler but it doesn't look like you were capturing procedure and batch calls.
answered Mar 31, 2011 at 05:44 AM
Grant Fritchey ♦♦
Do you have a monitoring service running against the system? The regularity of the spikes is certainly indicative of an automated process.
Is SQL Server sharing the machine with other applications? Do you have anti-virus software running on the server?
These are the areas I would start looking in.
answered Mar 31, 2011 at 05:00 AM
Grant Fritchey ♦♦
Are you excluding your data and log files from your anti-virus software?
How much RAM is on your system?
What are your MIN & MAX memory settings?
Is AWE enabled?
How many cores/CPUs?
During the spikes do you see high disk latency?
Is this a physical server or a VM?
Are any databases set to auto-close?
answered Apr 01, 2011 at 05:12 AM