Sql Server Problem?

Printable View

April 15th, 2005, 08:23 AM
ommy

Sql Server Problem?

Hello,
There is a strange problem at the moment for a very mission critical machine (That is running Microsft SQL Server 2000 with service pack 3 and all the latest patches havebeen updated on the machine). The problem is that sqlserv.exe process is consuming upto 90% of the cpu resources. The operating system we are using is Microsoft Windows 2003 (all patches applied). I would appreciate if someone could list down the approach for tracing this problem to its roots.

According to my understanding and the approach I am following is:

1) Immidiate antiVirus Scan (Norton with all definitons updated), which showed nothing.

2) Process Explorer for each and every process description that is executing on my machine.

3) Spyware Scans which did fix nitti grittie sort of worms and spyware, but the problem remains there.

I was thinking of monitoring all the inbound and outbound traffic of that machine (MS SQL Server Machine) i.e. monitoring all the ports and the traffic on it. Can someone recommend a handy tool for this ??

The biggest problem with this machine is that it is connected to internet (only for the sakeo f patches). I am trying to convince my administration that the machine should refuse all connection from the outer world (or machine has no access to internet) and we can apply the patches manually or by means of our patch updation server.

Any ideas from the community towards troubleshooting the problem that consumes around 90% of the cpu resources?? I would appreciate if someone could recommend any tools or suggest anything else.

Thank you
Ommy
April 15th, 2005, 01:54 PM
morganlefay

Can you provide hardware details.??

SQL is cpu intensive....how many do you have?? What are they?? PI, PII, PIII etc

How much RAM?? You can improve performance with adding more RAM...can take the load off of the CPUs??

What kind of harddrives??SCSI 10,000 rpm, IDE 7200 serail ATA???

Do you have lots of room on those drives??

How many users??

How many daily transactions in the database???

Waht other services are running on the server, ISA?, Exchange?

MLF
April 15th, 2005, 07:55 PM
CXGJarrod

Have you been patching SQL Server as well as the OS? You dont have any of the default passwords running do you? (Ex. sa sa)

Sp3 (http://www.microsoft.com/sql/downloads/2000/sp3.asp)

and then downloads for after SP3 here (Select SQL Server 2000 and then Service Pack 3)
April 15th, 2005, 08:18 PM
morganlefay

Quote:

(That is running Microsft SQL Server 2000 with service pack 3 and all the latest patches havebeen updated on the machine).

Looks like he has the patches already???

MLF
April 15th, 2005, 09:48 PM
CXGJarrod

Quote:

Originally posted here by morganlefay
Looks like he has the patches already???

MLF

Morganlefay: Didnt want to assume after this quote

Quote:

The operating system we are using is Microsoft Windows 2003 (all patches applied)

No mention of SQL Patches. If he goes to Windowsupdate.com to get patches it sometimes does not pick up patches for other programs such as SQL.
April 15th, 2005, 09:52 PM
morganlefay

Yes you are right...

Never assume ;)

MLF
April 15th, 2005, 09:56 PM
thehorse13

In the task manager, add additional info fields for threads and handles (VIEW>SELECT COLUMNS). See if the process is leaking. That's the first thing I would normally do.
April 15th, 2005, 10:43 PM
Tiger Shark

Quote:

3) Spyware Scans which did fix nitti grittie sort of worms and spyware, but the problem remains there.

This entry worries the **** outta me....... Really.... :rolleyes:

You have an SQL server with "nitti grittie sort of worms and spyware" on it??????

You have a bunch of other problems, policywise and practicewise, to solve before you solve this one.....

Start from scratch then come an tell us if you have the same issues....

I have $50 says you don't...... Really....
April 16th, 2005, 12:17 PM
ommy

The machine Microsoft SQL is running has dual QUAD processors and 2 gigs of RAM. CXGJarrod and MorganLefay, I have all the patches for Win2k3 and MS SQL Server (not that big of an idiot) installed We have a SCSI hard drives with 10,000 RPM. I am on the red alert mode for any security patches for both OS and the SQL Server. Our MS SQL servers handle around 3 lakh queries per day (inert, select). But MS SQL server has been handling more load then that in a day like around 8 lack queries in a day and it didnt showed any degradation in performance.

TigerShark, yes there are a bunch of problems both practice wise and policy wise (thats what i am here for, asking for recommendations ). But you know, before drafting a policy or something, atleast the system should be operational. I know, these policies should have been formulated before implementing this system in such a mission critical environment, but lack of expertise was shown by the consultants and project managers.

I am seriously looking forward for any recommendations on the issue , i.e. sqlserv.exe problem ( chewing up CPU resources) and any new tips for formulating my approach towards troubleshooting such problems.

Because, in the end, i am here to learn :) as i have already learnt so much from this community for long time now.

TheHorse13, thanx for the advice, i ll surely do that..:)
looking forward for any further advice...
April 16th, 2005, 09:14 PM
morganlefay

Well...I think Tiger may be right. ( as always ;) )

Sounds like you have the hardware to support your SQL....although I am not sure what Dual Quads are...is that like XEON???

So...if your server has worms and spyware...I would format and reinstall if it were mine...restore data..cause I think that will clear up your issues.

Recommended by CERT...if a server has become compromised...no matter what type (UNIX, MS etc)...the only way to be sure of a stable machine...is a reinstall..specially if it is a mission critical server.

MLF
April 16th, 2005, 09:27 PM
Tiger Shark

Ok... I reread the OP and found something else that bothers the **** outta me.... Old age, what can I say.... :mad:

Quote:

I am trying to convince my administration that the machine should refuse all connection from the outer world (or machine has no access to internet) and we can apply the patches manually or by means of our patch updation server.

The implication of this statement is that the SQL server is directly connectable to from the public network..... :eek:

Is that an HTML/CGI/ASP type connection to the same server the SQL database is held or is it a DMZed server that receives the public requests and then subsequently gets the data from the SQL server? Is that SQL server also in the DMZ or is it on the private network.

The problem I see in the first post I made was that the server is obviously being used for other things too.... You don't pick up spyware without going to web sites and stuff.... that's a no-no on a production server.. especially if it is a mission critical server.

Can you bring this server down or do you have to keep it running?
April 17th, 2005, 01:37 AM
moonlight_x

Is that SQL Server meant for internal use? Complete offline reformat/ patch and restrict access is the best choice. Obviously you know that. So assuming that is live production mission critical server. Get another server and transfer the database server. If the company that you are working for serious about keeping the database and/or information inside confidential, should let you do a proper cleanup before bringing it back online.

If you have two machine running cluster, i suppose you can do it one at a time.
(Is that what you mean by dual-quad?)
April 18th, 2005, 06:09 AM
ommy

see the post underneath...sorry..:)
April 18th, 2005, 06:11 AM
ommy

TigerShark, We have already formatted the database server and it is now a fresh and patched machines ( with all the patches of SQL and OS). But the problem presists. The SQL machine does not have a direct public access. Rather we have another machine (lets name it machine A) which is directly interacting with the users (collecting some information about their visit to the site) and a module on that machine A is actually executing all the queries on the SQL machine. Machine A has also all the patches updated and apparently no process leaks (I would be needing some further advice on it, that what can be some other ways to detect a process leak). Machine A is also hooked up to the Anti Virus server and all virus definitions are updated, as soon as it arrives. Both of the machines does not need to be DMZ'ed because both of our machines are on the private network but connected to the internet for the sake of patching.

The application module on machine A is interacting to the SQL machine by means of an API. Yes, the machine was running some other services as well i.e. MS IIS and we have locked it down, since its not needed. We will make sure that no one does the web surfing on our server machines ( now drafting a policy for our IT room ).

TigerShark, we cannot afford downtime at the moment, since the machine is very critical and it should be operational/running 24/7. In your opinion, would it be a good practice if i start sniffing all the inbound and outbound traffic of my SQL machine. Recommend me your personal favourite.

Morganlefay, for knowing anything further about QUAD processors, go to the link[1] that I provided. I hope that it would give you enough information about QUAD processors. :)

Thank you everyone :)
looking for your advice....

[1] Lynxworks
April 18th, 2005, 02:16 PM
morganlefay

Ommy thanks for the link.

Learn something new every day. :)

One thing.....are those on the hardware compatibiliy list for SQL???

Cause I know MS is REALLY fussy about hardware....If this is so critical..spend the 200 usd and call MS...sometimes its worth it...

I have a machine..dual xeon, and no processor errors??...and alot of other things running on it??

How many processors show up in task manager...mine shows 4...hyper threading

MLF
April 18th, 2005, 02:27 PM
Tiger Shark

Ommy:

Silly question, but is it possible that this is simply due to the normal load on the machine?
April 18th, 2005, 03:51 PM
Panic

Perhaps you have a run-away query, or a poorly written report running against a database on the server? You can get some info in Enterprise Manager -> <server> -> Management -> Current Activity -> Process Info. Not the best resource, but I think it's pretty much the best built-in way. We had the same issue when someone wrote a report that had a REALLY ugly join. Even caused our tempdb to fill up.

Does the application you have using the database have a means for checking on it's processes and queries? Some do, such as Crystal Enterprise.
April 18th, 2005, 04:25 PM
ghostmachine

Re: Sql Server Problem?

Quote:

Originally posted here by ommy
Hello,
There is a strange problem at the moment for a very mission critical machine (That is running Microsft SQL Server 2000 with service pack 3 and all the latest patches havebeen updated on the machine). The problem is that sqlserv.exe process is consuming upto 90% of the cpu resources. The operating system we are using is Microsoft Windows 2003 (all patches applied). I would appreciate if someone could list down the approach for tracing this problem to its roots.

According to my understanding and the approach I am following is:

1) Immidiate antiVirus Scan (Norton with all definitons updated), which showed nothing.

2) Process Explorer for each and every process description that is executing on my machine.

3) Spyware Scans which did fix nitti grittie sort of worms and spyware, but the problem remains there.

I was thinking of monitoring all the inbound and outbound traffic of that machine (MS SQL Server Machine) i.e. monitoring all the ports and the traffic on it. Can someone recommend a handy tool for this ??

The biggest problem with this machine is that it is connected to internet (only for the sakeo f patches). I am trying to convince my administration that the machine should refuse all connection from the outer world (or machine has no access to internet) and we can apply the patches manually or by means of our patch updation server.

Any ideas from the community towards troubleshooting the problem that consumes around 90% of the cpu resources?? I would appreciate if someone could recommend any tools or suggest anything else.

Thank you
Ommy

I think SQL server has sp_sysmon stored proc. Try that.
I believe you have auditing enabled???
And the SQL server log?? maybe you could take a look at them and see what is happening during that time...
April 18th, 2005, 05:14 PM
GONEin62nd

Hello there!

Quote:

but is it possible that this is simply due to the normal load on the machine?

- Probably the case.

The point is it also depends on the type of process your SQL is doing.

Quote:

The problem is that sqlserv.exe process is consuming upto 90% of the cpu resources.

In our company, I remember the time when we had the same load problem in which we run MS SQL 2000 in our MS Win 2K server and it actually eats up the resources of the RAM, the box is really extremely slow and eats up the CPU process entirely. At the first place, we suspect it is memory issues and we had upgraded to 1GB of RAM, but still did not solved the problem. We used PIII boxes normally and it has no problem whatsoever until the programmers did some changes with the BACKEND SOFTWARE supporting the SQL server. We had found out that SQL is not releasing such MEMORY that it is using in some of its process. Some problem with the SQL script that was setup by the programmers and the DB admin. Some sort of dumped temporary records are increasing which should be clean-up by the program but unfortunately it stays in the DB and it made the SQL Server very busy. It might be the same case since your SQL eats up the resources.

Even so, we also run and publish reports (Crystal Report) on the web retrieved by our clients but it should not actually affect the SQL Process since the Crystal Report will only get records from the SQL DB (in our case, it has no implication on the SQL process, it could be different with others).

Hope it could relate to your situation.

-GONE
April 18th, 2005, 06:04 PM
mohaughn

There are hundreds, if not thousands of different issues that could cause any relational database to run at high CPU loads. This is really not the kind of problem that you can resolve in a forum type environment. If this machine is so critical I would recommend that your company goes out and pays an experienced DBA to take a look at the machine.

Without understanding how many databases you have in the SQL server, the number of roles, the number of jobs, triggers, actions, etc.. There is really no way to say 100% what the problem is.

You could have poorly designed tables structures or non-normalized data that can cause high CPU utilization. You could also have over normalized data that can cause high CPU utilization. Or perhaps you have so many transactions going on that this is just normal load as somebody else said. Bottom line, this isn't the place to trouble shoot this type of problem.
April 19th, 2005, 11:22 AM
ommy

Tigersgark, I dont think that this would be the case. Machine has experienced more load then that and evn at time no load, but it was fully functional. Morganlefay, there are no hardware issues, as it has been bearing more load then that.
Panic, the application ddoes not have a means for checking its own processes.Ghostmachine, yes we do have auditing enabled nd we did onitored the logs. There was nothing in it other then usual SQL events.
GONEin62nd, the scenario is very much similaar. But in our case, we do not have access to the team who actually developed those modules (vendor). I think that this might be one of the issues.
mohaughn, probablly you are right that this sort of problem cannot be resolved in a forum environment. But it helped me a great deal when it comes to exploring the possibilites and tracing a problem.

Despite, no one talked about sniffing the network traffic on the SQL machine. how about that ??
Thanks
ommy
April 19th, 2005, 12:59 PM
Tiger Shark

Quote:

Despite, no one talked about sniffing the network traffic on the SQL machine. how about that ??

You say you have, and monitor, the logs and only valid SQL activity is going on. Since it is the SQL server process running at a high level and you have reloaded the box I can't see that sniffing would do you a lot of good. But then again, since you have no other route to go down at this point it certainly wouldn't hurt if only to cancel out another possibility.
April 20th, 2005, 06:03 AM
ommy

hmm...true TigerShark..this was the only possibility left in my mind. However your argument is valid as well. TigerShark, can you recommend me your personal favourite sniffing tool for both windows and *nix platform??

Thank you everyone for your intrest in the problem. This community certainly rocks.

Ommy
April 20th, 2005, 08:43 AM
Tiger Shark

I love my Ethereal.... It googles right to the top when you search for it by name. You'll need the WinPcap drivers but the Ethereal site points you to them. Be careful you only use the version of the WinPcap drivers recommended for the version of Ethereal you d/l. There are incompatibilities between certain versions.
April 21st, 2005, 06:27 PM
Panic

I use Ethereal as well and it works great. I've only used it on Linux, so I can't vouch for the Windows version. If you want to get information about who is communicating with the box and what type of traffic there is, you could check out NTOP. NTOP doesn't, by default, dump the raw packet data, but it tracks coversations on the wire. That way, you can see actual TCP/IP sessions, including source and destination addresses, ports, protocol type, amount of data, etc. You need a little bit heftier box, mainly the NICs, for NTOP than you do for Ethereal. Good product still.