主页 / dba / 问题 / 155684
Accepted
Henrik Staun Poulsen
Asked: 2016-11-19 02:25:42 +0800 CST

database_scoped_configurations 中的错误

  • 772

我正在尝试从以下位置插入结果集：

SELECT * FROM sys.database_scoped_configurations

到临时表中，因为我想检查服务器上所有数据库的设置。所以我写了这段代码：

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

但是每个数据库只有一行，而不是我期望在每个数据库中运行普通选择的四行。

我知道有比使用 sp_MSForEachDB 更好的编码方法，我尝试了几种方法。但是每个数据库我仍然只得到一行。我在 SQL Server 2016 RTM 和 SP1 上都试过了

这是 SQL Server 2016 的错误，还是我做错了什么？

sql-server configuration

4 个回答

  1. Best Answer

    这是 SQL Server 2016 的错误吗？

    是的。这绝对不是正确的行为。我已在此处报告并已在 SQL Server 2016 SP2 CU9 中修复。

    正如Mikael Eriksson在评论中所说，sys.database_scoped_configurations并sys.dm_exec_sessions以格式的视图实现

    SELECT ...  
FROM OpenRowset(TABLE xxxx)

    但是比较下面的两个计划有一个明显的区别。

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

    在此处输入图像描述

    这两个查询的跟踪标志 8619 输出显示

    应用规则：EnforceHPandAccCard - x0-> Spool or Top (x0)

    SQL Server 显然无法确定 TVF 的源不是插入目标，因此它需要万圣节保护。

    在会话案例中，这被实现为一个首先捕获所有行的假脱机。通过在计划中database_scoped_configurations添加一个。本文讨论了万圣节保护TOP 1的使用。该文章还提到了一个未记录的跟踪标志来强制假脱机而不是按预期工作。TOPTOP

    DECLARE @database_scoped_configurations TABLE(x INT);

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

    使用TOP 1rather than spool 的一个明显问题是它会任意限制插入的行数。所以这只有在函数返回的行数 <=1 时才有效。

    最初的备忘录看起来像这样

    在此处输入图像描述

    将其与查询 2 的初始备忘录进行比较

    在此处输入图像描述

    如果我正确理解上面的内容，它认为第一个 TVF 最多可以返回一行，因此应用了不正确的优化。第二个查询的最大值设置为1.34078E+154( 2^512)。

    我不知道这个最大行数是从哪里得出的。也许是 DMV 作者提供的元数据？TOP(50)解决方法没有被重写也很奇怪，TOP(1)因为TOP(50)不会阻止万圣节问题的发生（尽管会阻止它无限期地继续）

    • 9

  2. 请停止使用sp_MSForEachDB。它不受支持，没有记录，而且有问题——这可能就是这里的问题。我的替代品在这里展示了同样的问题，但总的来说它使用起来更安全。

    对于这样的事情，我更喜欢生成动态 SQL 而不是将单个命令交给一个过程执行多次（即使是我的过程，我更信任它），这样我就可以简单地打印命令而不是执行它们，并且确保他们都将按照他们所说的去做。

    借用系统视图底层代码实现 a 的观察结果TOP (1)，我们可以这样尝试：

    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;

    请注意，我在这里没有使用USE，而是在sys目录视图前加上数据库名称。

    为什么视图以神奇的方式工作，我不知道；我不知道你会在这里得到一个好的答案，因为它可能需要来自 Microsoft 的评论（或任何有权访问源代码或愿意启动调试器的人）。

    • 6

  3. 感谢您报告此问题！

    这确实是查询优化器为sys.database_scoped_configurations目录视图生成计划的方式中的一个错误。我们将在 SQL Server 2016 的下一个更新和 Azure SQL 数据库中解决这个问题。

    作为解决方法，您可以在插入部分添加一个TOP子句以获得正确的计划，例如：SELECT

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

  4. 我同意这是非常奇怪的，并且是一个潜在的错误，但是例如，将 TOP(50) 添加到您的选择中实际上确实会返回所有行，因此至少可以让您继续。结果似乎来自系统表值函数 ([DB_SCOPED_CONFIG])，所以我无法真正判断发生了什么。

    我会密切关注此线程，看看“聪明”的人是否知道为什么会发生这种情况。

    • 3

相关问题