ResourcePoolAffinityInfo is null

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.

Commencez par ResourcePool.get_ResourcePoolAffinityInfo et ResourcePoolAffinityInfo.SetSchedulerValues, tous deux mentionnés dans la stack trace, et reproduisez l’échec avec l’exemple PowerShell sur une instance SQL affectée. Comparez le cas d’affinité null avec une instance fonctionnelle ; le travail est terminé lorsque ResourcePool.Script() gère la condition sans l’exception de clé dupliquée.