SQL Server Performance

SQL Server counter page lookups/sec

Discussion in 'Performance Tuning for DBAs' started by asha192, May 26, 2005.

  1. asha192 New Member

    Hi,

    Please can anyone tell what are the ideal values for these counters

    Page Splits/sec
    Checkpoint Pages/Sec
    User Connections
    Average Wait Time (ms)
    Full Scans/sec
    Transactions/sec
    Page lookups/sec

    Regards,
    Asha
    asha192, May 26, 2005
    #2

  2. satya Moderator

    In theory these must be in min. side, how about the values at your end.

    If bottlenecks exist due to a lack of available physical memory for storing frequently accessed data in cache, in which case SQL Server must retrieve the data from disk.

    If query performance can be improved by adding more memory or by making more memory available to the data cache or SQL Server internal structures.

    How often SQL Server needs to read data from disk. Compared to other operations, such as memory access, physical I/O consumes a lot of time. Minimizing physical I/O can improve query performance.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, May 26, 2005
    #2

  3. asha192 New Member

    Hi,

    Page Splits/sec min 0max 407.0243697
    Checkpoint Pages/Sec min 0max 4.645153509
    User Connections min 8max 10
    Average Wait Time (ms) min 0 max 0
    Full Scans/sec min 0max 5.561088507
    Transactions/sec min 0max 95.00035683
    Page lookups/sec min 2170.564918 max 19470.54728

    Please let me know whether these numbers are too high and what is the acceptable limit?

    Regards,
    Asha
    asha192, May 26, 2005
    #2

  4. Luis Martin Moderator

    You have to look Average instead min or max.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, May 26, 2005
    #2

  5. asha192 New Member

    Following are the average values. Please let us whether these are permissible or not.

    Page Splits/sec Avg 0.01384937
    Checkpoint Pages/Sec Avg 0.184827079
    User Connections Avg 8
    Average Wait Time (ms) 0
    Full Scans/sec Avg 0.042317066
    Transactions/secAvg 0.819951935
    Page lookups/sec min 4350.206475

    Regards
    Asha

    asha192, May 26, 2005
    #2

  6. Luis Martin Moderator

    I don't see any issue with those values.
    But, what about Pages/sec, Avg. Disk Queue Length, Buffer Cache Hit ratio?



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, May 26, 2005
    #2

  7. satya Moderator

    Rather than going for ideal values, define whether you're hitting any performance issues during long running queries. This way we can reduce the overhead on server and fine tune the performance.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, May 27, 2005
    #2

  8. asha192 New Member

    Hi,

    The counters that I have monitored and the average values are mentioned below.
    Full Scans/sec0.813050521
    Page Splits/sec0.866093352
    Buffer cache hit ratio97.63982308
    Checkpoint pages/sec2.310393569
    Lazy writes/sec52.21794545
    Page life expectancy360.4166667
    Page lookups/sec7118.262204
    Transactions/sec73.58980734
    User Connections8.25
    Average Wait Time (ms)0
    Lock Waits/sec0.000534017
    Number of Deadlocks/sec0
    Target Server Memory(KB)1769908.667
    Total Server Memory (KB)1769908.667
    Batch Requests/sec125.8390381

    I would have defintely liked to see what is causing the problem by using the profiler but I do not have time for that. The client wants to know what is causing the problem like yesterday.

    Regards,
    Asha
    asha192, May 27, 2005
    #2

  9. satya Moderator

    Keeping the number of lazy writes low can enhance performance. A supply of buffers available for immediate use keeps the number of lazy writes low. Before a requested page can be brought into memory, a free buffer must be available in the buffer pool. If no free buffers are available, an existing buffer must be reused.

    When an existing buffer has to be reused, many buffer pages might have to be searched in order to locate a buffer to reclaim for use. If the buffer found is marked as dirty or modified, the buffer manager must first write the changes to disk before the page can be reused and assigned to the requesting process. This results in a wait for the requesting process. Waiting processes can degrade performance.

    So look at the memory configure to the SQL Server and ensure it is a dedicated Server where resources are not shared.



    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, May 27, 2005
    #2

  10. Luis Martin Moderator

    "I would have defintely liked to see what is causing the problem by using the profiler but I do not have time for that. The client wants to know what is causing the problem like yesterday."

    But looking the counters, that is what you have to do. (Profiler).

    User allways want to know now.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



    Luis Martin, May 27, 2005
    #2

Share This Page