Sql Server Problem?

[Tiger Shark]

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

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

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?

[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?)

[ommy]

see the post underneath...sorry..

[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

[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

[Tiger Shark]

Ommy:

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

[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.

[ghostmachine]

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

[GONEin62nd]

Hello there!

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.

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

[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.