Severe performance degradation in SQL Server 2022 when SMO enumerates databases with ActiveConnections due to sys.sysprocesses query

Comienza con la reproducción en PowerShell usando Server.SetDefaultInitFields para la enumeración de ActiveConnections y Databases; luego rastrea dónde se ensambla la consulta generada de ActiveConnections. Compara la consulta actual de sys.sysprocesses con la alternativa propuesta en SQL Server 2022 y valida la finalización confirmando que la enumeración de bases de datos ya no muestra la ralentización reportada.

Hello,
We've had to add a workaround for an issue that was introduced in 2022.
Please fix and we will update our codebase.

https://github.com/dataplat/dbatools/pull/9908

Description

SQL Server 2022 has severe performance degradation when SMO enumerates databases with ActiveConnections in the default init fields. The same query that takes ~1 second on SQL Server 2016 takes 20+ seconds on SQL Server 2022.

Root Cause

The generated query includes:

(select count(*) from master.dbo.sysprocesses p where dtb.database_id=p.dbid) AS [ActiveConnections]

This subquery for ActiveConnections uses the deprecated sys.sysprocesses compatibility view, which has severe performance issues on SQL Server 2022, particularly when querying hundreds of databases.

Workaround Found

Setting the legacy cardinality estimator on the master database reduces query time from 20s to 1s, suggesting this is a query optimizer regression in SQL Server 2022.

Environment

• SQL Server Version: SQL Server 2022 (VersionMajor 16)
• SMO Version: Multiple versions tested (including 17.100.23.0)
• Number of Databases: 190+ databases significantly amplifies the issue
• Query Execution: Each database enumeration includes this subquery, causing exponential slowdown

Proposed Solutions

1. Short-term: Update SMO to use sys.dm_exec_sessions instead of sys.sysprocesses
2. Medium-term: Fix the cardinality estimation issue for sys.sysprocesses queries in SQL Server 2022
3. Long-term: Remove sys.sysprocesses entirely as it's been deprecated since SQL Server 2008

Reproduction

$server = New-Object Microsoft.SqlServer.Management.Smo.Server "SQL2022Instance"
$initFields = New-Object System.Collections.Specialized.StringCollection
[void]$initFields.AddRange(@('ActiveConnections'))
$server.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.Database], $initFields)

# This will be very slow on SQL 2022 with 100+ databases
Measure-Command { $server.Databases | Out-Null }

Impact

• Affects all SMO-based tooling (SSMS, Azure Data Studio, dbatools, custom scripts)
• Only manifests when user has VIEW SERVER STATE permission
• Severely impacts automation and monitoring tools
• Issue affects SQL Server 2022 RTM and remains after applying latest CUs

References

• Original issue: dataplat/dbatools#9282
• Deprecated view documentation: https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql