SQL Server Performance

High PF Usage

Discussion in 'Performance Tuning for DBAs' started by peace2007, Mar 14, 2009.

  1. peace2007 New Member

    We have performanceproblem in our SQL server2005. Indexes are rebuilt, statistics are updated and slow queries are tuned however the sql server is still slow! In task manager CPU usage is around 3% sometimes goes up to 50% when we try to run a query through out web application. and the other problem is that PF Usage gets so high. Server has 8GB of RAM and PFUsage sometimes goes to 4GB or 7GB
    other values in task manager is as follows:
    totals:
    handles: 21145 , threads 1164, processes 70
    Physical memory:
    total: 8386216, available 4435588, system cache 1510852
    commit change:
    total: 4171988, limit 10030600, peak 4256540
    Kernel Memory:
    total 131980, paged 87172, nonpages 44820
    I wonder what really causes our sql server become slow. It is slow even if no user is connected to our application.
    Could anyone tell me how I can resolve this problem.
  2. geebee2 New Member

    Not enough information.The thread count is quite high.What are the stats for the SQL server process? ( Mem Usage, Peak Mem Usage )Is it SQL Server of something else that is using excessive memory? ( use Processes tab in task manager )What is the application? Is it a web application or something else?
  3. peace2007 New Member

    sqlserver.exe takes around 4GB of memory. the sqlserver only serves for our web application so I need a way to see why this application takes so much memory
  4. geebee2 New Member

    SQL server will use memory if it is available ( you can configure this of course in server properties ).
    The paging statistics don't look unusual to me.
    You say it is running slowly - is this specific queries?
  5. peace2007 New Member

    no the sqlserver is generally slow even if no application is run on it
  6. geebee2 New Member

    How are you measuring this "slowness"?
    What is "slow"?
    You need to be more specific.
  7. peace2007 New Member

    You're right :)
    opening management studio takes around 20 seconds. besides the most remarkable slowness is when no one is connected to the server and I try to run a query through the application. This is not about a specific query it is slow everywhere even if I try to run a query through query analyzer. I say SQLserver is slow because I see it is the only process that takes CPU and memory much more than others do
  8. geebee2 New Member

    Have you checked event logs? Maybe something is not running correctly ( authorization perhaps ).
  9. satya Moderator

    It is good that the initial information about the machine status can be obtained using Task Manager, but this should not be taken as final value or count to asses the system's performance.
    To get further information on the issues better to run PERFMON (SYSMON) to capture counters for PHysical disk, memory, CPU & SQL user connections that can get you what is happening at the point of busy and quiet times on the server.
    Here I would like to ask you to check whether database AUTOCLOSE is enabled, number of job scheduled during the day and also see whether backups of transaction log is taking longer time to complete. http://sqlserver-qa.net/blogs/perftune/archive/2008/04/01/3733.aspx see the information define there and also http://sqlserver-qa.net/blogs/perftune/archive/2007/12/28/3048.aspx about the memory configuration that has been defined on the environment.
    HTH

Share This Page