System Monito Counter Reuslt --Suggestions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » System Monito Counter Reuslt --Suggestions

System Monito Counter Reuslt --Suggestions

Rate Topic

Display Mode

Topic Options

Author

Message

kaykay005

Posted Wednesday, May 14, 2008 12:43 AM

Grasshopper

Group: General Forum Members
Last Login: Sunday, December 07, 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

SQL ORACLE

Posted Wednesday, May 14, 2008 9:18 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 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

colin.Leversuch-Roberts

Posted Wednesday, May 21, 2008 2:05 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:10 PM
Points: 2,668, Visits: 688

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

rajankjohn

Posted Wednesday, May 21, 2008 2:14 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, February 15, 2013 2:53 PM
Points: 1,199, Visits: 568

looks like some disk tuning required as there is high queue length. all other values seem ok

Post #504265

TheSQLGuru

Posted Thursday, May 22, 2008 5:12 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 10:32 AM
Points: 3,678, Visits: 5,176

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

kasaranenikiran-725528

Posted Thursday, May 22, 2008 8:26 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, September 24, 2012 10:43 AM
Points: 22, Visits: 81

Thanks for the replies....
Regards
Rahul

Post #505207

« Prev Topic | Next Topic »

Permissions