ResourcePoolAffinityInfo is null

スタック トレースに記載されている ResourcePool.get_ResourcePoolAffinityInfo と ResourcePoolAffinityInfo.SetSchedulerValues から始め、影響を受ける SQL インスタンスに対して PowerShell サンプルを実行して失敗を再現します。null アフィニティの場合を、動作するインスタンスと比較します。ResourcePool.Script() が重複キー例外を発生させずにこの条件を処理できれば完了です。

I'm really not sure if this is an issue with my instance or an issue with SMO but I'm happy to help with testing however I can. I'm assuming it's an issue with SMO because I'm receiving an exception for a situation I would expect to be handled within SMO in the case that this was normal to happen.

For some reason, out of 100 SQL Instances, just 1 Microsoft.SqlServer.Management.Smo.Server object is showing $server.ResourceGovernor.ResourcePools.ResourcePoolAffinityInfo as null for all ResourcePools. I have tried comparing every single resource governor related system view with all the instances that worked, and I see no differences that would indicate why it's null.

Here's the sample script I'm using:

$cs = 'Data Source=MYSERVERNAME;Database=master;Integrated Security=True;Encrypt=True;Trust Server Certificate=True;Application Name=DBADash;Multi Subnet Failover=True'
$cn = [Microsoft.Data.SqlClient.SqlConnection]::new($cs)
$sc = [Microsoft.SqlServer.Management.Common.ServerConnection]::new($cn)
$instance = [Microsoft.SqlServer.Management.Smo.Server]::new($sc)
$instance.ResourceGovernor.ResourcePools | select Name, ResourcePoolAffinityInfo

Which is returning:

Name     ResourcePoolAffinityInfo
----     ------------------------
default
internal

Whereas on all other instances I tested this against, I received:

Name     ResourcePoolAffinityInfo
----     ------------------------
default  Microsoft.SqlServer.Management.Smo.ResourcePoolAffinityInfo
internal Microsoft.SqlServer.Management.Smo.ResourcePoolAffinityInfo

The reason this is a problem for me is because an open source monitoring tool I use calls Microsoft.SqlServer.Management.Smo.ResourcePool.Script(). However, because ResourcePoolAffinityInfo is null, it is returning the following error:

Exception             : 
    Type           : System.Management.Automation.MethodInvocationException
    ErrorRecord    : 
        Exception             : 
            Type    : System.Management.Automation.ParentContainsErrorRecordException
            Message : Exception calling "Script" with "0" argument(s): "Script failed for  'default'. "
            HResult : -2146233087
        CategoryInfo          : NotSpecified: (:) [], ParentContainsErrorRecordException
        FullyQualifiedErrorId : FailedOperationException
        InvocationInfo        : 
            ScriptLineNumber : 6
            OffsetInLine     : 1
            HistoryId        : 114
            Line             : $instance.ResourceGovernor.ResourcePools[0].Script()
            Statement        : $instance.ResourceGovernor.ResourcePools[0].Script()
            PositionMessage  : At line:6 char:1
                               + $instance.ResourceGovernor.ResourcePools[0].Script()
                               + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            CommandOrigin    : Internal
        ScriptStackTrace      : at <ScriptBlock>, <No file>: line 6
    TargetSite     : 
        Name          : ConvertToMethodInvocationException
        DeclaringType : [System.Management.Automation.ExceptionHandlingOps]
        MemberType    : Method
        Module        : System.Management.Automation.dll
    Message        : Exception calling "Script" with "0" argument(s): "Script failed for  'default'. "
    Data           : System.Collections.ListDictionaryInternal
    InnerException : 
        Type             : Microsoft.SqlServer.Management.Smo.FailedOperationException
        SmoExceptionType : FailedOperationException
        Operation        : Script
        FailedObject     : [default]
        Message          : Script failed for  'default'. 
        HelpLink         : https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=17.100.18.0&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Script+ResourcePool&LinkId=20476
        TargetSite       : 
            Name          : EnumScriptImpl
            DeclaringType : [Microsoft.SqlServer.Management.Smo.SqlSmoObject]
            MemberType    : Method
            Module        : Microsoft.SqlServer.Smo.dll
        Data             : System.Collections.ListDictionaryInternal
        InnerException   : 
            Type       : System.ArgumentException
            Message    : An item with the same key has already been added. Key: 0
            TargetSite : 
                Name          : ThrowAddingDuplicateWithKeyArgumentException
                DeclaringType : [System.ThrowHelper]
                MemberType    : Method
                Module        : System.Private.CoreLib.dll
            Source     : System.Private.CoreLib
            HResult    : -2147024809
            StackTrace : 
   at System.Collections.Generic.Dictionary`2.TryInsert(TKey key, TValue value, InsertionBehavior behavior)
   at Microsoft.SqlServer.Management.Smo.NumaNodeCollection.get_NumaCollectionFromId()
   at Microsoft.SqlServer.Management.Smo.NumaNodeCollection.GetByID(Int32 numanodeId)
   at Microsoft.SqlServer.Management.Smo.ResourcePoolAffinityInfo.SetSchedulerValues()
   at Microsoft.SqlServer.Management.Smo.ResourcePoolAffinityInfo.PopulateDataTable()
   at Microsoft.SqlServer.Management.Smo.ResourcePoolAffinityInfo..ctor(ResourcePool parent)
   at Microsoft.SqlServer.Management.Smo.ResourcePool.get_ResourcePoolAffinityInfo()
   at Microsoft.SqlServer.Management.Smo.ResourcePool.GetAllParams(StringBuilder sb, ScriptingPreferences sp, Int32& count)
   at Microsoft.SqlServer.Management.Smo.ResourcePool.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptUrns(List`1 orderedUrns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(SqlSmoObject[] objects, ISmoScriptWriter writer)
   at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(SqlSmoObject[] objects)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImplWorker(ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImpl(ScriptingPreferences sp)
        Source           : Microsoft.SqlServer.Smo
        HResult          : -2146233088
        StackTrace       : 
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.EnumScriptImpl(ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptImpl(ScriptingPreferences sp)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptImpl()
   at CallSite.Target(Closure, CallSite, Object)
    Source         : System.Management.Automation
    HResult        : -2146233087
    StackTrace     : 
   at System.Management.Automation.ExceptionHandlingOps.ConvertToMethodInvocationException(Exception exception, Type typeToThrow, String methodName, Int32 numArgs, MemberInfo memberInfo)
   at CallSite.Target(Closure, CallSite, Object)
   at System.Dynamic.UpdateDelegates.UpdateAndExecute1[T0,TRet](CallSite site, T0 arg0)
   at System.Management.Automation.Interpreter.DynamicInstruction`2.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : FailedOperationException
InvocationInfo        : 
    ScriptLineNumber : 6
    OffsetInLine     : 1
    HistoryId        : 114
    Line             : $instance.ResourceGovernor.ResourcePools[0].Script()
    Statement        : $instance.ResourceGovernor.ResourcePools[0].Script()
    PositionMessage  : At line:6 char:1
                       + $instance.ResourceGovernor.ResourcePools[0].Script()
                       + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    CommandOrigin    : Internal
ScriptStackTrace      : at <ScriptBlock>, <No file>: line 6

My assumption here is that if it is possible for ResourcePoolAffinityInfo to be null, then I would expect SMO to be aware of that situation and would check for null prior to running Script(). But since it is not checking for that situation, I assume this is a bug, or at the very least, an unhandled exception.