What's the cause of my frequent 50% CPU usage in 1 of 3 instances of SQL 2000

Every so often one of my instances of SQL Server 2000 frequently keeps using 45-50% CPU (Every 2-3 secs.)
After a reboot things go back to normal for a period of weeks of months, then starts again.

Windows 2000 Server SP4 (DHCP,DNS, SQL,File, Print)
SQL Server 2000 SP4 x 2 instance , MSDE x 1
XEON 2.4
4 GB RAM (Avg MB available = 775MB)

As far as I can tell from Performance Monitor, the machine is handling the workload easily.
SQL Profiler also doesn't report any reason for the frequent peaks. (That I can tell)
SEM doesn't show any significant locking. (As far as I know)

In the past I have been able to link this problem to NTDBSMGE.EXE from Pervasive.
Pervasive is still installed on the machine, but has no activity and no CPU usage.

Has any one got any idea's about this?
How can I tell which instance is the problem instance and how can I find and resolve the issue?
Who is Participating?
DennisPostAuthor Commented:
Unfortunately the problem still pops up from time to time.
I personally suspect our in house software as the cause.
you can use performance monitor to monitor some sql server counters for any instance you want. you can check for full scans, disk usage, and others.

also, you can check activity monitor from management studio, which gives you cpu usage for the active processes.
DennisPostAuthor Commented:
Hi MrRobot,

Thanks for your comment.

I know the theory behind Performace monitor, just have little experience with the practical.
For starters, how do I know which sqlserver.exe goes with which instance of sql server.
If I look at the counters under Process I see sqlserver#1, sqlserver#2, sqlserver#3

Once I have that I could look at Process / %CPU for the instance, but that will only confirm what I can see in task manager i.e. it uses CPU in fits and bouts.

In short, I need instructions on how to find out which instance of SQL is giving me the trouble and how
I can find out what is causing the issue. Then I might need help resolving the issue.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Hi Dennis,

open the task manager with the PIDs being shown.
go to a command prompt and type "tasklist /svc". from the list, you can find out which PID is attached to which instance.
MrRobot's tip will identify the instance.  If you then want to identify the operation within that instance and can catch it while it is happening use the sqlhogs script in this:

DennisPostAuthor Commented:

Is tasklist /svc also fpr Windows 2000 Server?
It isn't recognised.
How else can I find out which one is the naughty instance?

I ran the script against each the Master db of the instances. One (MSDE) didn't return any results.
The other 2 returned  one line with CPUPercent = 0.
I ran your script when CPU% was >50.

I also tried the old version, again it is only seeing the execution in QA and nothing more. Maybe I am in the wrong instance???


If you see an instance of sql server in task mgr using cpu and the script gave you nothing back, I think that would mean the wrong instance.  You can't just run it against all instances?  Without tasklist you could see if sc is available to correlate the instance with a pid.  I think "sc queryex" would show you services and their pid's.
Here's what you can do to find out the cause of the frequent CPU usage.

Download process explorer from www.sysinternals.com

run process explorer and doubleclick on that instance of sql server which is consuming most of the cpu.
this will show you the thread related information.
now double click on the thread(s) which is consuming most of the cpu.
this will show you the stack of that particular thread.
by looking at the stack, you can determine the modules and the functions being called which cause the high cpu utilization.
you could then do an online search to see if there are any updates for that particular module/component.
In order to find out the same using perfmon, you can do the following:

1. Go to Start > Programs > Administrative Tools > Performance.
2. In the Performance Window under Performance Logs and Alerts option right click on Counter Logs option
3. Click on New Log settings option and assign a name to the New Log.
4. Click on Ok button and in the next window click on Add Counters window
5. From the Performance Object select the following options:
6. Please make sure that all counters and All Instances option are selected in the same window.
7. Click OK and set the sample interval to 5 seconds.
8. Then select the tab Log Files in the main window. In the logfile type select as Binary Circular File and click on Configure Button to specify the location of the Log file. Also you can specify the limit of the log file to 200 MB in the same window. Click OK again and in the main window select the tab Schedule the option Start log and Stop Log should be selected as Manually. Click on Apply and OK to exit from the main Window.
9. In the right pane of Perfmon Window you will see the new log file which has been created. Right click on that and click on Start option. Please allow Perfmon to run till the issue occurs.
10. Once it is observed that the issue has started to occur, lets leave Perfmon running for 10 minutes so that it can gather snapshots of all the selected counter objects.
11. After 10 minutes, please right click on the log file name and click on stop and upload it to any FTP site for analysis across after compressing it and give us the link so that we can analyse it.
DennisPostAuthor Commented:
Hi, just got back in the office.....

