MSDE be suck'n up muh memory!

wyrd

wyrd

Well-known member
Joined
Aug 23, 2002
Messages
1,408
Location
California
Ive been toying with a table that has 1 million records in it (so I can tweak performance of my app under such pressure). There are a few hiccups in it, but it seems like it doesnt have to do my app, rather MSDE itself.

I opened up the Windows Task Manager to watch the memory usage of MSDE as I navigated through my program and did different selections on the 1 million record table. Depending on the query the memory that MSDE used grew by a certain amount, reaching at times around 150k or so. Eventually, it decided to reclaim memory when it sucked up to much (going from 150k to about 6k), which made my program sit there "waiting" for a resultset and eventually timing out (because MSDE was busy reclaiming memory)

I checked my program to make sure DataReaders were closing and that my SqlConnections were closed. They indeed are. Nothing is left open after a query is made, and its certainly not my programming sucking up all this memory.

After all this testing, I did notice one thing; If I didnt make any queries to MSDE for a while, it eventually reclaimed whatever memory was in use, slowly but surely.

So my question is, what is this from and is there any way to fix it, or is this just the way MSDE (and Sql Server alike) works? Im using DataReader and SqlConnection, and the SqlConnection opens and closes immediately whenever the program requests data from the program (it does not remain open).
 
I assume you mean 150,000 K, not 150. :)

This is normal behavior. Ive had MSDE up to that with far fewer records, just farting around, to put it loosely. Database servers take a huge beating and it doesnt hurt to have a few gigabytes of memory. For instance, on these forums Im fairly sure Bob has 16GB installed, and thats just the database server.
 
Ah, I see. I think I basically understand what its doing just by watching the memory go up then slowly back down in the Task Manager. I just wanted to double check and make sure it was indeed MSDE and not my program.

Im only running 256mb on this machine. I used to use this machine for gaming + school, but now its so old it wont run the new games so Im going to turn it into a work computer. I guess if I plan on running large databases it on I should toss in some more memory.

To hopefully not suck up to much memory Ive started being a little more size conservative with my table columns. I changed a few varchar columns from 30 to 20 and 40 to 30 in size. Hopefully this will help a tad.

EDIT:
Oh and youre right, I ment 150mb. :) Thanks for pointing that out.
 
To be honest - you dont need to worry about this too much - the SQL Server Engine is very efficent at managing its dick cache and will release memory to higher priority processes as required. You can also set (well you can in SQL Server) the amount of memory used by this cache - this can normally be quite small as youre in a one? user test environment. You would only need to increace it (to be fair to the engine) when stress testing.
 
Back
Top