0

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
Mehmet AVŞAR
  • 101
  • 3
  • So obvious question based on the answers below, have you tried throwing more RAM at it? What happens if you assign the machine 64 GB RAM? – BlueCompute Apr 21 '15 at 19:55

2 Answers2

1

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.

Katherine Villyard
  • 18,550
  • 4
  • 37
  • 59
0

You don't say what OS you are using? But 8GB for the server in total? That's just crazily low for a modern machine and when you say physical memory usage never exceeds 723MB, I'm assuming you are talking about SQL server and not as a whole for the server? Just trying to establish everything

Righto have to agree with the post above, is there some sort of Physical reason you only have 8Gb of ram? you get that on laptops now. I'm not even sure, I'd be happy running a file server on Win 2008 with 8Gb let alone adding SQL to it? It's not like RAM is expensive, MAX out the RAM on that machine, assuming you can put more in, if you can't then is this some sort of desktop this is installed on? What sort of drive/array do you have? DBs are I/O intensive SATA drives are truly rubbish at that, if you have mirrored them it will be even worse as everything is written twice. SOP for a DB as far as I'm concerned is RAID 10 no reason not to use it very resilient very fast.

Brooker
  • 1
  • 1
  • Memory assumption is correct. 723 mb is the amount of used physical memory reported by dm_os_process_memory DMV. Operating system is Windows 8.1 – Mehmet AVŞAR Apr 21 '15 at 19:52