Michael (ya************@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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp