We have an application that uses the extended properties to hold columns meta data. The columns have extended properties to hold the field description, option values, position on screen etc. There is a view that queries these extended properties, which is queried once when the application starts.
Recently we upgraded from SQL Server 2008 to SQL Server 2016 and the problem is that this view is now much much slower, which freezes the application at start up.
CREATE VIEW dbo.VW_FIELD_METADATA
AS
SELECT
CAST(UPPER(o.NAME) AS VARCHAR) AS TABLE_NAME,
CAST(UPPER(c.name) AS VARCHAR) AS COLUMN_NAME,
CONVERT(VARCHAR(250), VarLabel.Value) AS ColDescr,
ValLabel.Name AS ValueName,
CONVERT(VARCHAR(250), ValLabel.Value) AS ValueLabel
FROM
SYSOBJECTS O
JOIN
SYSCOLUMNS C ON O.id = C.id
JOIN
SYS.EXTENDED_PROPERTIES VarLabel ON c.id = VarLabel.major_id
AND c.colid = VarLabel.minor_id
AND VarLabel.Name = 'VarLabel'
LEFT JOIN
(SELECT MAJOR_ID, MINOR_ID, NAME, VALUE
FROM SYS.EXTENDED_PROPERTIES
WHERE Name LIKE 'ValLabel%') ValLabel ON c.id = ValLabel.major_id
AND c.colid = ValLabel.minor_id
LEFT JOIN
SYS.EXTENDED_PROPERTIES Groep ON c.id = Groep.major_id
AND c.colid = Groep.minor_id
AND Groep.Name = 'Groep'
--LEFT JOIN
-- SYS.EXTENDED_PROPERTIES Minimum ON c.id = Minimum.major_id AND c.colid = Minimum.minor_id AND Minimum.Name = 'Minimum'
--LEFT JOIN
-- SYS.EXTENDED_PROPERTIES Maximum ON c.id = Maximum.major_id AND c.colid = Maximum.minor_id AND Maximum.Name = 'Maximum'
--etc there are 8 more JOINS
WHERE
(o.xtype = 'U' OR o.xtype = 'V')
GO
There are a lot of tables, columns and extended properties so this yields > 17000 rows
TABLE_NAME |COLUMN_NAME |ColDescr |ValueName |ValueLabel
------------+--------------+--------------------+------------+----------
PATIENT |PATNR |Registration number |NULL |NULL
PATIENT |FIRSTNAME |First name |NULL |NULL
PATIENT |SEX |Patient sex |ValLabel001 |1 = Male
PATIENT |SEX |Patient sex |ValLabel002 |2 = Female
etc.
On the old SQL Server 2008 is took less than one second, on the new SQL Server 2016 it takes up to two minutes, which causes the application to time-out.
Btw the actual query for the view is even longer, with about 8 extra LEFT JOINs, but then the query seems to get into a dead-lock altogether and never finishes, so I made it shorter here for testing purposes.
Anyway, I know we can fix this problem by setting the database to an older compatibility level, so Properties -> Options -> Compatibility level and set it to "SQL Server 2008 (100)" but that completely negates the server upgrade and blocks newer features.
My question is, is there a way make this query work while keeping the database in Compatibility level "SQL Server 2016 (130)" ? Why does querying these system tables take so much longer compared to the earlier SQL Server version?
(Btw, the new server is in a "Always On availability groups" but I don't know if that is relevant to this problem)
I also asked the question on the Microsoft SQL Server forum and someone pointed out that you can force the use of the so-called legacy cardinality estimator for a query.
So at the end add OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) like so:
..
WHERE
(o.xtype = 'U' OR o.xtype = 'V')
OPTION(USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
Using this hint increases the performance of this query dramatically.