Bug in Test-DbaMaxMemory with InstanceCount?

[SQLScott]

A few times now I have run the the Test-DbaMaxMemory cmdlet on some systems that have a single instance on them, but this cmdlet tells me I have 2 instances and recommends memory based on that. I dug into the code here in the repo, and I believe there is a bug on this line:

$instanceCount = ($serverService | Where-Object State -Like Running | Where-Object InstanceName | Group-Object InstanceName | Measure-Object Count).Count

I'll explain. The code first calls:

$serverService = Get-DbaService -ComputerName $instance -EnableException

And on the system I ran this on today, it returned the following:

ComputerName : TestServer
ServiceName : SQLServerReportingServices
ServiceType : SSRS
InstanceName : SSRS
DisplayName : SQL Server Reporting Services
StartName : NT SERVICE\SQLServerReportingServices
State : Running
StartMode : Automatic

ComputerName : TestServer
ServiceName : MsDtsServer150
ServiceType : SSIS
InstanceName :
DisplayName : SQL Server Integration Services 15.0
StartName : company\account
State : Running
StartMode : Automatic

ComputerName : TestServer
ServiceName : MSSQLSERVER
ServiceType : Engine
InstanceName : MSSQLSERVER
DisplayName : SQL Server (MSSQLSERVER)
StartName : company\account
State : Running
StartMode : Automatic

ComputerName : TestServer
ServiceName : SQLBrowser
ServiceType : Browser
InstanceName :
DisplayName : SQL Server Browser
StartName : NT AUTHORITY\LOCALSERVICE
State : Stopped
StartMode : Disabled

ComputerName : TestServer
ServiceName : SQLSERVERAGENT
ServiceType : Agent
InstanceName : MSSQLSERVER
DisplayName : SQL Server Agent (MSSQLSERVER)
StartName : company\account
State : Running
StartMode : Automatic

This line is then called:

$instanceCount = ($serverService | Where-Object State -Like Running | Where-Object InstanceName | Group-Object InstanceName | Measure-Object Count).Count

It groups on the InstanceName, and since there are two MSSQLSERVER objects that are running (the engine and SQL agent), it thinks there are two instances (if I am reading this right).

If we really wanted to know how many instances there are, wouldn't we want to know how many instances of the ENGINE there is, and therefore filter on ServiceType = Engine?

$instanceCount = ($serverService | Where-Object State -Like Running | Where-Object InstanceName | Where-Object ServiceType -Like Engine | Group-Object InstanceName | Measure-Object Count).Count

Ultimately, I have run this a few systems where I know there is a single SQL Server instance but this cmdlet returns a value of 2 for the instance count.

Interestingly enough, for further testing, I stopped the SQL Server agent and reran things and it still returned a value of 2. So maybe we could get away with something like this:

$instanceCount = ($serverService | Where-Object State -Like Running | Where-Object ServiceType -Like Engine | Group-Object InstanceName | Measure-Object Count).Count

Scott

[lowlydba]

This was fixed in #8249