Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

System Monito Counter Reuslt --Suggestions Expand / Collapse
Author
Message
Posted Wednesday, May 14, 2008 12:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 7, 2008 9:09 AM
Points: 12, Visits: 60
Hi ,

Our company had an Reporting Server which runs very slow. I have put some counters and run System Monitor. And the results are below.


Objects\Counters Avg Min Max
Memory\Available Mbytes 2106 1818 2141
Memory\ Pages/Sec 0.141 0 246
Network Interface\ Total Bytes/Sec 23536713 2107 65526893
Physical Disk\ Avg Disk Queue Legth (Total) 50.44 0.0001 453
Physical Disk\Avg Disk Queue Length(I) Index 4.59 0 453
Physical Disk\Avg Disk Queue Length(J) Logs 0.8 0 30
Physical Disk\Avg Disk Queue Length(H) Data 45 0 433
Physical Disk\Avg Disk Sec/Read(H)Data 0.09 0 0.77
Physical Disk\Avg Disk Sec/Write(H)Data 0.03 0 2.37
Processor\ % Processor Time 12.62 0.038 92.61
Server Work Queues\ Queue Length 0 0 0
SQL Server Buffer manager\ Buffer cache hit ratio 99.62 88.66 99.9
SQL Server Cache manager\Total cache hit ratio 67.01 66.96 67.08
SQL Server General Statistics\ User Connections 40.03 21 94
SQL Server Locks\Total Lock Timeouts/Sec 0.18 0 89.2
SQL Server Locks\Total Dead Locks/Sec 0.0003 0 0.2
System\Processor Queue Length 0.05 0 10

Could you please suggest anything from these counters, what has to be done.??

I ran the PerfMon for the whole day from 10AM to 8PM.

We have SQL server 2000 SP4 on Windows 2003 advanced Server. I am confused with Buffer cache hit ratio and Total cache hit ratio?

I think Total cache hit ratio should not be <90. So, what was the problem and what can be done?

And what can be done for the Avg Disk Queue Length?? The data drive (H) was having 5 disks on the array.

Please suggest .



Regards

Rahul
Post #500205
Posted Wednesday, May 14, 2008 9:18 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 5, 2013 4:43 PM
Points: 1,473, Visits: 1,314
I am not able to try to answer your questions one-by-one. But the following link may help.
http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx
Personally, it is a very good one from my personal point of view.
Post #500606
Posted Wednesday, May 21, 2008 2:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697
try looking at http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/01/24/perfmon-counters-thresholds.aspx

I'd also recommend the book SQL Server 2000 performance tuning reference manual


The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #504257
Posted Wednesday, May 21, 2008 2:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:36 AM
Points: 1,199, Visits: 574
looks like some disk tuning required as there is high queue length. all other values seem ok
Post #504265
Posted Thursday, May 22, 2008 5:12 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 4,406, Visits: 6,270
kaykay005 (5/14/2008)
Hi ,

Our company had an Reporting Server which runs very slow. I have put some counters and run System Monitor. And the results are below.

Objects\Counters Avg Min Max

Network Interface\ Total Bytes/Sec 23536713 2107 65526893

Physical Disk\Avg Disk Queue Length(H) Data 45 0 433

Could you please suggest anything from these counters, what has to be done.??

Rahul


Those are the two that jump out at me. Network activity could be showing that you are returning far too much data to the client. That involves investigating and possibly refactoring app code. Reports are the first place to look I would think.

H DQL is well above the 'recommended' 2-per-disk-spindle. It could be simply because you are throwing 25MB/sec back to the client. But more likely you have a) poorly performing queries and b) missing indexes. You can run profiler and capture calls with say > 10K reads and start working those. Or if you aren't experienced you could hire a performance consultant to help you figure out what needs to be done and mentor you on how to do that in the future.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #505073
Posted Thursday, May 22, 2008 8:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 4:54 AM
Points: 22, Visits: 93
Thanks for the replies....
Regards
Rahul
Post #505207
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse