MSSQL Server 2022 on Linux: sys.* tables very slow for non-db_owner users

Querying sys-tables, for instance sys.columns, sys.indexes or sys.tables, has become significantly slower on SQL Server 2022 on Linux for users which are not db_owner.

To reproduce, take a SQL Server 2022 on Linux (I've used 16.0.4035 which is the latest version as of this writing) and execute the following commands. It creates a dummy database with 500 tables with 10 columns each, such that sys.columns for this database gets about 5000 records. You can skip creating the dummy database if you have a database with a total of >1000 columns at hand.
CREATE DATABASE SLOWSYSTABLES
GO

USE SLOWSYSTABLES

DECLARE @i int
DECLARE @createTable nvarchar(max)
SET @i = 0
WHILE (@i

Now, I execute the following query:
SET STATISTICS TIME on
USE SLOWSYSTABLES
SELECT * FROM sys.columns


On my system (Intel i7-10700k, 32GB RAM, M.2 SSD disk), the execution time as sa or any user with db_owner permissions is about 100ms. But if slowsystables_reader runs the query, the execution time is 13 seconds. SQL Server uses 100% of the available CPUs for the entire 13 seconds.

(5048 rows affected)

 SQL Server Execution Times:
   CPU time = 7138 ms,  elapsed time = 13194 ms.

The issue disappears if slowsystables_reader is added to the db_owner database role and immediately reappears if the user is removed from the role again.

This issue is not present in SQL Server 2019 on Linux or 2019/2022 on Windows.

I can reproduce this on a freshly installed SQL Server 2022 on a freshly installed Ubuntu 20.04 Server or Debian bullseye. I also tried it on two different systems (both VMs, one Hypver-V, one KVM backed, but with different underlying hardware). The sole queries the SQL Server has ever executed are the ones above.

I tried updating system table stats, but no cigar.

Unfortunately, I don't have a Microsoft Support Plan and hence cannot open a support case with Microsoft.

I saw in my execution plan that SQL Server is spending nearly 100% of its time on the h

We are able to reproduce the issue in SQL Server 2022.
SQL Server 2022 CU7 (48 cpu)

• For our trouble database, lots of permissions

• Run as 'domain end user'

• SELECT * FROM sys.database_permissions

returns 2698 rows - Runtime of 1 min 6 seconds.

• Now run as me (sysadmin privilege – short circuit has_access)

returns 212,887 rows in 2 seconds.

SQL Server 2016 SP3-CU1 (36 cpu)

• Backup restore trouble database to this environment, lots of permissions

• Run as 'domain end user'

• SELECT * FROM sys.database_permissions

returns 2409 rows - Runtime of 3 seconds.

• Now run as me (sysadmin privilege – short circuit has_access)

returns 212,711 rows in 1 seconds.

SQL Server 2019 (12 cpu)

• Backup restore trouble database to this environment, lots of permissions

• Run as 'domain end user'

• SELECT * FROM sys.database_permissions

returns 2873 rows - Runtime of 5 seconds.

• Now run as me (sysadmin privilege – short circuit has_access)

returns 212,711 rows in 2 seconds.

SQL Server 2022 CU10 (4 cpu) sandbox environment, no workload

• Backup restore trouble database to this environment, lots of permissions

• Run as 'domain end user'

• SELECT * FROM sys.database_permissions

returns 2701 rows - Runtime of 34 seconds.

• Now run as me (sysadmin privilege – short circuit has_access)

returns 212,732 rows in 4 seconds.

SQL Server 2016 and 2019 show very little performance penalty for end user permission lookup as part of a select on sys_permission or sys_columns or other system table, but SQL Server 2022 shows a massive performance impact for the same query.

This is due in part to a database with a large number of permissions (200k) however this was not an issue before we upgraded to 2022 as is shown in the 2016 numbers.
Fix

Turning on trace flag 12502 in SQL Server 2022 appears to resolve the issue. Appears to be a bug in SQL Server that we have to "disable external authorization policies" as a workaround. I hope this can help someone else out there.

2351584 - Fixes an issue where high PREEMPTIVE_OS_QUERYREGISTRY waits occur. To apply this fix, you need to turn on trace flag 12502, which is used to disable external authorization policies for on-premises SQL Server instances.

Cumulative update 5 for SQL Server 2022 (KB5026806)

When the trace flag is off, and we run our test queries, we can monitor thousands (411k to be precise) of hits to the local registry looking for an Azure Purview related configuration. Enabling the flag stops the registry hit and makes our query run in seconds again.
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL16.DEV\MSSQLServer\PurviewConfig
Result NAME NOT FOUND


StackExchange Database Administrators Q#327285, answer score: 3