DSCResources/DSC_SqlMemory/en-US/about_SqlMemory.help.txt
|
.NAME
SqlMemory .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 https://msdn.microsoft.com/en-us/library/ms178067.aspx. > [!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 http://sqlmax.chuvash.eu/ website. This was inspired from the repository https://github.com/mirontoli/sql-max maintained by https://github.com/mirontoli. ### Formula The dynamic maximum memory (in MB) is calculate with this formula: `powershell 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 https://github.com/dsccommunity/SqlServerDsc/issues?q=is%3Aissue+is%3Aopen+in%3Atitle+SqlMemory. .PARAMETER InstanceName Key - String The name of the SQL Server instance to be configured. .PARAMETER ServerName Write - String The host name of the SQL Server to be configured. Default value is the current computer name. .PARAMETER Ensure Write - String Allowed values: Present, Absent 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'. .PARAMETER 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. .PARAMETER MinMemory Write - SInt32 Minimum amount of memory, in MB, in the buffer pool used by the instance of SQL Server. .PARAMETER MaxMemory Write - SInt32 Maximum amount of memory, in MB, in the buffer pool used by the instance of SQL Server. .PARAMETER 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. .PARAMETER 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. .PARAMETER 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. .PARAMETER IsActiveNode Read - Boolean Returns if the current node is actively hosting the SQL Server instance. .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 } } } |