Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Carat
Yak Posting Veteran
92 Posts |
 Posted - 2010-06-02 : 07:31:34
|
| End-users are complaining about the application because it is too slow. Our production SQL server has 8GB RAM. I looked in Task Manager and there I could see that SQL Server is using the total 8GB.What is the best thing to do in this situation? (rebooting the server or restarting the SQL Server service is not an option). How can I track what went wrong and how can I see which process on SQL Server is causing this?Thanks. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-02 : 07:37:26
|
| SQl will use all available RAM - that's by design.If you have other things on the same server that could be an issue, if not then that isn't the problem, more likely your queries need optimising.You can use SQL Profiler to record slow running queries (there is a template provided for that job) and you can use Performance Monitor to check for memory use / paging / disk queue length etc. but that's not something I know much about - hopefully someone who does will be along in a minute. |
 |
|
|
Carat
Yak Posting Veteran
92 Posts |
Posted - 2010-06-02 : 08:28:32
|
| Thank you for your answer Kristen.If I look to the tab 'Performance' in the Task Manager than I can see that PF Usage is at the maximum of RAM. But there is also a process (sqlservr.exe) that use 8GB. When I look to our other production SQL Servers I don't see this process.Only SQL Server is running on this server.I already used Performance Monitor to check the buffer cach hit ratio (99%) and page life expectancy (+3000). No problem there.I will check whether there are slow running queries using SQL Profiler. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-02 : 09:09:32
|
SQL won't grab all RAM immediately, it happens "over time and when needed" - so if other servers SQL Service has recently been restarted that would account for them using less RAM - or if they are lightly loaded they may not have got to the point of grabbing it all  - or they are set to use less as a MAX (but that's not a good idea if nothing else is running on the machine, although you might want to reserve 2GB for O/S)Not really my area of expertises though, so best you confirm my views before acting on them.But if you users are complaining of slow running then either a) you will find long-duration queries in SQL Profiler, or b) there is a network / CPU problem downstream of SQL Server. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-06-02 : 10:32:16
|
| What is Value for Total Server Memory and Target Server Memory in Perfmon? |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-06-02 : 22:35:42
|
| Sounds like you are running x64 version, which will take all available memory and starve the OS. On x64 systems, you need to set the max memory to prevent this.With 8GB of memory available, you should set the max to no more than 6GB - and even less than that if there are other processes running on the system (e.g. SSIS/SSRS).Check the SQL Server logs and see if there are any messages concerning memory being swapped out. Also check the application and system event logs for similar messages. |
|
|
|
|
|
|
SQL Server is using all the memory (8GB RAM)