I did run the script against each of the instances.
sc queryex is not recognized.

I downloaded Process Explorer and now Microsoft Debugging Tools For Windows for the DbgHelp.dll.
It tells me it is needed when I click on the threads tab.
On the services tab I can see which instance I am looking at.
Looks like I might try some of those other tools as well soon. Thanks for the tip!

I have also setup the performance couter logs as per your specification. Alas, I do not have access to any ftp site. Could you maybe recommend any?

BUT....... Automatic Updates updated my server last night and rebooted. Now the problem has gone and I have no strange issues to monitor anymore. Users happy, me disgruntled.
I'm sure this problem will popup again in future as it seems to do periodically.
If I close this question now and add comments to it later (When the problem starts again) will you
guys get an email about the new post?

damn i'm disappointed. all these inputs from all us experts and the problem goes away after automatic updates and a server reboot? :(
DennisPostAuthor Commented:
tasklist /svc = tlist -s

Windows 2000 support tools
DennisPostAuthor Commented:
It's happening again guys. :-(    :-)
Users are breating down my neck again so time is short before i HAVE to restart to temporarly resolve this.

tlist - s revealed that it's the local instance that is causing the issue.

Task manage stats:
CPU Time : 10:35
Mem. usage : 798656K
Page Faults : 703252
VM Size : 878104K
Handles : 715
Threads : 48
I/O Reads : 17341100952
I/O Writes : 6597142203

I'll run my troubleshooting Profiler template for an hour and get back to you.
DennisPostAuthor Commented:
I ran both your scripts multiple times against each database.
The most lines returned were 3 and the highest CPU% was 4.7 all the rest of the CPU% were under 4.
These results seem very acceptable to me, right?
DennisPostAuthor Commented:
Performance monitor stats. (Only for the troublesome instance.)

%CPU Time : Avg. 88
%Priveledge : Avg. 82
%User time : Avg 2.5

Instance is using both Xeon cores.
Both boost SQL priority on windows & use windows NT fibres are NOT selected.

Could this issue be related to me using remote desktop and running queries?
Re-reading everything I need to ask something basic: When you're looking at high cpu in task manager have you looked at the process list and verified it is one of your instances of sqlservr.exe that is causing the high cpu?  I'm not clear whether your cpu numbers posted are total usage or process usage.  I was pretty confident if it is sqlservr.exe with the high usage my script would show you the sql causing it.
DennisPostAuthor Commented:
>>When you're looking at high cpu in task manager have you looked at the process list and verified it is one of your instances of sqlservr.exe that is causing the high cpu?
Yes that's right.

My previous post was only for the instance of SQL that is giving me troubles.

Performance monitor has been running all day. Here are the latest averages.
% CPU = 130
% User Time = 99
% Priviledge Time = 42

I have also been running Profiler for most of the day and only just stopped and save the results to a table on the same instance as the one that's giving me hassles. (Local Instance)
... oh and I'am also running the Index Tuning wizard at the moment. (Both your scripts show Index Tuning Wizard running  > 100% CPU.

DennisPostAuthor Commented:
I look at this article ( http://support.microsoft.com/kb/314530 ) to help troubleshoot at SP time error
i'm getting.
No additional information was provided with DBCC TRACEON (7300, 3604).

DBCC SQLPERF(WAITSTATS) returns: (Shortend version, removed all 0 rows )
Wait Type                          Requests      Wait Time      Signal Wait Time
MISCELLANEOUS      1.0      0.0      0.0
LCK_M_S                            537.0      12762.0      230.0
LCK_M_U                            23.0      9796.0      15.0
LCK_M_X                            490.0      2374.0      141.0
LCK_M_IS                            2.0      31.0      0.0
LCK_M_IX                            4.0      79.0      0.0
SLEEP                           769417.0      7.6852006E+8      7.610743E+8
IO_COMPLETION      7662.0      52707.0      108.0
ASYNC_IO_COMPLETION      1272.0      126243.0      0.0
OLEDB                           8681987.0      7.9850118E+8      3.9679987E+9
RESOURCE_QUEUE      536188.0      2.2438339E+9      7.4988486E+8
ASYNC_DISKPOOL_LOCK17505.0      218.0      0.0
PIPELINE_INDEX_STAT      1.0      0.0      0.0
WRITELOG                           194037.0      2950177.0      28557.0
LOGBUFFER                           73.0      892.0      0.0
CMEMTHREAD      429.0      125.0      0.0
CXPACKET                           33668.0      141220.0      11338.0
PAGESUPP                           10.0      0.0      0.0
WAITFOR                           44.0      44514.0      44514.0
LATCH_SH                           3.0      63.0      0.0
LATCH_EX                           850.0      404.0      309.0
PAGELATCH_SH      38043.0      1186.0      1092.0
PAGELATCH_UP      15556.0      2393.0      2192.0
PAGELATCH_EX      18881.0      1531.0      1422.0
PAGEIOLATCH_SH      6896.0      59743.0      127.0
PAGEIOLATCH_UP      7452.0      124414.0      156.0
PAGEIOLATCH_EX      3793.0      70841.0      15.0
NETWORKIO                           717393.0      300259.0      0.0
Total                           1.1052217E+7      3.8147571E+9      5.4790482E+9

SPID 69 Has 2 TAB locks:
master.dbo.spt_values          TAB            Sch-S              GRANT                           Xact
tempdb.dbo.##lockinfo69       TAB            X                      GRANT                           Xact
Last TSQL command batch run : dbcc inputbuffer(69)

In SEM Process info I see:
PID                           69
User                         Me !!
DB                            Master
Status                      Runnable
Open Transactions  2
Command                 SELECT INTO
Application               MS SQLEM
Waiting type             Not waiting
CPU                          935


PID                           52
User                         NT System
DB                            msdb
Status                      Sleeping
Open Transactions  0
Command                 AWAITING COMMAND
Application               SQLAgent - Alert Engine
Waiting type             Not waiting
CPU                          567719 (565 000 higher than any other)
DennisPostAuthor Commented:

We only have 10-15 users working on this server at any one time.

My own (Amature) VB applicaton also runs on this instance. ADO connections are made and closed 1000+ times a day by each user.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Is parallel query execution disabled on the (3) instances? if not, disable that.
What are the MAX MEMORY settings for the 3 instances? the total should not be over 3.7GB allowed.

is the time when this happens regular (at the same time)?
 if yes, can you check if anything is scheduled / running at "exactly" that time?
DennisPostAuthor Commented:
>> Is parallel query execution disabled on the (3) instances? if not, disable that.
Not sure, where can I find this setting?

The combined max memory of all instances did indeed exceed 3.7GB. Task manager reported 450MB available. I lowered the max on each instance and now have 950MB available.

I have multiple scheduled backup tasks the run troughout the day. The CPU hogging also occurs between these jobs.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>> Is parallel query execution disabled on the (3) instances? if not, disable that.
>Not sure, where can I find this setting?
in the server (instance) properties, you have a processor tab. there you see a "parallelism" panel, which by default, has "use all available processors".
change that to "use 1 processor"

that will make sure that 1 single query will never be executed using several processors at once.
at least, that is the setting I saw most effective with most OLTP databases, as setting thresholds or leaving the "use all available processors" made very often even small queries terribly slow by the overhead of make the query run multi-processor.

don't get the setting understood wrong: running several queries will still be spread over the several (virtual) CPU on your machine!
DennisPostAuthor Commented:
Okay. I have changed the settings on all 2 instances to only use CPU 1 and not CPU 0.
Only time will tell if this helps resolve this is permanently as it only occurs some weeks after rebooting.

Is there any possiblity that this is linked to me using RDP & SEM remotely?
Do I need to worry about those TAB locks or the 2 open transactions I mentioned earlier?

btw, thanks for taking a look at this question. Very much appreciated !! :-)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Okay. I have changed the settings on all 2 instances to only use CPU 1 and not CPU 0.
that's actually the wrong setting :-(
note also that these settings only get effective after restarting the instance(s).
DennisPostAuthor Commented:
I looked again, this time with my eyes open. Found it!

