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.

  • 2
    Enterprise edition? – Stijn Wynants Sep 19 '17 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 '17 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. – Michael Wang Sep 19 '17 at 14:53
  • @Shark Wang For correctness sake, please, update your question with the result of SELECT @@version – sepupic Sep 19 '17 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) – Michael Wang Sep 19 '17 at 16:03
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.

  • 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. – Michael Wang Sep 20 '17 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 '17 at 5:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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