Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: System MonitorCounter Results

    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 5526893
    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

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    you want to keep "Physical Disk\Avg Disk Queue" under 2 per physical disk. that number for H looks pretty bad.

    it looks like you have a I\O bottleneck, but the causes can vary depending on the details on your configuration. performance tuning is the black magic of the sql arts.

    Have you tried to tune the sql?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    May 2008
    Posts
    3

    hi

    Do u mean tuning the SQL queries?? I haven't yet . I have put up a trace through profiler and some of the events which were very high in Reads have a table scan going on... SO, putting an index on that will solve the problem or do we have to do anything elese?? Thanks for the reply

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    table scans are bad. indexes sometimes clean those up. do that first and come back if you still have a problem.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •