SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


System Monito Counter Reuslt --Suggestions


System Monito Counter Reuslt --Suggestions

Author
Message
kaykay005
kaykay005
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
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
SQL ORACLE
SQL ORACLE
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7105 Visits: 1314
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.
colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12709 Visits: 715
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/
rajankjohn
rajankjohn
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2049 Visits: 575
looks like some disk tuning required as there is high queue length. all other values seem ok
TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33593 Visits: 8683
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 on googles mail service
kasaranenikiran-725528
kasaranenikiran-725528
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 93
Thanks for the replies....
Regards
Rahul
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search