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
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.dbi
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.dbi
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?
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.dbi
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.dbi
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?
ASKER
sorry I meant
changed OBJECT_NAME(objectid,s.dbi
changed OBJECT_NAME(objectid,s.dbi
ASKER
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!