x64 Memory Problems

(  I’m hopeful that Livewriter will enable me to publish a blog post which includes screen shots as this one has a large number! )

Please READ everything and the documentation with the download before you install this

I’ve blogged about memory issues before, and I’ve posted quite a few times on forums possible solutions to memory issues with SQL Server.    Configuring Windows 2003 x64 for SQL Server

There seems to be far more misinformation concerning x64 memory than there ever was with x32 but as I personally think 32 bit is now a thing of the past I won’t concern myself with x32 memory.

So this is only about x64.

That said, I’ve been making use of a utility called cacheset.exe both to set the Large System Cache and to monitor its use on Production SQL Servers for quite some time now, I have always been aware of what used to be the Sysinternals utilites from way back. This utility does work on both x32 and x64 systems.

cacheset.exe

Cacheset .exe showing current sizes. You can use this utility to set the working size but a reboot will lose the settings.

( You can also use this on the command line as a startup batch command. )

Large System Cache is a part of the advanced settings for Windows, remember that originally Windows Server was most likely seen as a file server rather than a database server and the requirements for the two are somewhat different.

The Memory Usage option alters the way the large system cache is allocated.

For SQL Server it should be Programs as shown below.

 perfoptions2

This is all well and good but in x64 land the system cache size defaults to the size of all available memory in the server, this is not good and if you cannot enable lock pages in memory then there is a risk of other activities including file caching paging out the memory used by SQL Server.

Even setting lock pages in memory ( Enterprise Edition ) isn’t really a complete solution and I found in certain circumstances the following bug had an impact on the file cache size.

http://www.grumpyolddba.co.uk/infrastructure/FileCopyBugW2003.htm

You should apply this registry fix to all your production SQL Servers as a matter of course anyway.

 

If you’re using Task Manager, I prefer Process Explorer, the one section you really need to watch to expose memory issues is the Commit Charge, if this exceeds physical memory then you will have problems. In the screen shot above you can see I have 64GB of memory, the graph indicates a use of 57.8GB which leaves about 6GB free

Commit Charge is about 60GB , it’s the total of real and virtual memory being used. If you live search you’ll find the Exchange guys have been battling with server memory for some years!

The following table indicates how to calculate the virtual cache size:

Architecture

Virtual cache size

IA-32

128 megabytes (MB) + (Physical memory - 16 MB) / 4 MB x 64 MB

IA-64

128 MB + (Physical memory - 16 MB) / 8 MB x 64 MB

The following table indicates the maximum and the minimum limits for the virtual cache size calculation:

Architecture

Minimum virtual cache size

Maximum virtual cache size

IA-32 (LargeCacheSize value = 0)

Not available

512 MB

IA-32 (LargeCacheSize value = 1)

Not available

960 MB

IA-64

128 MB

1 terabyte

 

 

Here’s something you’ll have to expect with x64, SSIS has just taken over 3GB of memory; in the case where I pulled this screen shot there were two SSIS packages running on a SQL Server with 8GB of ram ( and yes I will say that 8GB of ram for a serious x64 server is well under endowed !! ) Every 64bit application can also take all available memory, in this case the SSIS packages failed after some time, but at the expense of making the SQL Server almost comatose. Be very careful with what you install on your x64 SQL Servers. This screen shot comes from Process Explorer – I’m working on a blog post about Process Explorer, pictures in the blog have always been problematic and I’m working hard to find solutions to help me transfer my posts without losing the formatting and pictures, with varying success it has to be said!!

As we all know when you deploy SQL 2008 / SQL 2005 STD Edition you can’t enable lock pages in memory which means that SQL Server can be paged out. Well those nice Microsoft chaps have come up with a solution to the x64 System Cache, it involves a service and a registry change – there’s actually lots of options but I’ll just cover the basic setup here.

I've said before that there never seems any co-ordination of output from Microsoft, yes I know it's a big company and will suffer as all big companies seem to suffer from poor communications, but in february the Windows Debug team published this somewhat interesting post.

http://blogs.msdn.com/ntdebugging/archive/2009/02/06/microsoft-windows-dynamic-cache-service.aspx

I've been running this service for a month now ( on a x64 Server ) without any signs of any problems: What I thought I'd do was mention the original post and put out screen shots illustrating the install and so-on, I realise not everyone is confident following written instructions and a picture speaks a thousand words!

( The blog will take you to a download for this component.)

What follows is from the blog post – italics are mine

==============================================================================

The Microsoft Windows Dynamic Cache Service will manage the working set size of the Windows System File Cache. For 64 bit systems this service helps to address the problem of excessive cached read I/O that could eventually consume all of physical memory. This problem is described in this blog post:

http://blogs.msdn.com/ntdebugging/archive/2007/11/27/too-much-cache.aspx

This service will only run on Windows Server 2008 or earlier versions of Windows. Do not attempt to run this service on a version of Windows after Windows Server 2008 as it will most likely cause performance problems.

Installation

1) Copy DynCache.exe to %SystemRoot%\System32.

2) From a command prompt, run:

sc create DynCache binpath= %SystemRoot%\System32\DynCache.exe start= auto type= own DisplayName= "Dynamic Cache Service"

  • you should get this message ( below ) and the service should be shown in services

  • ( don't forget to make sure the service is actually running )

3) Import the DynCache.reg registry file. This registry file contains default settings that you will probably want to modify. ( double click on DynCache.reg )

  • you should receive this message ( below ) after clicking YES

  • searching the registry with regedit for DynCache will show as follows ( below ):

 

  • Here's the view ( above ) before installing the service and making a change to the registry, as you can see the entire 64GB of installed memory is available for system cache.

  • Here I've set the max system cache to 1GB and this is reflected in the setting shown in Cacheset ( above )
  • The last step is to add the dependency upon SQL Server, add the new key as shown below

 

Make sure you read the document ReadMe.docx that comes with the download carefully before you do anything to your server(s)

Published 18 March 2009 21:33 by GrumpyOldDBA

Comments

# Are there any potential problems moving from 32 bit Enterprise to 64 bit Standard SQL Server 2005 Drija

Pingback from  Are there any potential problems moving from 32 bit Enterprise to 64 bit Standard SQL Server 2005 Drija

# x64 Memory Issues - revisited

26 January 2012 08:31 by Grumpy Old DBA - Beware the darkside !

It's funny how things seem to bob to the surface every so often, a bit like revivals of Musicals

# Are There Any Potential Problems Moving From 32 Bit Enterprise To 64 Bit Standard SQL Server 2005 | Click & Find Answer !

Pingback from  Are There Any Potential Problems Moving From 32 Bit Enterprise To 64 Bit Standard SQL Server 2005 | Click & Find Answer !