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

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

dataplat/dbatools#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: Get-DbaDatabase significantly slower on SQL2022 vs. SQL2016 when executing user is sysadmin dataplat/dbatools#9282
• Deprecated view documentation: https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysprocesses-transact-sql