Performance issue with SQLServer 2022

[lenv]

Search first

• I searched and no similar issues were found

Description

After moving from SQLServer 2019 to SQLServer 2022 we encounter a performance hit during application DB update. In particular the following query execution time went from less than 100 ms to 2.5 minutes
SELECT db_name(db_id('pedro_gen')) AS TABLE_CAT
,object_schema_name(c.object_id, db_id('pedro_gen')) AS TABLE_SCHEM
,object_name(c.object_id, db_id('pedro_gen')) AS TABLE_NAME
,c.name AS COLUMN_NAME
,is_filestream AS IS_FILESTREAM
,is_rowguidcol AS IS_ROWGUIDCOL
,CASE
WHEN c.is_identity = 'true'
THEN 'YES'
ELSE 'NO'
END AS IS_AUTOINCREMENT
,CAST([ep].[value] AS nvarchar) AS [REMARKS]
,t.name AS TYPE_NAME
,dc.name AS COLUMN_DEF_NAME
,dc.DEFINITION AS COLUMN_DEF
,CASE t.name
WHEN 'bigint'
THEN - 5
WHEN 'binary'
THEN - 2
WHEN 'bit'
THEN - 7
WHEN 'char'
THEN 1
WHEN 'date'
THEN 91
WHEN 'datetime'
THEN 93
WHEN 'datetime2'
THEN 93
WHEN 'datetimeoffset'
THEN - 155
WHEN 'decimal'
THEN 3
WHEN 'float'
THEN 8
WHEN 'image'
THEN - 4
WHEN 'int'
THEN 4
WHEN 'money'
THEN 3
WHEN 'nchar'
THEN - 15
WHEN 'ntext'
THEN - 16
WHEN 'numeric'
THEN 2
WHEN 'nvarchar'
THEN - 9
WHEN 'real'
THEN 7
WHEN 'smalldatetime'
THEN 93
WHEN 'smallint'
THEN 5
WHEN 'smallmoney'
THEN 3
WHEN 'text'
THEN - 1
WHEN 'time'
THEN 92
WHEN 'timestamp'
THEN - 2
WHEN 'tinyint'
THEN - 6
WHEN 'udt'
THEN - 3
WHEN 'uniqueidentifier'
THEN 1
WHEN 'varbinary'
THEN - 3
WHEN 'varbinary(max)'
THEN - 3
WHEN 'varchar'
THEN 12
WHEN 'varchar(max)'
THEN 12
WHEN 'xml'
THEN - 1
WHEN 'LONGNVARCHAR'
THEN 2009
ELSE 1111
END AS DATA_TYPE
,CASE
WHEN c.is_nullable = 'true'
THEN 1
ELSE 0
END AS NULLABLE
,10 AS NUM_PREC_RADIX
,c.column_id AS ORDINAL_POSITION
,c.scale AS DECIMAL_DIGITS
,c.max_length AS COLUMN_SIZE
,c.precision AS DATA_PRECISION
,c.is_computed AS IS_COMPUTED
FROM [pedro_gen].sys.columns c
INNER JOIN [pedro_gen].sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN [pedro_gen].[sys].[extended_properties] AS [ep] ON [ep].[class] = 1
AND [ep].[major_id] = c.object_id
AND [ep].[minor_id] = column_id
AND [ep].[name] = 'MS_Description'
LEFT OUTER JOIN [pedro_gen].sys.default_constraints dc ON dc.parent_column_id = c.column_id
AND dc.parent_object_id = c.object_id
AND type_desc = 'DEFAULT_CONSTRAINT'
WHERE object_schema_name(c.object_id, db_id('pedro_gen')) = 'iongridpedro'
AND object_name(c.object_id, db_id('pedro_gen')) = 'DATABASECHANGELOG'
ORDER BY object_schema_name(c.object_id, db_id('pedro_gen'))
,object_name(c.object_id, db_id('pedro_gen'))
,c.column_id

   select * FROM [pedro_gen].sys.columns c where object_id = 8
   select OBJECT_NAME(8) FROM master.sys.objects;  

Microsoft tech support suggested the following change

