Find most intensive sql server process

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Find most intensive sql server process

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-10 : 12:32:10

Biva writes "Hello,

I have tried to research an answer to this question on the web, but so far have been unsuccessful in finding the answer.
As a DBA, I hear complaints as to the server being slow once in a while. Whenever I hear complaints, I do sp_who2 and find out if there is a blocking going on. If there is not, I use Compaq Insight Manager and look at the processor load. What I would really like to find out is if there is a process that is taking up a lot of resources (cpu time or memory), find the owner of that process and ask that person what he /she is doing.
Now my question is: When I do the sp_who2, it shows me CPUTime and DiskIO and last batch time for processes.
Is this what I should be looking at and is that a correct measure to find out which processes are resource intensive?
If a process has a high CPU time and high IO, does that mean that process is the culprit?

Any help would be greatly appreciated.

Thanks,
Biva"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 12:48:10

You should be using SQL Profiler to get this information. sp_who and sp_who2 just give you basic information.

Tara

byauchler
Starting Member

4 Posts

Posted - 2004-05-10 : 13:15:59

Thanks Tara. But events should I be looking at?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 13:20:17

You should start with the default events. Save your trace results to a table, then:

SELECT TOP 10 *
FROM TraceTableName
WHERE TextData IS NOT NULL
ORDER BY Duration Desc

The above will give you the worst 10 performing queries that occurred while the trace was running. Fix those queries (rewrite the query, add indexes, drop indexes, optimize, etc...), then look at the next 10 until you find that the longest running query takes a small amount of time. All of your queries should take less than 10 seconds, preferably under 5.

Tara

byauchler
Starting Member

4 Posts

Posted - 2004-05-10 : 14:03:25

Thanks Tara. But events should I be looking at?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-10 : 14:07:04

You should start with the default events. Just use the default trace template.

Tara

byauchler
Starting Member

4 Posts

Posted - 2004-05-10 : 14:09:25

Thanks Tara. That was very helpful. I will try to use this method from now onwards.
Appreciahte your help!
Biva