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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-27 : 11:09:23
|
| BJ writes "Hi,I'm having a strange problem. Even after cold reboot my sql server 2000(W2K server) is using (in about 2 hrs) all my 1 GB of physical memory even without any users logged in. When users starts using then it is very slow.Any idea what's going on !!!My understanding is sql server should dynamically allocate memory according to usage and deallocate after finishing the transactions.I don't have any other app. running on this machine.Any kind of response is welcome.Thanks in advanceBJ" |
|
|
MuadDBA
628 Posts |
Posted - 2001-12-27 : 16:44:55
|
| You are correct, in that SQL will dynamically use memory as it becomes needed (of course, unless you have set it to a fixed amount).If you have no other apps running, could it be some scheduled jobs that aggregate data or do some other functions?Have you run profiler to see what else is happening on the server? This might also answer your questions. |
 |
|
|
GreatInca
Posting Yak Master
102 Posts |
Posted - 2001-12-27 : 17:35:04
|
| I think there is much lag from when memory is uneeded and when it is de-allocated unless the OS threatens to start paging. I've never seen a SQL server take up a huge byte out of ram when it first starts but it is not surprising once activity starts on it. |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2001-12-27 : 22:09:50
|
| GreatInca is right -- SQL Server always tries to keep the data cached up in memory. Therefore as it reads the data from the tables, it will grab all the memory it can until the OS starts using the page file... Once paging begins -- SQL Server will free up some memory and release it back to the OS. If you want to prevent SQL Server from hogging memory, you can set "max server memory" option. However, I wouldn't do that as long as SQL is the only application on the machine -- after all, the more data SQL Server is able to cache, the better your queries will perform. In fact, since you are still seeing poor performance, I would guess that SQL Server wasn't able to cache enough... Look at the cache hit ratio in performance monitor -- if it is below 90%, you probably should consider buying more memory for your server. |
|
|
|
|
|
|
|
|
SQL server 2000 Memory usage