SELECT db_name(db_id('pedro_gen')) AS TABLE_CAT
,object_schema_name(c.object_id, db_id('pedro_gen')) AS TABLE_SCHEM
,object_name(c.object_id, db_id('pedro_gen')) AS TABLE_NAME
,c.name AS COLUMN_NAME
,is_filestream AS IS_FILESTREAM
,is_rowguidcol AS IS_ROWGUIDCOL
,CASE
WHEN c.is_identity = 'true'
THEN 'YES'
ELSE 'NO'
END AS IS_AUTOINCREMENT
,CAST([ep].[value] AS nvarchar) AS [REMARKS]
,t.name AS TYPE_NAME
,dc.name AS COLUMN_DEF_NAME
,dc.DEFINITION AS COLUMN_DEF
,CASE t.name
WHEN 'bigint'
THEN - 5
WHEN 'binary'
THEN - 2
WHEN 'bit'
THEN - 7
WHEN 'char'
THEN 1
WHEN 'date'
THEN 91
WHEN 'datetime'
THEN 93
WHEN 'datetime2'
THEN 93
WHEN 'datetimeoffset'
THEN - 155
WHEN 'decimal'
THEN 3
WHEN 'float'
THEN 8
WHEN 'image'
THEN - 4
WHEN 'int'
THEN 4
WHEN 'money'
THEN 3
WHEN 'nchar'
THEN - 15
WHEN 'ntext'
THEN - 16
WHEN 'numeric'
THEN 2
WHEN 'nvarchar'
THEN - 9
WHEN 'real'
THEN 7
WHEN 'smalldatetime'
THEN 93
WHEN 'smallint'
THEN 5
WHEN 'smallmoney'
THEN 3
WHEN 'text'
THEN - 1
WHEN 'time'
THEN 92
WHEN 'timestamp'
THEN - 2
WHEN 'tinyint'
THEN - 6
WHEN 'udt'
THEN - 3
WHEN 'uniqueidentifier'
THEN 1
WHEN 'varbinary'
THEN - 3
WHEN 'varbinary(max)'
THEN - 3
WHEN 'varchar'
THEN 12
WHEN 'varchar(max)'
THEN 12
WHEN 'xml'
THEN - 1
WHEN 'LONGNVARCHAR'
THEN 2009
ELSE 1111
END AS DATA_TYPE
,CASE
WHEN c.is_nullable = 'true'
THEN 1
ELSE 0
END AS NULLABLE
,10 AS NUM_PREC_RADIX
,c.column_id AS ORDINAL_POSITION
,c.scale AS DECIMAL_DIGITS
,c.max_length AS COLUMN_SIZE
,c.precision AS DATA_PRECISION
,c.is_computed AS IS_COMPUTED
FROM [pedro_gen].sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
AND o.name = 'DATABASECHANGELOG'
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
AND s.name = 'iongridpedro'
INNER JOIN [pedro_gen].sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN [pedro_gen].[sys].[extended_properties] AS [ep] ON [ep].[class] = 1
AND [ep].[major_id] = c.object_id
AND [ep].[minor_id] = column_id
AND [ep].[name] = 'MS_Description'
LEFT OUTER JOIN [pedro_gen].sys.default_constraints dc ON dc.parent_column_id = c.column_id
AND dc.parent_object_id = c.object_id
AND dc.type_desc = 'DEFAULT_CONSTRAINT'
ORDER BY TABLE_SCHEM, TABLE_NAME, c.column_id

Steps To Reproduce

Use performance monitor to check query execution time.

Expected/Desired Behavior

Manually executing modified query proves restored performance.

Liquibase Version

No response

Database Vendor & Version

SQLServer2022

Liquibase Integration

No response

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

Windows 11

Additional Context

No response

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[kevin-atx]

@lenv - thank you for submitting this issue. When you get a chance, could you share the following information:

• What version of Liquibase are you using?
• Are you running Liquibase from the CLI, Maven, Java API, or something else?

[lenv]

Liquibase is 4.23.2
Java API is being used.
The real issue for slow down is RBAC implementation in SQL Server 2022 and the way it handles non admin user.
But proposed query eliminates RBAC overhead.

[kevin-atx]

Thank you for the additional information (and for the proposed query change). I'm forwarding this to development.