CPU spike & sys.sysdbreg being scanned every 8 seconds?

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.

Any ideas!!?

asked Mar 31 '11 at 04:36 AM in Default

Funkoid
11 ● 1 ● 1 ● 1

I should have said in my original post all antivirus software has been disabled as have all monitoring services. It's a dedicated SQL Server with plenty of RAM and there is only one datbaase on the server plus the system databases.

I've run Who Is Active a good few times now and it doesn't capture any activity - yet its the sqlservr.exe process that is causing the peak which has been recorded in process monitor every 8 seconds.

Mar 31 '11 at 05:28 AM Funkoid

Well this gets stranger and stranger - having run a server side trace no activity was detected, yet the spikes still occur!

Apr 01 '11 at 05:03 AM Funkoid

The system has a total of 6 GB of memory with the min setting being 0 and max 4096, AWE is not enabled, the server is a two core virtual machine and there is no high disk latency when the spike occurs.

Apr 01 '11 at 05:53 AM Funkoid

I don't know if its of any significance but the issue starts after the service is started and memory consumption hits 788.88mb then continues until the service is stopped.

Apr 01 '11 at 06:02 AM Funkoid

(comments are locked)

5 answers:

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]
How about removing the NIC to prove its something either from the network or something on the box?
What other processes in Task Manager are running in descending CPU activity?

[Edit - after more comment]
Do you have any manufacturers server management tools running? I have seen some of our Dell boxes get bogged down with some utilities that are pre-installed. If you are still struggling then I would suggest getting a trial version of SQLMonitor from RedGate and letting that show up whats going on. If you want to see how it works and what it does take a look here: http://monitor.red-gate.com other wise get the download from here https://www.red-gate.com/products/dba/sql-monitor/ - its 14 days for fully functioning trial.

answered Mar 31 '11 at 04:48 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

(comments are locked)

I would say monitoring is in play here as well.

From BOL we find that:

sys.sysdbreg - Exists in the master database only. Contains a row for each registered database.

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 '11 at 05:28 AM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

(comments are locked)

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 '11 at 05:44 AM