SQL Server Performance

sql server 2005 slowness

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by dba_boston, Feb 8, 2011.

  1. dba_boston Member

    sql server 2005 standard version sp3 64bit, activce/passive cluster
    on window cluster server 2003 64bit 96G RAM, 16cpu
    Recently we are experiencing prodcution sql server slowness:
    sql server memory has been configured as
    max sever menory 38G, min server memory 12G
    I have been monitoring the main performance counter:
    buffer cache hit ratio: between 95% to 99% over 24 hour
    user connections: betwween 1000 to 1200 50% of 24 hour
    % processor time: betwwen 50% to 60% over 24 hour
    processor queue length:<10 most of the time over 24 hour
    % disk time: between 0 to 100 most of time ove 24 hour
    Avg. Disk Queue length:<10 most of time over 24 hour
    memory: Pages/sec: <30 most of time ove 24 hour

    Is there any problem in terms of the hardware bottleneck?
    Thanks in advance
  2. Luis Martin Moderator

    I can't see any problem in your numbers.
    About queries, did you run profiler to find high reads/cpu?
  3. dba_boston Member

    Thank you.
    There are about 40 db on this server, with 2 big ones over 150G, and the rest db from 1G to 30G.
    No, I did not fo the trace yet, do you have any recommendation on which I need to run profiler on?
    The temp db is set as 100m, and increase 10%, do you think I need to make it bigger?
  4. Luis Martin Moderator

    My suggestion is to run profiler (in WS) on all databases with "tuning template", may be with filter "duration >= 1000".
    After that you will find large queries duration or large reads or big CPU.
    Be sure to add (in template profiler) all columns you need (reads, writes, CPU, duration, database name, etc.).
  5. satya Moderator

    I will not recommend running PROFILER on the current system that will addup more stress to the database performance.
    You have to identify where and when the performance is hampered, such as frequently running queriesThere are many object and counters for Microsoft SQL Server to choose from and it can be confusing when trying to decide what to look at and what not to for metrics. This whitepaper contains some of the more important counters to keep an eye on when characterizing performance of a Microsoft SQL Server environment. http://www.microsoft.com/technet/prodtechnol/sql/bestpactice/performance_tuning_waits_queues.mspx
    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx see thjs whitepaper that refers the proper configuration of IO subsystems is critical to the optimal performance and operation of SQL Server systems. Below are some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server.
  6. MikaS New Member


    Good alternative to running Profiler is to take server side trace from your system. There are many good examples in the net (for one example, go here) on how to set it up. Server side trace is very lightweight option to running the Profiler, as there's no need to send your results to client among other things.

    Best regards,

Share This Page