I'll restart now. This would temporaraliy resolve the problem anyway, lets hope it resolves it permanently this time.

DennisPostAuthor Commented:
Things are looking good, but I'll hold off awarding points for a while longer.
DennisPostAuthor Commented:
Problem's back :-(

I have no new information. Everything looks the same as last time. Grrrr
I think go back to where we left off (at the time the script accurately told you it was the index tuning wizard using the cpu).  So, start with looking at task manager and verify that it is one of your sql server instances using most of the cpu.  Run that script on that instance and see what it says.
DennisPostAuthor Commented:

I just ran both your scripts 20 times each. the highest cpu % was 3.2.
I ran the script mostly against a user database that is used more than any other database.
Hey Dennis,

Are you watching sql server counters on perfmon? They should give you an idea about what's happening.

Have you checked sql server activity monitor from management studio? It lists every process running in your instance with their resource usage.

Also, in the profiler try to include every counter, there should be an indication. If not, I would pass sql side, and consantrate on the server environment; hardware, or more likely software. There could be something wrong with the libraries installed. And since this problem lasts for a long time, I would consider re-installing the server.
DennisPostAuthor Commented:
Hi MrRobot,

How are ya?

Yes I am watching the sql server counters on prefmon. I can see that the CPU Priveledge % grows with each spike. I just don't know why and how I can find out.

I would like to try taking some of the other User DBs off line. How can I kick everyone off a DB so that I can take it off line?

I am already doing a lot of overtime to get other things done, so I would really, really perfer not to re-install SQL Server.
Thanks Dennis,

You can use
to rollback any incomplete transactions and kick the other users out.

And you can try
to put the db offline immediately.

So on perfmon, don't you notice a growth in the lock / full scan counts?
What about server activity monitor and sql server profiler?

DennisPostAuthor Commented:

Ok I'm running System Monitor at the moment. Looking at locking.
Lock request / sec are related to the cpu spikes but
Lock Timeout / sec,  Lock Wait time, Lock Waits /sec & number of deadlocks are all 0.

As far as full scans are concerned, no correlation to the CPU spikes (Almost no scans happening at all). Index Searches / sec  on the other hand are happening on each CPU spike.
Probe & range scans are at a bare minimum.

If I am interpreting this data correctly, the I either have too many indexes, bad indexes or fragemented indexes. All of these I find strange as I have run the Index tuning wizard 2-3 times this year.

I just run DBCC SHOWCONTIG example E. from BOL
E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
This example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold. (30.0)

