SQL Server Memory Usage – Why sqlsrvr.exe Is Not Using All Memory

As I see, you're using a 32 bit system, so please follow the information from this MSDN article: Do I have to assign the Lock Pages in Memory privilege for Local System?. It's from MS's own CSS team.

Quote from it: "If you are using 32bit systems, the messages and algorithm are slightly different. The message you should look for in the ERRORLOG is the following:

Address Windowing Extensions is enabled

If the privilege was not set correctly, you will see this message:

Could not use Address Windowing Extensions because the 'lock pages in memory' privilege was not granted.

These messages will NOT show up for 32bit systems unless the configuration value 'awe enabled' is set to 1. The second message can show up if the "Lock Pages in Memory" privilge was changed after successfully configuring AWE and then the privilige was cleared OR you used RECONFIGURE WITH OVERRIDE and the privilige was not set. "

Another detailed article regarding "Lock pages in memory" is from well known sql writer Glenn Berry: SQL Server and the “Lock pages in memory” Right in Windows Server.

The actual memory in use won't increase until you actually load data into memory. It's not going to jump to 10GB combined allocated just because that's what you've set max memory to and because your databases are at least that large. It's a max, not a min (and never mind that min doesn't work that way either), and it isn't going to try to guess - before you've run enough queries - which tables / indexes it should load into buffer pool memory.

So yes, it is normal to see a gap in these values. You can probably get much closer to the max values if you pump the buffer pool by issuing a bunch of SELECT * FROM dbo.BiggestTables; on each instance, but it is usually better to let SQL Server allocate the memory as it deems appropriate based on actual usage by your real application. And remember too that max server memory doesn't just cover the buffer pool.

In short, SQL Server is pretty good at memory management; let it do its thing, and worry about adjusting max memory on your various instances when you have an actual performance issue.