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 |
|
nickfinity
Yak Posting Veteran
55 Posts |
 Posted - 2002-02-15 : 14:20:34
|
| Hello,I've read some other post about this topic, but I still have a question. Please forgive me if this has been asked. I know SQL Server allocates as much memory as it needs unless specified otherwise. I have a web front end to my db and everytime a query is run the memory usage goes up. That's what I expect, but shouldn't the memory be released after the query is run? It doesn't seem to get released in my case. I wouldn't find it hard to believe that I am doing something wrong, but I just don't know what it is. Anyone have any ideas?Thanks,Nick |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-15 : 16:02:03
|
| Microsoft's products are notorious memory hogs, and just as notorious for not releasing it when no longer needed. It's not as bad as it used to be, but as long as it is running, it's fair to say SQL Server rarely release memory unless you explicitly tell it to.In a way, this is a good thing, since heavy query activity can be considerably slower if buffers have to be allocated and data read from disk. If it's already in cache, the speed improvement will be substantial. Unless you are seeing negative performance from the memory hogging, you should leave SQL Server to its own devices. |
 |
|
|
Spyder
SQLTeam Author
75 Posts |
Posted - 2002-02-16 : 17:37:18
|
It is possible to configure SQL Server memory usage although it is generally best to let SQL Server do it. In a perfect world, your server would have SQL Server on it and nothing else and hopefully your databases are small enough for everything to fit into cache, but since we don't live in a perfect world there are sometimes situations where your server is "wearing many hats", it can be useful to tweak the memory settings. At any rate, if you make memory changes be careful!In SQL Server 2000 (and 7.0 if I recall correctly), you can right-click on the server name in Enterprise manager and go to the Memory tab. From there you can select min and max memory usage or a fixed memory size or reserve physical memory for SQL Server. With SQL Server 6.5 the memory configuration is a little trickier because you have to express it in terms of the number of 2k pages. Just be sure you don't configure all of the memory for SQL Server because the operating system needs memory, too. Edited by - spyder on 02/16/2002 17:38:47 |
|
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-02-16 : 21:10:30
|
What you are seeing is normal (if version 7.0, I can't comment on others). Taken from "Microsoft SQL Server 7.0 Performance Tuning Technical Reference"...quote:
SQL Server will allocate memory as needed up to the maximum configured value (or actually, up to the the maximum memory available, because you could configure more physical memory than you actually have), and it will release that memory only if another application needs some, at which point SQL Server will page.
You'll only see the memory released if another process requests memory and SQL Server is hogging to much of the goods.Justin |
|
|
|
nickfinity
Yak Posting Veteran
55 Posts |
Posted - 2002-02-27 : 12:19:19
|
| Thanks everyone for the help. I've got a new question now on the same topic. When I run a query in SQL Query Analyzer it uses a lot less memory then when I run the same query from an asp page. Is that another Microsoft thing or could it be my asp code? After some heavy usage I've seen SQL Server using between 700,000k and 900,000k. That seems excessive to me considering that the db could sit there for a while with no use and not release some of that memory.Thanks again,Nick |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-27 : 12:44:41
|
| That's the point: unless another app specifically requests the memory, SQL Server will not release it. And if SQL Server is the only thing running on your server, then it will never release it. I know it sounds dumb, but when you think about it, if it's allocated to SQL Server once, and nothing else needs the memory, why not keep it allocated to SQL Server, instead of releasing it and then having to re-allocate it later if it is needed?Is the memory glom causing performance issues? If you have enough RAM to feed SQL Server, and none of the performance counters suggest a problem, I don't think you should worry about it.The only thing I can think of that's causing the extra memory used by ASP vs. QA is that ADO might add some overhead to SQL Server that a direct query wouldn't (extra buffered data, extra locks, etc.) If you use the ADO Recordset methods (MoveNext, Update, AddNew, Delete) instead of SQL commands then this overhead could become quite substantial. If you are not using stored procedures to perform INSERT/UPDATE/DELETE operations, you should try them instead and see if the memory usage improves. |
|
|
|
nickfinity
Yak Posting Veteran
55 Posts |
Posted - 2002-02-28 : 09:18:28
|
| Thanks again for your reply Rob. I do think the memory issue is causing a problem. The server has crashed twice in the past week or so. The only thing that has been different with it is the amount of work SQL Server has been doing and the amount of memory I have noticed it using.Unfortunately, I am not using SPROCs. I wrote the asp pages a while ago before I found this site and learned of the wonders of SPROCs. If only I had known. When the time permits (haha) I'll update my INSERT/UPDATE/DELETE statements to use SPROCs instead. Thanks,Nick |
|
|
|
|
|
|
|
|
Memory Usage