>I'm running a 2005 express now, after years of MSDE. Behaves the same, AFAIK, except that it keeps eating memory. I'm testing a routine which does summon a bunch of tables, maybe a dozen or so, and returns about twenty cursors. My guesstimate is that the total size of the cursors returned is below one megabyte, and it may update some fields in one or two tables, whose total size is around one megabyte.
>
>Why does then SqlExpress eat up to 800 megs while doing that? I can see in the Task Manager the memory being returned after I restart the service. Is there a setting somewhere? Can it somehow flush its buffers and return to dormant state when not in use for a while?
>
>Or is this the price of the freebie.

If you're not using it already, SQL Server Management Studio Express can be freely downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

With this, you can manipulate max RAM from a GUI rather than a command line (properties of the database server instance). I find it a lot easier.

The various flavours of SQL Server may use up to 50% more RAM than you specify if a given instance is heavily loaded. For example, an SBS 2003 server I administer runs 4 instances:

(Instance/Configured Max RAM/Actual RAM in use)

Windows Internal Database/256/363
Backup Exec (Symantec)/1023/263
Sharepoint/2147483647[unlimited]/115
SBSMonitoring/128/195

The Backup Exec instance was installed as part of Backup Exec 11d, hasn't been touched and is probably closely configured by Symantec. It gets used only once per day.

Sharepoint is basically not used on this server, although available.

SBSMonitoring runs all the time, maintaining server statistics.

Windows Internal Database gets installed as part of Windows Server Update Services (WSUS) 3.0 SP1. It is an EXTREME RAM hog. It will consume every single free byte of memory on the machine it runs on, which for an SBS server running as a PDC is completely unacceptable. I've personally seen this instance consume over 1.8GB of RAM on a 32-bit server that has 4GB total, and has plenty of other more important stuff to do. Microsoft doesn't want people fiddling with this instance so they make it very difficult to even connect to it. The magic invocation (string) you use to connect to it in Management Studio Express is

\\.\pipe\mssql$microsoft##ssee\sql\query

By default the maximum RAM setting for this instance is also 2147483647 (unlimited). It seems to work just fine when limited to 256MB.

If you do change maximum RAM settings for an instance (either up or down), IME it can take some time (hours+) for the instance to "adjust" to it, and performance may be quite a bit less during this period.