Dear All

I have problem with my database server which running SQL server 2000.
The server running very slow. The worst case, to save a record required
more than 20-30 seconds.
Since this problem, I usually monitoring Process Info from Enterprise
Manager (Management - Current Activity), and I found a misterious
process as follow :

1. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Waiting : >438 Million

2. User : System
AccessTo : Master
Status : Background
Common : Task Manager
Physical IO : > 51000

3. User : Administrator (Join domain)
Database : MSDB
Status : Sleeping
Common : Awaiting Command
App : SQL Agent Alert Engine
CPU Usage : > 16 Million

Anybody know about these condition? Does it normal?

Thanks

Michael

Michael (yapmichael2000@gmail.com) writes:
> I have problem with my database server which running SQL server 2000.
> The server running very slow. The worst case, to save a record required
> more than 20-30 seconds.
> Since this problem, I usually monitoring Process Info from Enterprise
> Manager (Management - Current Activity), and I found a misterious
> process as follow :
>
> 1. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Waiting : >438 Million
>
> 2. User : System
> AccessTo : Master
> Status : Background
> Common : Task Manager
> Physical IO : > 51000
>
> 3. User : Administrator (Join domain)
> Database : MSDB
> Status : Sleeping
> Common : Awaiting Command
> App : SQL Agent Alert Engine
> CPU Usage : > 16 Million
>
> Anybody know about these condition? Does it normal?

These are system processes, and they be normal, particularly if SQL Server
has been up for a long time. I checked a production box, and while it
had lower numbers than yours, they were still big.

The most likely reason when a server appears to be slow is poor indexing,
poorly written code and fragmentation. For instance, when saving a row and
there is a poorly written trigger, this could make the INSERT statement
to take a long time. Blocking could also be an issue, and blocking can
also easily occur, if there are slow queries.

You don't say whether this is an application, you have control over
or a third-party app. But in any, case you need to analyse exactly
which queries that are slow. One way to do this is use the SQL Profiler,
and filter for operations with a long duration. Note though that from
duration alone, you cannot tell whether it was due to blocking or bad
performance. The CPU, Reads and Writes columns can give some hints about
this. (If they are low and duration is high, there was blocking.) You
can also use sp_who to see if you have any blocking, by looking for
non-zero values in the Blk column.

Once you have found the queries that are long-running, you can look
into improving indexes, and if possible also rewrite them.

You can also try running DBCC DBREINDEX on tables where you experience
problem. If you have fragmentation, you can get improvements.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Thanks for your answer.

I've problem, as I wrote down, sometime to save a record required much
time.
When this happen, usually I restart the server, and then the problem
solved for a while. The problem will happen again within 10 day.
When the problem occured, on the Task Manager, Process Tab, SQLServ.exe
using more than 1 Gigabyte memory. And after restart the server,
SQLServ.exe only use about 450 to 500 Megabyte.

While the problem occured, there were difficulty to make connection to
the server (using Enterprise manager, Query Analyser, Application).
Usually an error message : Timeout expired.

About the application, I use visual basic to develop application.
And there were a few trigger, some of there were use cursor.
At this momenth, I have disable many trigger that used cursor, but
there a bit trigger which used cursor still active.

Would you like to give any suggestion?

Thanks very much

Michael

Michael (yapmichael2000@gmail.com) writes:
> I've problem, as I wrote down, sometime to save a record required much
> time.
> When this happen, usually I restart the server, and then the problem
> solved for a while. The problem will happen again within 10 day.
> When the problem occured, on the Task Manager, Process Tab, SQLServ.exe
> using more than 1 Gigabyte memory. And after restart the server,
> SQLServ.exe only use about 450 to 500 Megabyte.

That's perfectly normal. SQL Server grabs as much memory it needs and
can get. This memory is used for cache. So if SQL Server are kept running,
and there is no other activity on the machine, SQL Server should by
time have grown to use about all memory on the machine that the OS
does not need. (If there are other apps asking for memory, SQL Server
will yield memory.) Thus, a high memory consumption is no sign of
problem.

> While the problem occured, there were difficulty to make connection to
> the server (using Enterprise manager, Query Analyser, Application).
> Usually an error message : Timeout expired.

This one on the other hand obviously is a token of that something is wacko.
Do you get these problems also when you try to connect from the machine on
which SQL Server is running? If this works fine, one could suspect network
problems.

If not, it sounds like something is bogging down SQL Server very heavily.
This could be a poorly written query, but it also be an anomaly in the
server. Check what is in the SQL Server log at these occassions; there
might be some interesting messages. Particularly, I have one about UMS
Scheduler in mind. (A message that was added in SP3, but you are running
SP3 aren't you? By the way, SP4 is out.)

It could also be an idea to keep a Profiler trace running so you can see
what commands that are submitted and then try to correlate these commands
with the conditions where there server is not very reposnive.

Another check to make, just to rule out the more silly stuff, is that
you don't have any compressed database files.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp