SQL05 Standard - sqlservr 100% CPU at all times!

[Ceez]

Hello everyone.

Disclaimer - SQL n00b with capital N so I need this as lamans terms as possible.

Our accounting dept uses a software called Vision which runs on SQL. We have 2 db's; office A and office B and nothing else.

Running on Prliant380 g3 - xeon dual core 3.4 - 3.5 ram - Server Ent 2003 32bit.

There are 9 accounting people using this db, there are random employees (about 70) during the week that log into the web portion at various times to fill out their timesheet.

Now as the subject reads, sqlservr.exe gets stuck at 100%, might jump down to 90% for a split second but back to 100%. Obviously this slows down everything and accounting comes to complain. We reboot the server (sometimes twice) and it seems to clear out whatever problem it's having. We'll see the cpu at 90% but the users say that the system 'flies' so we leave it at that. There will be weeks that pass until the problem resurfaces.

What can I do?

thank you,

ceez

 

[gmmastros]

How big are the database(s)?

When this happens, it could be a blocking situation which occurs when 2 queries are trying to access the same data at the same time. The next time this happens, you should run the following in a SQL Server Management Studio query:

sp_who2

This will show you a lot of information. Of particular interest would be the column named BlkBy. It should give you a pretty clear indication of the queries that are running, and who's running them.



I also suggest you download SQLCop. This tool is free and contains several diagnostic checks that you can run. Pay particular attention to the configuration section. If SQLCop reports any problems and you don't understand it, post your question here. I wrote SQLCop so I can probably help with any questions you might have.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Ceez]

thanks for the reply. I will look into that.

I spoke with support and they ruled out their app or even sql by stopping the sql services as well as 3 other services for their software. also stopped the print spooler and kaspersky AV and kaspersky network agent.

now it seems to go down to 60% but will immediately spike to 100%. And if I click around (I am RDP-ing to the box) it will spike to 100%.

He did mention (which I missed) that the taskmgr.exe is using 50% of the cpu, that might be the culprit of this operation. Dont know where to start to fix this. this was a clean install of the OS/sql and the accntng application!

thanks

 

[intelwizrd]

Process Explorer (a sysinternals app) might give you a little more insight into the processes and the commands being run to invoke the processes (have to add columns).

Also, check the network traffic. I have had personal experience with my desktop where the CPU would run higher than any of the totals in taskmgr could account for, turns out a bunch of devices were misconfigured and sending my system SNMP traps.