błąd w konfiguracjach_danych_bazy_danych

Próbuję wstawić zestaw wyników z:

SELECT * FROM sys.database_scoped_configurations

do tabeli tymczasowej, ponieważ chcę sprawdzić ustawienia wszystkich baz danych na moim serwerze. Więc napisałem ten kod:

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

Ale wtedy będzie tylko jeden wiersz na bazę danych, a nie cztery wiersze, których oczekuję po uruchomieniu zwykłego wyboru w każdej bazie danych.

Wiem, że istnieją lepsze sposoby na kodowanie tego niż użycie sp_MSForEachDB, i próbowałem kilka. Ale nadal dostaję tylko jeden wiersz na bazę danych. Próbowałem tego zarówno na SQL Server 2016 RTM, jak i SP1

Czy to błąd w SQL Server 2016, czy robię coś złego?

Czy to błąd w SQL Server 2016?

Tak. Zdecydowanie nie jest to właściwe zachowanie. Zgłosiłem to tutaj i został naprawiony w SQL Server 2016 SP2 CU9 .

Jak mówi Mikael Eriksson w komentarzach sys.database_scoped_configurationsi sys.dm_exec_sessionssą realizowane jako widoki w formacie

SELECT ...  
FROM OpenRowset(TABLE xxxx)  

Jednak porównanie dwóch poniższych planów jest oczywistą różnicą.

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

Dane wyjściowe flagi śledzenia 8619 dla obu tych zapytań pokazują

Zastosuj regułę: EnforceHPandAccCard - x0-> Spool or Top (x0)

SQL Server najwyraźniej nie jest w stanie ustalić, czy źródło TVF nie jest również celem wstawiania, więc wymaga ochrony na Halloween.

W przypadku sesji zostało to zaimplementowane jako bufor, który najpierw przechwytuje wszystkie wiersze. W database_scoped_configurationsdodając a TOP 1do planu. Zastosowanie ochrony TOPna Halloween omówiono w tym artykule . W artykule wspomniano również o nieudokumentowanej fladze śledzenia, która wymusza buforowanie, a nie TOPdziała zgodnie z oczekiwaniami.

DECLARE @database_scoped_configurations TABLE(x INT);

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

Oczywistym problemem związanym z używaniem TOP 1zamiast szpuli jest to, że arbitralnie ogranicza liczbę wstawionych wierszy. Byłoby to prawidłowe tylko wtedy, gdy liczba wierszy zwróconych przez funkcję wynosiła <= 1.

Wstępna notatka wygląda następująco

Porównaj to z początkową notatką dla zapytania 2

Jeśli dobrze rozumiem powyższe, to wydaje się, że pierwszy TVF może zwrócić maksymalnie jeden wiersz i dlatego stosuje niepoprawną optymalizację. Maksimum dla drugiego zapytania jest ustawione na 1.34078E+154( 2^512).

Nie mam pojęcia, skąd pochodzi ta maksymalna liczba wierszy. Być może metadane dostarczone przez autora DMV? Dziwne jest również to, że TOP(50)obejście nie zostało przepisane, TOP(1)ponieważ TOP(50)nie zapobiegłoby wystąpieniu problemu z Halloween (choć zatrzymałoby go na czas nieokreślony)

Przestań używać sp_MSForEachDB. Jest nieobsługiwany, nieudokumentowany i zawiera błędy - co może być tutaj problemem. Mój zamiennik pokazuje tutaj ten sam problem, ale ogólnie rzecz biorąc, jest to bezpieczniejsze.

W przypadku takich rzeczy wolę generować dynamiczny SQL niż przekazywać pojedyncze polecenie do procedury wykonującej wiele razy (nawet mojej procedury, której ufam o wiele bardziej), w ten sposób mogę po prostu wydrukować polecenia zamiast ich wykonania, i upewnij się, że wszyscy zrobią to, co mówią.

Pożyczając z obserwacji, że kod leżący u podstaw widoku systemu implementuje a TOP (1), możemy spróbować w ten sposób:

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;

Zauważ, że nie używam USEtutaj, ale raczej poprzedź syswidok katalogu nazwą bazy danych.

Dlaczego widok działa w magiczny sposób, nie wiem; Nie wiem, czy uzyskasz tutaj dobrą odpowiedź, ponieważ prawdopodobnie wymaga ona komentarzy od firmy Microsoft (lub kogokolwiek, kto ma dostęp do kodu źródłowego lub chce uruchomić debuggera).

Dziękujemy za zgłoszenie tego problemu!

Jest to rzeczywiście błąd w sposobie, w jaki Optymalizator zapytań generuje plan sys.database_scoped_configurationswidoku katalogu. Zajmiemy się tym w jednej z kolejnych aktualizacji SQL Server 2016 oraz w bazie danych Azure SQL.

Aby obejść ten problem, możesz dodać TOPklauzulę do SELECTczęści wstawki, aby uzyskać odpowiedni plan, np .:

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

Zgadzam się, że jest to bardzo dziwny i potencjalny błąd, ale na przykład dodanie TOP (50) do twojego wyboru faktycznie zwraca wszystkie wiersze, więc przynajmniej byś zaczął. Wydaje się, że wynik pochodzi z systemowej funkcji wartości tabeli ([DB_SCOPED_CONFIG]), więc nie mogę tak naprawdę powiedzieć, co się dzieje.

Będę pilnować tego wątku, aby sprawdzić, czy „mądrzejsi” ludzie wiedzą, DLACZEGO to się dzieje.