x

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

Hi

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!!?
more ▼

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

Funkoid gravatar image

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)
10|1200 characters needed characters left

5 answers: sort voted first

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.
more ▼

answered Mar 31 '11 at 04:48 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Mar 31 '11 at 05:28 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Mar 31 '11 at 05:44 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Mar 31 '11 at 05:00 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.6k 19 21 74

(comments are locked)
10|1200 characters needed characters left

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?
more ▼

answered Apr 01 '11 at 05:12 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.8k 28 30 35

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x12

asked: Mar 31 '11 at 04:36 AM

Seen: 2082 times

Last Updated: May 19 '11 at 12:51 AM