Sql-server – SQL 2008 Express Unload Due to Memory Pressure

A .NET app should clean it self up when the garbage collector runs on the app server. This should be automatically running every few minutes.

Can you query the SQL Server and see that these connections are still open on the SQL Server?

If you run netstat on the app server can you see all the socket connections open? (Each spid being used on the SQL Server will have a socket connection on the app server.)

If you do see all the ports in use on the app server then the app server is definitely not closing the connections as SQL doesn't close connections unless requested to. The .NET code may be expecting this to happen automatically, and it isn't. You probably don't have the issue in your test environment because the usage is much lower, and you probably to releases to the test environment more often which causes all the ports to be closed when IIS is restarted.

This definitely sounds like an app code issue to me.

The best method for dealing with this issue is to use the SetSystemFileCacheSize API as MS KB976618 instructs used to instruct.

Don't periodically clear the cache

Using the SetSystemFileCacheSize function rather than clearing the cache periodically improves performance and stability. Clearing the cache periodically will result in too much metafile and other info being purged from memory, and Windows will have to re-read the required info back into RAM from HDD. This creates a sudden and severe drop in performance for several seconds whenever you clear the cache, followed by good performance that slowly degrades as memory fills with metafile data.

Using the SetSystemFileCacheSize function sets minimum and maximum that will result in Windows flagging excess old metafile data as standby memory that the normal caching functions can use or discard according to the current resource demands and normal cache priorities. This also allows more metafile data than the active memory maximum you set, to be in memory as standby data if Windows is not using the memory for anything else, while maintaining plenty of available memory. This is the ideal situation keeping the performance characteristics of the system good all the time.

Third Party Programs are Unsupported by MS

If you are like me and don't want to run a binary from some unknown third party on your production servers, you want an official MS tool or some code you can inspect before running on those servers. The DynCache tool for 2008 R2 is practically impossible to obtain from M$ without paying for a support case and quite frankly, based on the code for 2008, it seems overly bloated for the task as Windows already has the built in logic needed to dynamically size the cache—it just needs to know an appropriate maximum for your system.

Solution to all of the above

I wrote a Powershell script that works on 64 bit machines. You need to run it as an administrator with elevated privileges. You should be able to run it, as is, on any x64 windows Vista / Server 2008 up to and including 10 / Server 2012 R2 with any amount of RAM. You do not need to install any additional software, and as a result keep your server/workstation fully supported by MS.

You should run this script at every boot with elevated privileges for the setting to be permanent. Windows Task Scheduler can do this for you. If the Windows install is inside a virtual machine and you change the amount of RAM allocated to that VM, you should also run it after the change.

You can run this script at any time on a running system even while in production use without having to reboot the system or shut down any services.

# Filename: setfc.ps1
$version = 1.1

#########################
# Settings
#########################

# The percentage of physical ram that will be used for SetSystemFileCache Maximum
$MaxPercent = 12.5

#########################
# Init multipliers
#########################
$OSBits = ([System.IntPtr]::Size) * 8
switch ( $OSBits)
{
    32 { $KiB = [int]1024 }
    64 { $KiB = [long]1024 }
    default {
        # not 32 or 64 bit OS. what are you doing??
        $KiB = 1024 # and hope it works anyway
        write-output "You have a weird OS which is $OSBits bit. Having a go anyway."
    }
}
# These values "inherit" the data type from $KiB
$MiB = 1024 * $KiB
$GiB = 1024 * $MiB
$TiB = 1024 * $GiB
$PiB = 1024 * $TiB
$EiB = 1024 * $PiB


#########################
# Calculated Settings
#########################

# Note that because we are using signed integers instead of unsigned
# these values are "limited" to 2 GiB or 8 EiB for 32/64 bit OSes respectively

$PhysicalRam = 0
$PhysicalRam = [long](invoke-expression (((get-wmiobject -class "win32_physicalmemory").Capacity) -join '+'))
if ( -not $? ) {
    write-output "Trying another method of detecting amount of installed RAM."
 }
if ($PhysicalRam -eq 0) {
    $PhysicalRam = [long]((Get-WmiObject -Class Win32_ComputerSystem).TotalPhysicalMemory) # gives value a bit less than actual
}
if ($PhysicalRam -eq 0) {
    write-error "Cannot Detect Physical Ram Installed. Assuming 4 GiB."
    $PhysicalRam = 4 * $GiB
}
$NewMax = [long]($PhysicalRam * 0.01 * $MaxPercent)
# The default value
# $NewMax = 1 * $TiB


#########################
# constants
#########################

