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

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

Bắt đầu bằng việc tái hiện trong PowerShell sử dụng Server.SetDefaultInitFields cho việc liệt kê ActiveConnections và Databases, sau đó truy vết nơi truy vấn ActiveConnections được tạo ra được lắp ráp. So sánh truy vấn sys.sysprocesses hiện tại với phương án thay thế được đề xuất trên SQL Server 2022, và xác nhận hoàn tất bằng cách kiểm tra rằng việc liệt kê cơ sở dữ liệu không còn cho thấy tình trạng chậm đã được báo cáo.