Link to home
Start Free TrialLog in
Avatar of startianshi
startianshi

asked on

How do I interprete performance counters on a sql db server

We have sql server 2005 enterprise running on a power edge 6850, raid 5, 8 cpu, 4 gb ram. C drive 12 gb with 4 gb free space.  we have a .net ERP  application using this db, with about 50 users.
Problem is users experience sporadic system slow downs (only the ERP app). It happens intermittenly. Some user will
experience the slow down for say half hour (reading and writing to the application is very slow), while
at the same time other users are fine. Then the slow down went away, and may come some time later to  other users.
I saw spikes of physicaldisk read/sec and average que, sometimes in page/sec in the db server performance monitor. But I don't know these spikes can be interpreted as problem or not. Can someone look at the snapshots and tell me? Any suggestion is greatly appreciated!
(I am attaching the screen shots of the performance monitor for both a 1 minute snap shot and a 43 minute snap shot, as well as the txt 43min log file recorded every 15 seconds)

ServerPerf.doc
PerfLog43Min.txt
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of startianshi
startianshi

ASKER

Thanks.
1. The server has 8 cpus. The Avg Queue lenght counter is for "ALL", so in the performance monitor, value lower than 32 (8x4) should not be a concern?
2. I got an low average queue length ( 1 over 43 min period), with many spikes -- what is the frequency of the spikes should be viewed as dangerous?
3. does the page/sec look problematic in the chart?

Thanks again!
Also, I ran the first query in your article and got this error--
Msg 174, Level 15, State 1, Line 3
The object_name function requires 1 argument(s).

I looked up BOL and changed OBJECT_NAME(objectid,s.dbid) from OBJECT_NAME(objectid)
and ran again and got this error:


Msg 321, Level 15, State 1, Line 7
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

(our db compatibility mode is 80)

Your query is:
SELECT
             s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
             s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,s.dbid),
Definition = CAST(text AS VARCHAR(MAX))
 INTO        #Processes
 FROM      sys.sysprocesses s
 CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 WHERE
            s.spid > 50

Is it the compatibility mode problem?
sorry I meant
changed OBJECT_NAME(objectid,s.dbid) TO OBJECT_NAME(objectid)