Sql-server – SQL Server taking a lot of memory

sql serversql-server-2005sql-server-express

I have SQL Server Express on my VPS(2GB RAM) with a database size of 1.5 GB that I expect will increase to 15 GB. I have noticed that SQL Server is using 1.5 GB RAM even when only small queries has been run against the database.

Is there any relation between memory and database size? Can I tune my SQL Server so it can handle memory better and not use as much memory as it does now?

Here is the memory status of SQL Server:

VM Reserved,1455488
VM Committed,19320
AWE Allocated,0
Reserved Memory,1024
Reserved Memory In Use,0

Best Answer

SQL Server is known for by default consuming as much memory as the OS allows it to take, so you'll have to reduce it manually.

One way of doing this is described here and here. On the second link, specifically, you will find this example that might be useful for you:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Instead of 4096, you might want to enter something smaller depending on your available RAM.

And, as @outcoldman says in a comment to your question, SQL Server Express doesn't allow 15 GB databases. This answer specifies 4 GB max for SQL Server Express 2005 and 10 GB max for SQL Server Express 2008 & 2012.

Related Solutions

Sql-server – Is the SQL Server Express memory and CPU limit per instance

If I have 8GB of RAM in a server and I run 4 instances of SQL Express, will the total memory limit used by SQL Server be 1GB or 4GB?

Each instance can use up to 1GB of memory for the buffer pool. Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool. In your case, the maximum memory used by the four instances for buffer pool would be 4GB.

BOL extract

To confirm, I started two instances of the SQL Server 2008 Express Database Engine, performed some activity to load up the (separate) buffer pools, and then looked at per-instance memory utilization in a number of ways, for example using DBCC MEMORYSTATUS or by counting the number of buffers using the sys.dm_os_buffer_descriptors DMV.

The physical memory usage numbers below were obtained using simultaneous queries against the system DMV sys.dm_os_process_memory on each instance of the database engine:

SELECT 
    dopm.physical_memory_in_use_kb 
FROM sys.dm_os_process_memory AS dopm;

Output:

╔═══════════╦═══════════╗
║ Instance1 ║ Instance2 ║
╠═══════════╬═══════════╣
║   1102872 ║   1059812 ║
╚═══════════╩═══════════╝

Each of these is slightly in excess of 1GB as total physical memory usage includes more than just buffer pool usage, as mentioned previously.

Would it be advisable to run multiple instances like this to enable each database to make better use of resources (assuming that the server has plenty of resources)?

If the databases on each instance are functionally independent then it is at least workable to run multiple instances of Express in this way, though you would need to pay careful attention to configuration and maintenance may be made more complex.

You might be be better served using another edition, such as the fully-featured (and very cheap) Developer Edition, if the intended usage matches the licence). You would need to say much more about the circumstances to get a clear answer on this point.

Sql-server – Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram

As you might know, the reason it allocates 1.7 GB of working set is 32-bit memory limitations. On 32-bit editions of Windows, by default each process gets 2 GB of usable virtual address space, and by default 32-bit editions of SQL Server leave a few hundred MB outside of its largest consumer of memory, the buffer pool.

It is most likely just a misunderstanding that SQL Server allocated only 60 MB of memory when you enabled locked pages. Memory allocated through AWE doesn't show up on the processes tab in Task Manager (or other tools that report a figure based on the processes' working set size) because it isn't part of the process' working set. If SQL Server used a smaller amount of memory when you enabled Lock Pages in Memory, this suggests that AWE was working and the SQL Server process had most likely allocated more than 60 MB of memory.

There are several ways to view memory allocated by AWE, including but not limited to the following.

Query sys.dm_os_memory_clerks, for example as follows:

SELECT SUM(awe_allocated_kb) AS [Total KB AWE Allocated] 
FROM sys.dm_os_memory_clerks;

View the Performance Monitor counter SQL Server: Memory Manager\Total Server Memory (KB)

A few notes on your question:

As you probably know, basically anyone who does this for a living will tell you that it's long past time to migrate off the 32-bit operating system and 32-bit SQL Server. You'll be able to use far more RAM without any hassles on a 64-bit platform.
It's highly unusual for SQL Server to be running as the Network Service account.
Processes allocate memory, not RAM (which may not be the same thing)
You shouldn't need to add /PAE to boot.ini. Windows has booted a PAE-aware kernel by default since Windows 2003 SP1 unless you also disable Data Execution Prevention with the switch '/noexecute=alwaysoff'. (You don't want to do that, by the way.)
Using locked pages can cause out-of-memory errors if you don't set a reasonable memory limit in SQL Server or if something other than the SQL Server buffer pool -- either another program or another part of SQL Server -- is competing for memory in Windows.

References:

How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!

Fun with Locked Pages, AWE, Task Manager, and the Working Set…

Great SQL Server Debates: Lock Pages in Memory

Best Answer

Related Solutions

Sql-server – Is the SQL Server Express memory and CPU limit per instance

Sql-server – Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram

Related Question