9

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?

  • bug has been fixed, at least in Microsoft SQL Server 2017 (RTM-CU15-GDR) – Henrik Staun Poulsen Aug 27 at 9:19
8

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 );

enter image description here

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

enter image description here

Compare this with the initial memo for query 2

enter image description here

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)

6

Please stop using sp_MSForEachDB. It's unsupported, undocumented, and it's buggy - which may be the problem here. My replacement demonstrates the same problem here, but in general it's a safer thing to use.

For things like this I prefer to generate dynamic SQL than hand off a single command to a procedure to execute multiple times (even my procedure, which I trust a lot more), this way I can simply print the commands instead of executing them, and make sure they're all going to do what they say.

Borrowing from the observation that the code underlying the system view implements a TOP (1), we can try this way:

DROP TABLE IF EXISTS #h;

CREATE TABLE #h(dbname sysname, configuration_id INT, name sysname, 
  value SQL_VARIANT,  value_for_secondary SQL_VARIANT);

DECLARE @sql nvarchar(max) = N'', @base nvarchar(max) = N'insert into #h
  (dbname, configuration_id, name, value,value_for_secondary)  SELECT TOP ($c$) 
  $db$ as dbname, * FROM $qdb$.sys.database_scoped_configurations;';

SELECT @sql += REPLACE(REPLACE(REPLACE(@base, N'$qdb$', QUOTENAME(name)), 
  N'$db$', CHAR(39) + name + CHAR(39)), N'$c$', RTRIM(COUNT(*) OVER()))
FROM sys.databases WHERE state = 0;

PRINT @sql;
EXEC sys.sp_executesql @sql;
SELECT * FROM #h;

Notice that I don't use USE here, but rather prefix the sys catalog view with the database name.

Why the view works in magical ways, I don't know; I don't know that you'll get a good answer here, since it likely requires comments from Microsoft (or anyone with access to source code, or willing to fire up a debugger).

  • that was the first of the several methods I tried, but I didn't think I could use that sproc in the example. – Henrik Staun Poulsen Nov 18 '16 at 14:27
6

Thank you for reporting this issue!

This is indeed a bug in the way the Query Optimizer generates a plan for the sys.database_scoped_configurations catalog view. We will address this on one of the next updates of SQL Server 2016 and in Azure SQL Database.

As a workaround, you can add a TOP clause on the SELECT part of your insert to get the correct plan, e.g.:

DECLARE @database_scoped_configurations TABLE(x INT); 
INSERT INTO @database_scoped_configurations 
SELECT **TOP 100** configuration_id 
FROM sys.database_scoped_configurations 
3

I agree that this is very strange and a potential bug, but adding a TOP(50), for example, to your select actually does return all of the rows, so that would at least get you going. The result seems to be coming from a system Table Value Function ([DB_SCOPED_CONFIG]), so I can't really tell what's going on.

I'll be keeping any eye on this thread to see if 'smarter' people know WHY this is happening.

  • Do you get only the MAXDOP row for each database? – Dan Guzman Nov 18 '16 at 13:31
  • @DanGuzman - yes The select by itself works just fine (without all of the foreach stuff, just on a single database). It's when you add the Insert into that produces the weird behavior – Scott Hodgin Nov 18 '16 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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