1

We use

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor)

The machine has 70GB Memory

The SQL Server has

  • minimum Server memory: 20480 MB
  • maximum Server memory: 51200 MB

But when I open the Resourcemonitor and check the memory of sqlserver.exe-process, I see that the Commited Memory (Zugesichert) is about 51GB, but the WorkingSet (Arbeitssatz) and Privat are only about 1GB.

enter image description here

The SQL Server is under full load and it is running for 3 month without restart.

The Page Lifetime Expectancy is 14'146s (=about 4h)

For testing purpose, I have selected a table with 3.5Mio Rows (Storage Size: 4'600MB) - but the values for WorkingSet and Private in the Resourcemonitor do not change.

Now my questions:

  • Is there something wrong or are the values of Resourcemonitor not correct?
  • If yes, where can I get the real memory usages?
  • If no, where can I take in to solve the problem or get more information?
BennoDual
  • 5,865
  • 15
  • 67
  • 153
  • Look into the local security privilege "Lock pages in memory" for your SQL Server windows service account - it will allow you to force SQL Server to keep tables in memory. – Filburt Dec 11 '17 at 08:33
  • What is your server **Edition**? Please update your post with the result of select @@version – sepupic Dec 11 '17 at 09:07
  • @sepupic Thank you for your hint. I have added the information. – BennoDual Dec 11 '17 at 09:09
  • Ok for edition, it's not limited in any way. What does Perfmon show? – sepupic Dec 11 '17 at 09:26
  • @sepupic Which Counter should I monitor to get the right information? – BennoDual Dec 11 '17 at 10:25
  • In the answer given here https://stackoverflow.com/questions/1289558/how-much-ram-is-sql-server-actually-using you can find correct counters – sepupic Dec 11 '17 at 10:54

0 Answers0