Now Index Seaches / sec are continuously high. Avg. 265 / sec, Max 4025 /sec. :-(

DennisPostAuthor Commented:

I had to restart the service to resolve this temporarily again.
I left perfmon running with the same tokens.
Index Searches still peak regulary, but no loss of performance.
I no longer think Index Searches have anything to do with this issue.

Any idea's?
It's unlikely index searches. More likely index searches are just a by product of the operation which requests locks. High lock requests can really really decrease the performance. The first reason I asked to check for it is that there can be a client issue which makes the client run a query which affects a big resultset, or improperly use cursor / lock types. Eg, using a non forward only / read only cursor can easily bring a server down on very frequent operations with a big result set. Once there was an issue with our client software's name lookup filter, which made a request for nearly every keypress when looking for a customer name, and did it using unnecessarily locked requests. This made a huge number of lock requests from time to time, which brought the server down on its knees.

Whether be it the client or the server side, this shows the problem is sql related and not a system / server issue. So what about profiler / activity monitor?

This can also be a component from an auditing / logging tool, like an extended sp or something, which does some operation time to time, and the exe file doesn't have to be using some noticable cpu time for this to happen. I would check the developer's faqs to be sure it's completely removed from the system with all its components.
DennisPostAuthor Commented:

Profiler hasn't revealed much. But I addmit, I'm only only a novice in using it. I have created some traces based on what I have found on the net. Interpreting is a another skill all together.  From what I have seen there are some sp's that are taking a long time (High reads & duration) but only when things are running slow, otherwise things look okay. Maybe you have some tips for me?

As far as I know activity monitor is a tool in SQL 2005 and doesn't exist in SQL 200. Please corect me if I'm wrong.

I will open a support case with McAfee. Who knows, maybe that's causing the trouble. ProtectionPilot does run a lot of auditing & logging sps. These are the ones I was refering to above.
DennisPostAuthor Commented:
These MS articles explain situations that are similar to what I'm experiencing.

Unforunately their resolution is to install the latest service pack, which already is.
well, you may need to play more on the profiler. eg, you can trace locks:acquired, locks:escalation events during spikes, group the events on different columns, like DB ID, login name, object id, client process id, application name to see who or what causes those spikes, as you will get the number of events in each group. you can also save the results in an sql table and analyze profiler events using tsql.

you can install sql 2005 client tools and use them to connect to sql 2000. as the data structure used by the activity monitor exists on sql2000, you can use activity monitor on sql server 2000.

also try sql batch completed events.
once you know which dbid makes the trouble, you can filter your events using "filter columns" to focus on that db.
ahh.. btw, it can be cached execution plans of stored procedures and indexes. I should've added this.

you said some procs takes long time to complete when the system slows down. use profiler to monitor read/write io access for those procs. if you see a considerable increase in io access on those SPs, you got it.

sp execution plans are calculated using the parameters given to the sp the first time it runs. so a sp can use different indexes different times. like, for the parameters given the first time it runs, it may produce an execution plan which will utilize an index for a table, but if the nature of the parameters change in a later call which makes the index usage impossible, the procedure may need huge table scans.

just keep an eye on the avarage io access for those procs on profiler.
you can try to use
sp_recompile <proc name>

during the spikes for each sp that slows down, and see if it will have any effect.

DennisPostAuthor Commented:
I'm running profiler at the moment. I'll use the trace as a baseline.
I had to appease the natives by restarting the service recently, or else they would have linched me.

Everything is running fine at the moment. When things slow down again i'll run the traces as you suggested.
In the meantime I'll download activity monitor and experiment a bit.

Thanks for the help!
DennisPostAuthor Commented:
about that Acivity Monitor.
Googling gave me a lot of "Big Brother" programs.

From this MS site:

I downloaded this:

Please let me know if this is the one you were refering to.
activity monitor is built into "management studio", the replacement for "query analyzer" and "enterprise manager". you can try "management studio express", which is free, or client tools from the trial version of "sql server 2005", if you don't have "sql server 2005". btw, when the trial time is over, client tools probably won't expire.
DennisPostAuthor Commented:

This issue has come back a few times since I last posted.
Just now I tried to see what was happening in ProcessMonitor.
I got the dreaded Blue Screen of Death upon doubleclicking the processmonitor exe. FLTMGR.SYS being the culprit.

I will contiue to monitor all events from PID 1388 (Local SQL instance) with a duration > 1.
Because I just had to restart, I'll have to wait for the issue to raise its head again.

My applogies from the consistantly slow replies. Even though this is becomming a major problem, it is still not in my top 5 priorities. My boss keeps me occupied with all sorts of other things he deems to be more important......
Please bare with me, I still need your wisdom, guidance and especially your patience.
DennisPostAuthor Commented:
It didn't take longer to raise its head. :-(

SEM Locks revealed:
Object     LockType     Mode      Status      Owner     Index
TestDB    DB                S            Granted   Sess        
MutDet     TAB              IX           Granted   Sess
MutDet     Page             IX           Granted   Sess       PK_MutDet_7e6cc920
MutDet     Key               X           Granted   Sess       PK_MutDet_7e6cc920

The testdb is a restored (renamed) backup from the actual DB on the same instance.
Even though, I killed the application on my client machine, these still show up in SEM on the server, but not in SEM on the client.

........ Just got interupte by the big boss himself........ I now have other priorities than server crashes and poor performance...... :-( grrrrr.
I'll still be listening though.
DennisPostAuthor Commented:
How big an impact on performace should sp_setapprole have?

I am monitoring profiler duration > 50. 99% displayed is sp_setapprole, duration 150 - 210, cpu = 0.
Is this normal? Could it have something to do with the cpu usage?

My application ADO connection uses an application role.  the ADO Connection is closed after every tsql.
This means that sp_setapprole is used tens of thousand times a day.
Is this normal practice, or far from best practice?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.