SQL Server SMO Query Performance (2014 vs 2017)

Question

I've got two identical databases, one on SQL Server 2014 and the other on 2017. When querying all tables and columns with SMO, I get a large difference in performance. Looking at profiler whilst it's running, I've noticed that in 2017, it adds an extra join to sys.periods, as you can see from the SQL produced by both below:

SQL Server 2017:

exec sp_executesql N'SELECT
                         clmns.column_id AS [ID],
                         clmns.name AS [Name]
                     FROM
                         sys.tables AS tbl
                     LEFT OUTER JOIN 
                         sys.periods as periods ON periods.object_id = tbl.object_id
                     LEFT OUTER JOIN 
                         sys.tables as historyTable ON historyTable.object_id = tbl.history_table_id
                     INNER JOIN 
                         sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
                     WHERE
                         (tbl.name = @_msparam_0 
                          AND SCHEMA_NAME(tbl.schema_id) = @_msparam_1)
                     ORDER BY
                         [ID] ASC',
            N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',
            @_msparam_0=N'ExampleTable',@_msparam_1=N'ExampleSchema'

SQL Server 2014:

EXEC sp_executesql N'SELECT
clmns.column_id AS [ID],
clmns.name AS [Name]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
WHERE
(tbl.name=@_msparam_0 and SCHEMA_NAME(tbl.schema_id)=@_msparam_1)
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'ExampleTable',@_msparam_1=N'ExampleSchema'

My understanding is that 2016 added temporal tables, which is why it's joining to sys.periods. However the duration for each of those queries jumps from 0ms to 80ms, which increases the overall running time by a lot.

My question is, is there a way to either combat this huge performance hit, or effectively tell SMO to not care about temporal tables and produce the SQL like it does in 2014? Temporal tables isn't something I am using, so I don't mind turning it off if that's an option.

Kevin

SQL Server SMO Query Performance (2014 vs 2017)

0 Answers