SQL Server Performance

Slow Performance

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by fcti, Oct 3, 2006.

  1. fcti New Member

    Hello

    I have sql server 2005 x64 SP1 on a server with win 2003 x64, 2 dual core opterons, and 4 gb of memory. There are no other services running on this server except for SQL 2005. The four processors are constantly above 70% and the free memory is 100 mb. SQL 2005 is configure to use a minimum of 1 gb of memory and a max of 3 gb. I stop all sql jobs and even took sql 2005 offline and the performance remains the same.

    Please help!!! My users are hunting me down and I am cowering in the server room.

    -DBA_Rookie

    Thee
  2. satya Moderator

    What kind of queries you're running and how about the index defragmentation methods in this case?

    A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. SQL Server 2000 exposes some of this information though system tables such as sysprocesses, but sometimes you need to generate a physical dump of the SQL Server process memory to extract relevant information from internal structures.
    Check the resource bottlenecks: CPU, memory, and I/O bottleneck, Tempdb bottlenecks and a slow running user query. The poor choice of a query plan can tax system resources and cause an overall slowdown of the workload. So, if a large table is missing a useful index, or the query optimizer decides not to use it, this not only causes the query to slow down but it also puts heavy pressure on the I/O subsystem to read the unnecessary data pages and on the memory (buffer pool) to store these pages in the cache. Similarly, excessive recompilation of a frequently running query can put pressure on the CPU.



    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. joechang New Member

    learn to use the PSSDIAG and Read80Trace (from RML, maybe Read90Trace for 2005)

    search the terms PSSDIAG or RML on the MS site
    or get Quest Coefficient or some other third party product

    if you do not collect the right information,
    then you are just guessing blindly
    which is more likely to cause harm than help
  4. gurucb New Member

    See if paging is occuring on server. Look for below counters:

    Pages / second
    Access Methods -> Full Scans / seconds
    Memory Available MBytes
    Working set size for processes

    Processor is it more on user time or privileged time.

    If SQL is high on CPU
    -> Compilations / sec or recompilations /sec.
    -> if compilcation / sec is high then check for batches / sec if both follow same trend then it should be adhoc queries are more.
    Set database property to forced parameterization on
    -> If recompilation is more see subclass event to check reason for recompilation

    -> If priv time is more then it should IO or paging in server
    Cap max server memory to 3 Gigs and let OS have breating space and see performance.

    Use tools like
    PSSDiag
    ReadTrace (RML)
    IOSim
    http://www.microsoft.com/downloads/...6A-28C2-4483-8293-76FFF67B9EB3&displaylang=en
    http://support.microsoft.com/kb/231619


Share This Page