bug in database_scoped_configurations

I'm trying to insert the result set from:

SELECT * FROM sys.database_scoped_configurations

into a temp table, because I want to check the settings for all databases on my server.
So I wrote this code:

DROP TABLE IF EXISTS #h
CREATE TABLE #h(dbname sysname, configuration_id INT, name sysname,     value SQL_VARIANT,  value_for_secondary SQL_VARIANT)
EXEC sys.sp_MSforeachdb 'USE ?; insert into #h(dbname, configuration_id, name, value,value_for_secondary)  SELECT ''?'' as dbname, * FROM sys.database_scoped_configurations  D'
SELECT * FROM #h H

But then there will only be one row per database, not the four rows that I expect from running a plain select in each database.

I know there are better ways to code this than using sp_MSForEachDB, and I tried several. But I still only get one row per database.
I've tried this on both SQL Server 2016 RTM and SP1

Is this a bug with SQL Server 2016, or am I doing something wrong?

Is this a bug with SQL Server 2016?

Yes. Definitely this is not correct behaviour. I have reported it here and is fixed in SQL Server 2016 SP2 CU9.

As Mikael Eriksson says in the comments sys.database_scoped_configurations and sys.dm_exec_sessions are implemented as views in the format

SELECT ...  
FROM OpenRowset(TABLE xxxx)

However comparing the two plans below there is an obvious difference.

DBCC TRACEON(3604);

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );

DECLARE @dm_exec_sessions TABLE(x INT);

INSERT INTO @dm_exec_sessions
SELECT session_id
FROM   sys.dm_exec_sessions
OPTION (QUERYTRACEON 8608, QUERYTRACEON 8615, QUERYTRACEON 8619, QUERYTRACEON 8620 );

Trace flag 8619 output for both of these queries shows


Apply Rule: EnforceHPandAccCard - x0-> Spool or Top (x0)

SQL Server is apparently not able to ascertain that the source for the TVF isn't also the insert target so it requires Halloween protection.

In the sessions case this was implemented as a spool that captures all rows first. In the database_scoped_configurations by adding a TOP 1 to the plan.
The use of TOP for Halloween protection is discussed in this article. The article also mentions an undocumented trace flag to force a spool rather than TOP that works as expected.

DECLARE @database_scoped_configurations TABLE(x INT);

INSERT INTO @database_scoped_configurations
SELECT configuration_id
FROM   sys.database_scoped_configurations
OPTION (QUERYTRACEON 8692)

An obvious problem with using TOP 1 rather than a spool is that it will arbitrarily limit the number of rows inserted. So this would only be valid if the number of rows returned by the function was <=1.

The initial memo looks like this

Compare this with the initial memo for query 2

If I understand the above correctly it thinks that the first TVF can return a maximum of one row and so applies an incorrect optimisation. The Max for the second query is set to 1.34078E+154 (2^512).

I've no idea where this maximum rowcount is derived from. Perhaps metadata supplied by the author of the DMV? It's also odd that the TOP(50) workaround doesn't get rewritten to TOP(1) because TOP(50) wouldn't prevent the Halloween issue from occurring (though would stop it continuing indefinitely)

StackExchange Database Administrators Q#155684, answer score: 9