Hi, newb here so go easy please!

Anyway - I have a 'write intensive' batch application which I have just moved to a new 2005 server with 2GB of RAM and 4 core processor and have been having problems with performance. Basically; when the 'max server memory' is left as default (2GB) my process takes over 24 hours and one of the CPUs is averaging nearly 100%. I notice also that about 1GB of memory is actually being used and there is no heavy paging (still quite a few MBs of RAM left).

Now, when I change the Max memory config to 750MB  (and Min to 8MB - was 0 previously) my process takes 2hours which, for the type of operation is acceptable (this was the configuration on the old machine). Also CPU usage is a lot less and better spread across cores.

My question is; why is this? You would think more available memory would mean at least equal performance to the lower configured value, wouldn't you...

Any thoughts greatly appreciated.

 Welcome to the forum!.

Have you try leaving memory dynamically?

Yes - the original config (2GB) is the dynamic (default) setting...

 Is other application running or is SQL dedicated?

Thanks for reply - no - no other apps running. It's a dedicated server. Exactly the same conditions for both runs. The only difference is the SQL Server memory config.

This is very strange

 Yeap, and I have not a clue!

hi

 I assume you are running Win2k3?  and SQL2k5 sp2?

 With only 2gb of ram, the os will try to use most of that so leave the setting as dynamic.

Consider more memory, sql 2005 is a memory hungry beast. my rule of thumb is 80% of the time you are looking at 20% of the db, therefore for a 10gb database, you will need 2gb of ram for sql server, and as the os will take the first 2gb,  you would need  4gb, otherwise your 4 cores will be busy paging swapping data to / from disk.

also,  you say "write intensive",  what disk configuration are you running?  Scsi, RAID,  Fibre?