SQL Server Performance

Performance Tuning

Discussion in 'Performance Tuning for Hardware Configurations' started by amitm79, Dec 22, 2004.

  1. amitm79 New Member

    Hi All,
    I am looking at various performance counters to find out if i can do some tuning to improve performance. Most of the

    counters seem to be at acceptable levels. I am wondering if i can still find out something to adjust. I have following doubts

    pertaining to it
    1> In Buffer manager i have Total pages less then Target pages. Does it means my system needs more memory for buffer manager?
    2> What else to check?

    I am listing down current values for some of the counters

    Memory
    Available MBytes116
    Page faults/Sec65
    Pages/Sec0

    SQL Server: Buffer Manager
    Buffer Cache Hit Ratio96.447
    Free Pages88
    Page Reads/Sec1300.841
    Procedure Cache Pages364
    Target Pages194104
    Total Pages193592

    SQL Server: Memory manager
    SQL Cache Memory (KB)520
    Target Server Memory(KB)1568104
    Total Server Memory(KB)1560696

    Regards,
    amitm79
    amitm79, Dec 22, 2004
    #2

  2. satya Moderator

    Pages - this number shows the number of pages that constitute the SQL data cache. A large changes in this value indicates the database is swapping cache values from the cache.

    Also include %Processor and Disk monitoring counters:
    - Processor - %Processor Time
    - Processor - Processor Queue Length
    - PhysicalDisk - %Disk Read Time
    - PhysicalDisk - Avg. Disk Queue Length

    There are some other perfmon counters that depend on working pattern and dependant on the environment setup and the above are the ultimate use in the daily activity to assess the performance.

    http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp for information on database performance counters.

    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, Dec 22, 2004
    #2

  3. Luis Martin Moderator

    According those values, I think you need more memory.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

    Luis Martin, Dec 22, 2004
    #2

  4. Twan New Member

    Hi ya,

    I think that before any conclusions are drawn you really ought to
    a) use SQLProfiler to check for queries taking more than say 100ms of CPU
    b) use Perfmon trace logs over a longer period of time to get an idea of average load during peak times

    single figures like this don't really show anthing. The difference between total pages and target pages is miniscule and since there is still 110MB free it is not showing to me that your server needs more ram

    Cheers
    Twan
    Twan, Dec 22, 2004
    #2

  5. Luis Martin Moderator

    Sorry, I confuse pages/sec with Page Reads/Sec forget my post.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

    Luis Martin, Dec 22, 2004
    #2

  6. satya Moderator

    I would suggest to collect these stats during busy hours and less traffic hours to determin how SQL is stressed, say every four hours will give better idea of assessment.

    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, Dec 22, 2004
    #2

Share This Page