SQL Server 2005 uses 1GB of RAM

Question

Is this normal? To be exact it uses 1,068,000 KB. It occasionally shoots up to 1,400,000 if query takes too long.

My total system memory is 3GB and I am running WinXP. Is there a specific amount of RAM recommended for SQL Servers?

The reason I am asking this question, could our database have problem, if it uses this much RAM? We dont have a DBA here, just a programmer. Also

1. Does running multiple instances of SQL Servers affect performance?
2. Does running multiple databases hit peformance? We use only 1 but there are 5 databases running (old ones)

I am using SQL Server 2005 Express. It is not a huge database, just about 40,000 records

Answer 1

SQL Server 2005 Express Edition is limited to 1GB of RAM for the buffer pool. Non-express editions, by default, will not be limited unless configured to set a max. In either case, the usage will typically not decrease unless memory pressure forces it, or the service is restarted.

1. Multiple instances will definitely impact performance, especially if their memory allocations exceed physical memory, and the I/O contention can slow things down for concurrent queries.

2. Inactive databases will have no impact, other than the disk space they consume.

Answer 2

No, there is likely nothing wrong with your database. SQL Server reserves a very large amount of memory for itself for the purpose of caching disk reads, among other things. In server scenarios, it is absolutely common for SQL Server to take tens or even hundreds of gigabytes.

What's out of the ordinary is that SQL Server is taking so little memory, but that's because you're using SQL Server Express Edition.

Answer 3

There is nothing abnormal with this. The 1 GB of RAM shown is reserved for SQL server's use, and does not necessarily mean that it is doing a workload that is continuously taking up 1 GB of memory.

Run sp_configure 'min server memory' on your instance and the 'minimum' column will show you the memory value below which SQL server will not go to.

If you feel the current RAM usage is too much you can reduce it by running:

EXEC sys.sp_configure N'max server memory (MB)', N'500'
GO
RECONFIGURE WITH OVERRIDE
GO 

This will set the max server setting to 500 MB. SQL server will not go beyond this, but your SQL instance may be starved for memory and page out to disk when you run heavy workloads.

Yes running multiple instance on the same server affects performance. Having multiple DBs on your server does not mean they are all being used at the same time. So if you have only one DB and the rest do not have any active sessions, then you do not have to worry about performance issues.