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.
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?
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
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?
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
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
