Our app runs on a server with 4 core CPU, 8GB RAM and Sql Server 2008 R2 Standard Edition (10.50.1600.1), 64 bit. Server dedicated to Sql Server, no other heavy service is running.
We've set min/max memory usage to 4/6.4 GB respectively. AWE is off and locked_page count always remains 0. Processor affinity of Sql Server is maintained automatically, and virtual memory management of Windows is also set to auto.
Problem is, physical memory usage never exceeds 723 MB. Even when running reports with heavy load in background. We're facing huge lock waits.
Server was running on a virtual machine that we considered as the cause of the problem but, it still exists after migrating to physical server.
What could be limiting memory usage?
Edit (OS): Windows 8.1. Database was previously running on a virtual Windows 2008 server with almost same capacity. Moved to this one for testing purposes. Enough configuration for the app.
I've tried page locking. Granted page locking privilege to NETWORKSERVICE user, which runs the service, using group policy settings. Then turned on AWE. Didn't help.
Edit (Locks): This has an obvious reason. Our handheld devices carry their data offline on Sql Server CE. Once they start transferring their daily data to main database, due to long-running transactions, other clients waits for acquiring locks, mostly LCK_M_S. Though PDAs transfer data row by row, engine mostly put locks on multiple pages, due to indexes. We have no idea how to solve this issue, cause we can't opt out of using transactions.
Below you can see some indicators from two installations of the same app. Right column values belongs to the server in this question. And the left one belongs to the biggest implementation. First one obviously needs more memory.
Total Mem (GB) 14 8
Mem Used by SQL Server (GB) 12,5 0,5
Page Faults (per minute) 13500 1200
Key Lock Count 6 3
Key Lock Wait (sec) 20 11
Page Lock Count 8 3
Page Lock Wait (sec) 65 591
Stolen Pages 145945 12525
Database Pages 1045799 25564
Database Size On Disk (MB) 52504 269
User Connections 95 18
Buffer Cache Hit Ratio (%) 100 100
Best Answer
Max server memory means that your server cannot use more than that amount, not that it must use that amount. Min server memory doesn't mean it must use that amount, either; it means that once the server uses that much it doesn't surrender memory to below that amount. Basically, SQL Server uses the amount of memory it needs until it reaches the upper limit you set, and if it stops using it it might surrender part of it back unless you've set a min memory.
That said:
Microsoft's recommendations for memory for SQL Server 2008 R2 are... optimistic. (They recommend 4GB or more.) You didn't say what operating system you were using, but they also recommend 2GB of RAM for Windows Server 2008 R2 and 8GB of RAM for 2012.
Basically: that 512MB of RAM number cited as the "minimum" for the OS install is basically the "the installer will refuse to run below this" number. (Ditto for the 1GB of RAM for SQL, for that matter.)
My opinion? The operating system is what's using your RAM.