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

Question

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 < 500)
BEGIN
    SET @createTable = 'CREATE TABLE dummy_' + CAST(@i as nvarchar) + '(Col1 int not null identity(1,1) primary key, Col2 int, Col3 int, Col4 int, Col5 int, Col6 int, Col7 int, Col8 int, Col9 int, Col10 int)'
    SET @i = @i + 1
    exec sp_executesql @createTable
END
CREATE LOGIN slowsystables_reader WITH password='Asdfasdf1', check_policy=off
CREATE USER slowsystables_reader FOR LOGIN slowsystables_reader
ALTER ROLE db_datareader ADD MEMBER slowsystables_reader

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 has_access function, and AFAIK it's "known" that has_access is short-circuited if the user is db_owner. So this is the same root case as Query against sys.schemas and sys.synonyms runs very slow for one user, but AFAIK I have absolutely no influence on has_access and I can't really rewrite/optimize SELECT * FROM sys.columns.

The huge difference between SQL Server 2019 and 2022 and between Windows and Linux is what's strange about this. We only noticed this issue because SELECT * FROM sys.columns ran absolutely fine on 2019 and suddenly timed out on 2022.

score 3 · Answer 1 · edited Dec 06 '23 at 10:42

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

score -2 · Answer 2 · answered Jul 17 '23 at 14:43

USE [master]
GO

-- Create a test database
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'SLOWSYSTABLES')
BEGIN
    CREATE DATABASE [SLOWSYSTABLES]
END
GO

USE [SLOWSYSTABLES]
GO

-- Create a dummy user
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'slowsystables_reader')
BEGIN
    CREATE LOGIN [slowsystables_reader] WITH PASSWORD = 'Asdfasdf1', CHECK_POLICY = OFF
    CREATE USER [slowsystables_reader] FOR LOGIN [slowsystables_reader]
    ALTER ROLE [db_datareader] ADD MEMBER [slowsystables_reader]
END
GO

-- Create a table with identity column and 10 additional columns
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'dummy')
BEGIN
    CREATE TABLE [dbo].[dummy] (
        [Col1] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
        [Col2] INT,
        [Col3] INT,
        [Col4] INT,
        [Col5] INT,
        [Col6] INT,
        [Col7] INT,
        [Col8] INT,
        [Col9] INT,
        [Col10] INT
    )
END
GO

-- Populate the table with dummy data
DECLARE @i INT = 1
WHILE (@i <= 500)
BEGIN
    INSERT INTO [dbo].[dummy] ([Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [Col8], [Col9], [Col10])
    VALUES (@i, @i, @i, @i, @i, @i, @i, @i, @i)
    SET @i = @i + 1
END
GO

-- Update statistics
EXEC sp_updatestats
GO

-- Query sys.columns
USE [SLOWSYSTABLES]
GO

SET STATISTICS TIME ON

-- Run the query as slowsystables_reader
EXECUTE AS USER = 'slowsystables_reader'

SELECT * FROM sys.columns

REVERT

SET STATISTICS TIME OFF

The script includes the command EXEC sp_updatestats to update statistics for improved query performance. creates the test database 'SLOWSYSTABLES' only if it doesn't already exist.

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

2 Answers2

SQL Server 2022 CU7 (48 cpu)

SQL Server 2016 SP3-CU1 (36 cpu)

SQL Server 2019 (12 cpu)

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

Fix