Post reply

SQL Performance Counters and Profiler Counters

  • August 24, 2005 at 9:13 am

    #90795

    Hi All,

    Can anyone provide me with a ready list of few performance counters and their set standards against which I can evaluate with my server performance.

    Counter Name

    Average

    Min/Max

    Memory: Pages/sec

     

    Memory: Available Bytes

    Physical Disk: % Disk time

     

    Physical Disk: Avg. Disk Queue Length

    Processor: % Total Processor Time

     

    System: Processor Queue Length

     

    SQL Server Buffer: Buffer Cache Hit Ratio

     

    SQL Server General: User Connections

     

     

     

    Also few commonly used SQL Profiler counters and their standards.

    Any help will be highly appreciated.

    --Kishore

  • August 24, 2005 at 9:29 am

    #584646

     

    Hello,

    This is what I keep from a couple of years ago, guidelines may change. I know it is from one of the books I have but I don't remember from what book. Anyway the author of this book should be credited, not me. I personally do not agree with anything saying "less then 90%",  I think it should be "less then 75%" whatever metrics we consider. I also do not thing that Available Memory should be more then 4 MB, I would say, it should be more then 2 GB, but again, depending on your system it should be more then 25% of total memory on the system at least.

     

    Windows NT Performance Monitor Counters

     

    Object

    Counter

    Applicable to

    Recommended Value

    Processor

    %Processor Time

     

    CPU

    < 75 %

    Processor

    %Privileged Time

    Time for executing system commands and user apps

     

    Processor

    %User Time

     

    Processor

    Interrupts/sec

    CPU

    <=1000 (486)

    <=3500 (pentium)

    Memory

    Available Bytes

    Memory

    >= 4 MB

    Memory

    Committed Bytes

    Memory

    <=Physical Memory

    Memory

    Pages/sec

    Memory

    <=20

    Physical Disk

    Avg. Disk Bytes/Write

     

    I/O

    <15 ms

    Physical Disk

    Avg. Disk Bytes/Read

     

    I/O

    <15 ms

    Physical Disk

    Disk Reads/sec

    I/O

    The sum of both should be less the disk capacity of 85 I/O operations/sec

    Physical Disk

    Disk Writes/sec

    I/O

    Physical disk

    Disk Queue Length

    I/O

    <=2

    Physical disk

    % disk time

    I/O

    <=90

    Paging File

    %Usage

    Page File

     

    Network Segment

    % Network Utilization

    Network

    <=30 on the Ethernet

     

     

    Attention: Disk counters could slow performance.

     

    Regards,Yelena Varsha

  • August 24, 2005 at 9:46 am

    #584662

    Thanks Yelena for the info.

    Anything for sql profiler ?

     

  • August 24, 2005 at 9:48 am

    #584665

    Thanks Yelena for the info.

    Anything for sql profiler ?

     

  • August 24, 2005 at 10:48 am

    #584693

    You mean what to select for profiler?

    - the less fields/ events the better

    - always select Text Data, shows you statements.

    Everything else depends on the particular task you are trying to accomplish

    Regards,Yelena Varsha

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply