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 [Server Memory Server Configuration Options](https://msdn.microsoft.com/en-us/library/ms178067.aspx). >**NOTE** 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](http://sqlmax.chuvash.eu/) website. This was inspired from the repository [sql-max](https://github.com/mirontoli/sql-max) maintained by [@mirontoli](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 [here for all open issues](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 $env:COMPUTERNAME. .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 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 } } } |