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