6

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.

5
  • You’ll get a full refund for reporting a bug. The reason to open the support case is so that the support folks can see what else is going on at the same time on the system, which helps with creating a fix for a future CU or GDR release. Hope that helps. May 31, 2023 at 7:16
  • I can't repro 13 seconds. Used your exact repro. SA: CPU time = 62 ms, elapsed time = 126 ms. slowsystables_reader: CPU time = 265 ms, elapsed time = 396 ms. I also tried SQL19 and it was faster than 2022, but again 2022 didn't take 13 seconds. Jun 1, 2023 at 13:54
  • It was on a local VM, used the MS documentation to install. Does this need to be docker or something else? Jun 1, 2023 at 17:11
  • @SeanGallardy No, I used ubuntu 20.04 server and then aptitude from deb [arch=amd64] https://packages.microsoft.com/ubuntu/20.04/mssql-server-2022 focal main
    – final
    Jun 2, 2023 at 4:43
  • 2
    We reproduced this problem on a pure Windows SQL environment use xxxxx execute as login = 'yyy\zzz' SELECT * into #temp FROM sys.columns revert -- 31 seconds 2022 -- 2 seconds 2016
    – JC-Aus
    Dec 4, 2023 at 5:43

2 Answers 2

3

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
4
  • I'll test this as soon as I'm able to update the SQL Server in question to the latest CU - will take a week or so
    – final
    Dec 6, 2023 at 10:01
  • @final Only CU5 is required, CU10 is latest
    – Paul White
    Dec 6, 2023 at 11:29
  • This problem appears to occur on windows too, and it's not fully fixed by traceflag 12502. Is it really fixed in linux? We observe that a simple query on sys.computed_columns takes 1023ms on sql2022; it takes 161ms with traceflag 12505; 17ms when run as a db_owner; yet only 6ms on sql2019. Mar 5 at 13:21
  • For completeness, there is also another scenario where you might run into this. When you discover stored procedure parameters while using Enterprise Library in a .Net application, you'll face slowness which is caused by the same problem and also fixed with the same traceflag. I've written a blogost about it. It's in Dutch. To Summarize: .Discoverparameters() calls the system sp sp_procedure_params_100_managed, which in turn starts making multiple requests to the windows registry. twintos.com/artikel/preemptive_os_queryregistry-waits-oplossen Mar 10 at 21:23
-2
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.