MSDE be suck'n up muh memory!

[wyrd]

I've 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 doesn't 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 it's certainly not my programming sucking up all this memory.

 

After all this testing, I did notice one thing; If I didn't 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? I'm using DataReader and SqlConnection, and the SqlConnection opens and closes immediately whenever the program requests data from the program (it does not remain open).

[Derek Stone]

I assume you mean 150,000 K, not 150. :)

 

This is normal behavior. I've had MSDE up to that with far fewer records, just farting around, to put it loosely. Database servers take a huge beating and it doesn't hurt to have a few gigabytes of memory. For instance, on these forums I'm fairly sure Bob has 16GB installed, and that's just the database server.

[wyrd]

Ah, I see. I think I basically understand what it's 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.

 

I'm only running 256mb on this machine. I used to use this machine for gaming + school, but now it's so old it won't run the new games so I'm 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 I've 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 you're right, I ment 150mb. :) Thanks for pointing that out.

[Gruntie]

To be honest - you don't 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 you're in a one? user test environment. You would only need to increace it (to be fair to the engine) when stress testing.