| Author |
Topic |
|
limester
Starting Member
11 Posts |
 Posted - 2004-12-01 : 19:20:09
|
| Hi All,Currently I have an application that uses SQL 2000. The SQL server service tends to take up as much of the physical memory as possible. The problem is I also have other services relating to this application running that are very important. What tends to happen after a period of time is SQL takes up all of the physical memory, so that the other services are using the paging file (virtual memory). This causes extremely slow response time over the network as these other services are having to parse the paging file.I know there is an option to set memory usage for SQL but I am unsure how this would respond in a production environment. What would happen if SQL would require more memory than what was allocated to it?Can SQL release the memory and still act as normal?Any input would be generously appreciatedCheers! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-01 : 19:29:39
|
| If SQL Server needs more memory and can't get it, then performance of the query will suffer. SQL Server should be on a dedicated server. It's designed to be a memory hog. And yes SQL Server will release memory if it no longer needs it AND another process is requesting it. In your situation though, it sounds like SQL Server still needs it since it wasn't able to release it. Upgrade the box or move SQL Server onto its own server.Tara |
 |
|
|
limester
Starting Member
11 Posts |
Posted - 2004-12-01 : 19:33:44
|
| Thanks for your reply!Do you think if I would be better off to set the OS virtual memory to just the size of physical RAM, so that SQL will release memory when it sees "pressure" from the other services?...or should I not put that much confidence into SQL's ability to relinquish memory?Cheers! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-01 : 19:38:03
|
| So your page file isn't set to the size of RAM already? I believe that's a standard practice/recommendation. Not just for SQL Server, but for Windows boxes.And no I wouldn't bet on SQL Server releasing memory. It's designed to be a memory hog. That's the main reason why SQL Server should be on a dedicated server.Tara |
|
|
|
limester
Starting Member
11 Posts |
Posted - 2004-12-01 : 19:40:24
|
| The OS is setup to use the default amount of virtual memory, which is 1 and a half times the size of physical RAM.Is this not a good practice? Should I adjust the default value to the size of physical RAM?Cheers! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-01 : 19:41:42
|
| Let me look through my documentation. I might have remembered the setting incorrectly.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-01 : 19:43:19
|
| 1.5 times the amound of RAM is the recommended setting.Tara |
|
|
|
limester
Starting Member
11 Posts |
Posted - 2004-12-02 : 09:51:19
|
| If 1.5 times the amount of RAM is Microsoft's recommended setting, why would they choose a setting that would provide poor performance?Any read/writes to the paging file will decrease a processes performance wouldn't it? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-02 : 12:34:03
|
| Yes as compared to having done it in RAM. SQL Server is designed to do very little paging, so on a dedicated box you'll see very little of this if you've got enough RAM.Tara |
|
|
|
limester
Starting Member
11 Posts |
Posted - 2004-12-03 : 08:28:01
|
| Thanks Tara.How can I determine how much RAM the server will need if SQL will take it all regardless? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-12-03 : 12:19:16
|
| The only way to tell is to have your application run for some time and then check out Performance Monitor for the SQL Server Memory Manager counters of Target and Total. If Target is higher then Total then it needs more. These numbers should be equal if memory is not a bottleneck.Tara |
|
|
|
|
SQL Server Memory Usage