SqlMemory

SqlMemory

Parameters

| Parameter | Attribute | DataType | Description | Allowed Values | | | | --- | | InstanceName | Key | String | The name of the SQL Server instance to be configured. | | | ServerName | Write | String | The host name of the SQL Server to be configured. Default value is the current computer name. | | | Ensure | Write | String | When set to 'Present' then min and max memory will be set to either the value in parameter MinMemory and MaxMemory, or dynamically configured when parameter DynamicAlloc is set to $true. When set to 'Absent' min and max memory will be set to its default values. Default value is 'Present'. | Present, Absent | | DynamicAlloc | Write | Boolean | If set to $true then max memory will be dynamically configured. When this parameter is set to $true, the parameter MaxMemory must be set to $null or not be configured. Default value is $false. | | | MinMemory | Write | SInt32 | Minimum amount of memory, in MB, in the buffer pool used by the instance of SQL Server. | | | MaxMemory | Write | SInt32 | Maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server. | | | MinMemoryPercent | Write | SInt32 | Minimum amount of memory, as a percentage of total server memory, in the buffer pool used by the instance of SQL Server. | | | MaxMemoryPercent | Write | SInt32 | Maximum amount of memory, as a percentage of total server memory, in the buffer pool used by the instance of SQL Server. | | | ProcessOnlyOnActiveNode | Write | Boolean | Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server instance. | | | IsActiveNode | Read | Boolean | Returns if the current node is actively hosting the SQL Server instance. | |

Description

The SqlMemory DSC resource sets the minimum server memory and maximum server memory configuration option. That means it sets the minimum and the maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server The default setting for minimum server memory is 0, and the default setting for maximum server memory is 2147483647 MB. Read more about minimum server memory and maximum server memory in this article Server Memory Server Configuration Options.

Important

These configuration options can also be configured using the DSC resource SqlConfiguration but will not allow the dynamic configuration as this resource provides. Make sure this value is not configured by both the resources SqLMemory and SqlConfiguration!

Formula for dynamically allocating maximum memory

The formula is based on the SQL Max Memory Calculator website. This was inspired from the repository sql-max maintained by @mirontoli.

Formula

The dynamic maximum memory (in MB) is calculate with this formula:

TotalPhysicalMemory - (NumOfSQLThreads * ThreadStackSize) - (1024 * CEILING(NumOfCores / 4)) - OSReservedMemory

NumOfSQLThreads

• If the number of cores is less than or equal to 4, the number of SQL threads is set to: 256 + (NumberOfCores - 4) * 8.
• If the number of cores is greater than 4, the number of SQL threads is set to: 0 (zero).

ThreadStackSize

• If the architecture of windows server is x86, the size of thread stack is 1MB.
• If the architecture of windows server is x64, the size of thread stack is 2MB.
• If the architecture of windows server is IA64, the size of thread stack is 4MB.

OSReservedMemory

• If the total physical memory is less than or equal to 20GB, the percentage of reserved memory for OS is 20% of total physical memory.
• If the total physical memory is greater than 20GB, the percentage of reserved memory for OS is 12.5% of total physical memory.

Requirements

• Target machine must be running Windows Server 2012 or later.
• Target machine must be running SQL Server Database Engine 2012 or later.

Known issues

All issues are not listed here, see here for all open issues.

Examples

Example 1

This example shows how to set the minimum and maximum memory configuration option with the value equal to 1024 and 12288.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlMemory 'Set_SQLServerMaxMemory_To12GB'
        {
            Ensure               = 'Present'
            DynamicAlloc         = $false
            MinMemory            = 1024
            MaxMemory            = 12288
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 2

This example shows how to set the maximum memory configuration option with the automatic configuration.

In the event this is applied to a Failover Cluster Instance (FCI), the ProcessOnlyOnActiveNode property will tell the Test-TargetResource function to evaluate if any changes are needed if the node is actively hosting the SQL Server instance.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlMemory 'Set_SQLServerMaxMemory_ToAuto'
        {
            Ensure                  = 'Present'
            DynamicAlloc            = $true
            ServerName              = 'sqltest.company.local'
            InstanceName            = 'DSC'
            ProcessOnlyOnActiveNode = $true

            PsDscRunAsCredential    = $SqlAdministratorCredential
        }
    }
}

Example 3

This example shows how to set the minimum memory to 2GB and the maximum memory configuration option with the automatic configuration.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlMemory 'Set_SQLServerMinAndMaxMemory_ToAuto'
        {
            Ensure               = 'Present'
            DynamicAlloc         = $true
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'
            MinMemory            = 2048

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 4

This example shows how to set the minimum and maximum memory configuration option with the default configuration.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlMemory 'Set_SQLServerMaxMemory_ToDefault'
        {
            Ensure               = 'Absent'
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 5

This example shows how to set the minimum and maximum memory configuration option, to a percentage of total server memory. In this case a value equal to 25 and 75 percent of total memory.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlMemory 'Set_SQLServerMemoryByPercent'
        {
            Ensure               = 'Present'
            DynamicAlloc         = $false
            MinMemoryPercent     = 25
            MaxMemoryPercent     = 75
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 6

This example shows how to set the minimum memory, to 25 percent of total server memory and the maximum memory configuration option with the automatic configuration.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlMemory 'Set_SQLServerMemoryByPercent'
        {
            Ensure               = 'Present'
            DynamicAlloc         = $true
            MinMemoryPercent     = 25
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}

Example 7

This example shows how to set the maximum memory to 75 percent of total server memory and the minimum memory to a fixed value of 1024.

Configuration Example
{
    param
    (
        [Parameter(Mandatory = $true)]
        [System.Management.Automation.PSCredential]
        $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName 'SqlServerDsc'

    node localhost
    {
        SqlMemory 'Set_SQLServerMemoryByPercent'
        {
            Ensure               = 'Present'
            DynamicAlloc         = $false
            MaxMemoryPercent     = 75
            MinMemory            = 1024
            ServerName           = 'sqltest.company.local'
            InstanceName         = 'DSC'

            PsDscRunAsCredential = $SqlAdministratorCredential
        }
    }
}