# Flags bits
$FILE_CACHE_MAX_HARD_ENABLE     = 1
$FILE_CACHE_MAX_HARD_DISABLE    = 2
$FILE_CACHE_MIN_HARD_ENABLE     = 4
$FILE_CACHE_MIN_HARD_DISABLE    = 8


################################
# C# code
# for interface to kernel32.dll
################################
$source = @"
using System;
using System.Runtime.InteropServices;

namespace MyTools
{
    public static class cache
    {
        [DllImport("kernel32", SetLastError = true, CharSet = CharSet.Unicode)]
        public static extern bool GetSystemFileCacheSize(
            ref IntPtr lpMinimumFileCacheSize,
            ref IntPtr lpMaximumFileCacheSize,
            ref IntPtr lpFlags
            );

        [DllImport("kernel32", SetLastError = true, CharSet = CharSet.Unicode)]
        public static extern bool SetSystemFileCacheSize(
          IntPtr MinimumFileCacheSize,
          IntPtr MaximumFileCacheSize,
          Int32 Flags
        );

        [DllImport("kernel32", CharSet = CharSet.Unicode)]
        public static extern int GetLastError();

        public static bool Get( ref IntPtr a, ref IntPtr c, ref IntPtr d )
        {
            IntPtr lpMinimumFileCacheSize = IntPtr.Zero;
            IntPtr lpMaximumFileCacheSize = IntPtr.Zero;
            IntPtr lpFlags = IntPtr.Zero;

            bool b = GetSystemFileCacheSize(ref lpMinimumFileCacheSize, ref lpMaximumFileCacheSize, ref lpFlags);

            a = lpMinimumFileCacheSize;
            c = lpMaximumFileCacheSize;
            d = lpFlags;
            return b;
        }

        
        public static bool Set( IntPtr MinimumFileCacheSize, IntPtr MaximumFileCacheSize, Int32 Flags )
        {
            bool b = SetSystemFileCacheSize( MinimumFileCacheSize, MaximumFileCacheSize, Flags );
            if ( !b ) {
                Console.Write("SetSystemFileCacheSize returned Error with GetLastError = ");
                Console.WriteLine( GetLastError() );
            }
            return b;
        }
    }

    public class AdjPriv
    {
        [DllImport("advapi32.dll", ExactSpelling = true, SetLastError = true)]
        internal static extern bool AdjustTokenPrivileges(IntPtr htok, bool disall, ref TokPriv1Luid newst, int len, IntPtr prev, IntPtr relen);

        [DllImport("advapi32.dll", ExactSpelling = true, SetLastError = true)]
        internal static extern bool OpenProcessToken(IntPtr h, int acc, ref IntPtr phtok);

        [DllImport("advapi32.dll", SetLastError = true)]
        internal static extern bool LookupPrivilegeValue(string host, string name, ref long pluid);

        [StructLayout(LayoutKind.Sequential, Pack = 1)]
        internal struct TokPriv1Luid
        {
            public int Count;
            public long Luid;
            public int Attr;
        }
        internal const int SE_PRIVILEGE_ENABLED = 0x00000002;
        internal const int SE_PRIVILEGE_DISABLED = 0x00000000;
        internal const int TOKEN_QUERY = 0x00000008;
        internal const int TOKEN_ADJUST_PRIVILEGES = 0x00000020;

        public static bool EnablePrivilege(long processHandle, string privilege, bool disable)
        {
            bool retVal;
            TokPriv1Luid tp;
            IntPtr hproc = new IntPtr(processHandle);
            IntPtr htok = IntPtr.Zero;
            retVal = OpenProcessToken(hproc, TOKEN_ADJUST_PRIVILEGES | TOKEN_QUERY, ref htok);
            tp.Count = 1;
            tp.Luid = 0;
            if(disable)
            {
                tp.Attr = SE_PRIVILEGE_DISABLED;
            } else {
                tp.Attr = SE_PRIVILEGE_ENABLED;
            }
            retVal = LookupPrivilegeValue(null, privilege, ref tp.Luid);
            retVal = AdjustTokenPrivileges(htok, false, ref tp, 0, IntPtr.Zero, IntPtr.Zero);
            return retVal;
        }
    }
}
"@
# Add the c# code to the powershell type definitions
Add-Type -TypeDefinition $source -Language CSharp

#########################
# Powershell Functions
#########################
function output-flags ($flags)
{
    Write-output ("FILE_CACHE_MAX_HARD_ENABLE  : " + (($flags -band $FILE_CACHE_MAX_HARD_ENABLE) -gt 0) )
    Write-output ("FILE_CACHE_MAX_HARD_DISABLE : " + (($flags -band $FILE_CACHE_MAX_HARD_DISABLE) -gt 0) )
    Write-output ("FILE_CACHE_MIN_HARD_ENABLE  : " + (($flags -band $FILE_CACHE_MIN_HARD_ENABLE) -gt 0) )
    Write-output ("FILE_CACHE_MIN_HARD_DISABLE : " + (($flags -band $FILE_CACHE_MIN_HARD_DISABLE) -gt 0) )
    write-output ""
}

#########################
# Main program
#########################

write-output ""

#########################
# Get and set privilege info
$ProcessId = $pid
$processHandle = (Get-Process -id $ProcessId).Handle
$Privilege = "SeIncreaseQuotaPrivilege"
$Disable = $false
Write-output ("Enabling SE_INCREASE_QUOTA_NAME status: " + [MyTools.AdjPriv]::EnablePrivilege($processHandle, $Privilege, $Disable) )

write-output ("Program has elevated privledges: " + ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole([Security.Principal.WindowsBuiltInRole] "Administrator") )
write-output ""
whoami /PRIV | findstr /I "SeIncreaseQuotaPrivilege" | findstr /I "Enabled"
if ( -not $? )  {
    write-error "user Security Token SE_INCREASE_QUOTA_NAME: Disabled`r`n"
}
write-output "`r`n"


#########################
# Get Current Settings
# Init variables
$SFCMin = 0
$SFCMax = 0
$SFCFlags = 0
#Get Current values from kernel
$status = [MyTools.cache]::Get( [ref]$SFCMin, [ref]$SFCMax, [ref]$SFCFlags )
#typecast values so we can do some math with them
$SFCMin = [long]$SFCMin
$SFCMax = [long]$SFCMax
$SFCFlags = [long]$SFCFlags
write-output "Return values from GetSystemFileCacheSize are: "
write-output "Function Result : $status"
write-output "            Min : $SFCMin"
write-output ("            Max : $SFCMax ( " + $SFCMax / 1024 / 1024 / 1024 + " GiB )")
write-output "          Flags : $SFCFlags"
output-flags $SFCFlags


#########################
# Output our intentions
write-output ("Physical Memory Detected : $PhysicalRam ( " + $PhysicalRam / $GiB + " GiB )")
write-output ("Setting Max to " + $MaxPercent + "% : $NewMax ( " + $NewMax / $MiB + " MiB )`r`n")

#########################
# Set new settings
$SFCFlags = $SFCFlags -bor $FILE_CACHE_MAX_HARD_ENABLE # set max enabled
$SFCFlags = $SFCFlags -band (-bnot $FILE_CACHE_MAX_HARD_DISABLE) # unset max dissabled if set
# or if you want to override this calculated value
# $SFCFlags = 0
$status = [MyTools.cache]::Set( $SFCMin, $NewMax, $SFCFlags ) # calls the c# routine that makes the kernel API call
write-output "Set function returned: $status`r`n"
# if it was successfull the new SystemFileCache maximum will be NewMax
if ( $status ) {
    $SFCMax = $NewMax
}


#########################
# After setting the new values, get them back from the system to confirm
# Re-Init variables
$SFCMin = 0
$SFCMax = 0
$SFCFlags = 0
#Get Current values from kernel
$status = [MyTools.cache]::Get( [ref]$SFCMin, [ref]$SFCMax, [ref]$SFCFlags )
#typecast values so we can do some math with them
$SFCMin = [long]$SFCMin
$SFCMax = [long]$SFCMax
$SFCFlags = [long]$SFCFlags
write-output "Return values from GetSystemFileCacheSize are: "
write-output "Function Result : $status"
write-output "            Min : $SFCMin"
write-output ("            Max : $SFCMax ( " + $SFCMax / 1024 / 1024 / 1024 + " GiB )")
write-output "          Flags : $SFCFlags"
output-flags $SFCFlags

There is line near the top that says $MaxPercent = 12.5 that sets the new maximum working set (active memory) to 12.5% of the total physical RAM. Windows will dynamically size the amount of metafile data in active memory based on system demands, so you don't need to dynamically adjust this maximum.

This will not fix any issues you have with the mapped file cache getting too big.

I've also made a GetSystemFileCacheSize Powershell script and posted it on StackOverflow.

Edit: I should also point out that you should not run either of these 2 scripts from the same Powershell instance more than once, or you will receive the error that the Add-Type call has already been made.

Edit: updated SetSystemFileCacheSize script to version 1.1 that calculates an appropriate max cache value for you and has a nicer status output layout.

Edit: Now I've upgraded my Windows 7 laptop, I can tell you that the script runs successfully in Windows 10, though I haven't tested if it is still needed. But my system is still stable even when moving virtual machine HDD files around.