May 14, 2008 at 12:43 am
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
May 14, 2008 at 9:18 am
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.
May 21, 2008 at 2:05 am
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
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 21, 2008 at 2:14 am
looks like some disk tuning required as there is high queue length. all other values seem ok
May 22, 2008 at 5:12 am
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
May 22, 2008 at 8:26 am
Thanks for the replies....
Regards
Rahul
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply