SqlServerMaxDop: Feature request - add cost threshold property

[Zuldan]

It would be great if there was a cost threshold property in this resource. There's no point defining MaxDOP if cost threshold hasn't been defined (setting the bar as to when to allow queries to multithreaded).

https://msdn.microsoft.com/en-us/library/ms190949.aspx

[johlju]

You mean it would be better to have a parameter for that value in this resource than using the xSQLServerConfiguration resource to set that value first? Or is your thinking to make cost threshold for parallelism dynamic like $DynamicAlloc does with max degree of parallelism (MAXDOP)?

You could as easily set max degree of parallelism using xSQLServerConfiguration, right? Unless you want the dynamic functionality.

[Zuldan]

@johlju, you wouldn't configure MAXDOP without configuring 'cost threshold for parallelism' as well. I just figured because both settings go hand in hand they should be configured under the same resource. I guess 'cost threshold for parallelism' could be optional in the resource.

You are correct MAXDOP and 'cost threshold for parallelism' can both be set in xSQLServerConfiguration. I just thought it was odd to configure one option via xSQLServerConfiguratio and the other option via dedicated resource (xSQLServerMaxDop).

It's like formatting a disk with xDisk and then using xComputerConfiguration to assign it a drive letter. Everything should be done under xDisk.

Sorry hope I explained ok. I guess other people might see it from another point of view. Just throwing it out there.

[Zuldan]

Here a little info about why the options are configured together. https://www.littlekendra.com/2016/07/14/max-degree-of-parallelism-cost-threshold-for-parallelism/

[johlju]

Yes you explain well, and I don't see a problem adding a parameter for it in this resource. Just some thoughts

1. The resource is named xSQLServerMaxDop, won't that be misleading that it also changes another value?
2. What should we name the new parameter? CostThresholdForParallelism?
3. How should the cost threshold for parallelism parameter work when DynamicAlloc is set to $true?

[aleksbor]

Is this still in the works?

[johlju]

@aleksbor no one in the community have started work on this what I know of. It is still label help wanted so that anyone in the community can pick this up.

[johlju]

Should be rolled into a new resource SqlProperties, or closed if SqlServerConfiguration supports settings this value. See also issue #1539 and issue #1538.

[Fiander]

personally i just use the code below to set al of this. The advantage of having it in a resource would be that you can have it dynamic what would be helpfull with thousends of VMs. But for the moment below works just fine.

    function Get-ObjectMembers {
        [CmdletBinding()]
        Param(
            [Parameter(Mandatory=$True, ValueFromPipeline=$True)]
            [PSCustomObject]$obj
        )
        $obj | Get-Member -MemberType NoteProperty | ForEach-Object {
            $key = $_.Name
            [PSCustomObject]@{Key = $key; Value = $obj."$key"}
        }
    }

    [CmdletBinding]
    function Get-FromJson
    {
        param(
            [Parameter(Mandatory=$true, Position=1)]
            [string]$Path
        )

        function Get-Value {
            param( $value )

            $result = $null
            if ( $value -is [System.Management.Automation.PSCustomObject] )
            {
                Write-Verbose "Get-Value: value is PSCustomObject"
                $result = @{}
                $value.psobject.properties | ForEach-Object { 
                    $result[$_.Name] = Get-Value -value $_.Value 
                }
            }
            elseif ($value -is [System.Object[]])
            {
                $list = New-Object System.Collections.ArrayList
                Write-Verbose "Get-Value: value is Array"
                $value | ForEach-Object {
                    $list.Add((Get-Value -value $_)) | Out-Null
                }
                $result = $list
            }
            else
            {
                Write-Verbose "Get-Value: value is type: $($value.GetType()) value: : $($value)"
                $result = $value
            }
            return $result
        }

        if (Test-Path $Path)
        {
            $json = Get-Content $Path -Raw
        }
        else
        {
            $json = '{}'
        }

        $hashtable = Get-Value -value (ConvertFrom-Json $json)

        return $hashtable
    }


    Configuration DSC_SqlServerConfiguration_JSON_Demo
    {
        Import-DscResource -ModuleName 'SqlServerDsc'

        node $AllNodes.NodeName
        {
            '{'+$node.SQLOptions+'}' | ConvertFrom-Json | Get-ObjectMembers | ForEach-Object {
                SqlServerConfiguration ('SqlServerConfiguration-{0}' -f $_.Key) {
                    Servername       = $node.NodeName
                    InstanceName     = $node.InstanceName
                    OptionName       = $_.Value.OptionName
                    OptionValue      = $_.Value.OptionValue
            }}
        }
    }


    #Eerste SQLOption. Moeten er nog veel bij komen.
    $SQLOptionsJsonDefault = @'
            "SQLConfigCLR":{"OptionName":"clr enabled","OptionValue":"1"},
            "SQLBackupCompression":{"OptionName":"backup compression default","OptionValue":"1"},
            "SQLContainedDbAuth":{"OptionName":"contained database authentication","OptionValue":"1"},
            "SQLC3Option":{"OptionName":"common criteria compliance enabled","OptionValue":"1"},
            "SQLBackupChecksum":{"OptionName":"backup checksum default","OptionValue":"1"},
            "SQLAdhocWorkload":{"OptionName":"optimize for ad hoc workloads","OptionValue":"1"},
            "SQLRemoteAdmin":{"OptionName":"remote admin connections","OptionValue":"1"},
            "SQLCostTresholdParallelism":{"OptionName":"Cost Threshold for Parallelism","OptionValue":"50"},
            "SQLMaxDegreeParallelism":{"OptionName":"max degree of parallelism","OptionValue":"4"}
    '@

    $ConfigurationData = @{
        AllNodes = @(
            @{
                NodeName        = 'localhost'
                InstanceName    = 'DSCSQLTEST'

                SQLOptions      = $SQLOptionsJsonDefault
            }
        )
    }