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

首先使用 Server.SetDefaultInitFields 对 ActiveConnections 和 Databases 进行枚举，在 PowerShell 中复现，然后跟踪生成的 ActiveConnections 查询在哪里组装。将当前的 sys.sysprocesses 查询与 SQL Server 2022 上提出的替代方案进行比较，并确认数据库枚举不再出现报告的速度下降，以验证完成情况。

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