1

Recently, we added some physical RAM to our server which running SQL Server 2008 R2 instance, the total memory was changed from 64GB to 192GB.

And we tuned the memory option of SQL Server by changing the 'min server memory' from 16GB to 64GB, and the 'max server memory' from 48GB to 128GB.

And the pagefile of Windows Server 2008 R2 is left unchanged with 64GB.

But after two days later, Windows Server reported run out of virtual memory, some 'Windows successfully diagnosed a low virtual memory condition' events can be found in EventLogs. We cannot connected via remote desktop client, and cannot launch mmc or notepad application even though operating from console, only Explorer and task manager can be opened. at that time, about 50% physical memory was used.

And now, we change 'min server memory' back to 16GB, the server runs without any resource exhaustion detector events, but the physical RAM usage was always lower than 64GB.

SQL Server Version:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64) Jun 24 2011 13:09:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Please help us to solve it.

Thanks.

Improve this question
7
  • 2
    Enterprise edition?
    – Stijn Wynants
    Sep 19, 2017 at 12:05
  • 3
    Are both the OS and SQL Server Enterprise Edition? Windows Server 2008 R2 Standard is limited to 64GB, which would cause these symptoms.
    – Dan Guzman
    Sep 19, 2017 at 12:08
  • OS is Windows Server 2008 R2 Enterprise Edition, and DB is SQL Server 2008 R2 Enterprise Edition, all of the 192GB memory presented under the Control Panel\System and Security\System.
    – Steven Wang
    Sep 19, 2017 at 14:53
  • @Shark Wang For correctness sake, please, update your question with the result of SELECT @@version
    – sepupic
    Sep 19, 2017 at 15:31
  • @sepupic Microsoft SQL Server 2008 R2 (SP1) - 10.50.2769.0 (X64) Jun 24 2011 13:09:43 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    – Steven Wang
    Sep 19, 2017 at 16:03

1 Answer 1

Reset to default
1

I believe your virtual memory should be, at minimum, the same size as your physical memory.

I once had a new server deployment that had tested well, but performed terribly under a full load. MS got involved, and finally figured out we had a 16GB page file, with 128GB physical memory. Their recommendation was that the page file be at least 1.5 times as large as physical memory.

Bumping our page file size up to 192 GB got performance back to normal. Not sure if 1.5 X physical memory is truly required (in fact, I doubt it), but page file significantly less than physical memory does seem to be a Bad Thing.

NOTE: Our system at the time was Windows Server 2008R2, SQL Server 2008.

Improve this answer
2
  • By checking the MSDN documents, there is no need to set page file size up to 1, 1.5 or 2 times of large physical RAM. we also have another two servers works well with 128GB RAM and 64GB page file, but they runs with Windows Server 2012 R2 and SQL Server 2014.
    – Steven Wang
    Sep 20, 2017 at 1:20
  • @SharkWang You can set page file to almost the RAM size and if RAM is smaller you can make page file to 1.5 times the RAM size, there is no harm in that AFAIK.
    – Shanky
    Sep 20, 2017 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.