SQL 重啟後，CTE 在 SQL Server 2019 中導致短暫的無效對象名稱錯誤
我們在 SQL Server 2016 數據庫中有一些已經投入生產一段時間的 SQL 程式碼；但它在重新啟動 SQL Server 後的第一個小時左右（從 5-10 分鐘到一個小時或更長時間，可能取決於 SQL Server 中的活動級別）在 SQL Server 2019 數據庫中引發錯誤。錯誤是 CTE（公用表表達式）的“無效對象名稱”。
我們在 SQL Server 2016 中有一個包含多個數據庫的生產環境。我們現在已經使用 SQL Server 2019 建立了一個新的開發/測試環境（在 Windows Server 2016 機器上，具有 24GB 的 RAM 和 4 個 CPU 核心），以便我們可以測試使用 SQL Server 2019。此測試伺服器上的數據庫是從生產備份還原的生產數據庫副本。測試環境中的所有數據庫都將兼容級別設置為 150 (SQL Server 2019)。
每天清晨，我們開始看到一些使用 CTE 的函式存在一些問題，這些函式會引發如下錯誤：
SqlException (0x80131904): Invalid object name 'CTEuniqueName'.] Msg 208, Level 16, State 1, Procedure ufn_FunctionName, Line 28 [Batch Start Line 0] Invalid object name 'CTEuniqueName'.錯誤在短時間內停止發生，直到第二天早上才再次發生。
該錯誤發生在一對依次呼叫的儲存過程中，這兩個儲存過程都呼叫了相同的（使用者定義的 SQL）函式。通過一些測試，我了解到有時我可以通過呼叫函式，然後只執行函式中的程式碼塊來導致相同的錯誤。
我還發現我可以通過重新啟動 SQL Server 實例並呼叫函式或程式碼塊來始終導致錯誤。這可能也是它只是在清晨失敗的原因 - 在此之前的幾個小時 SQL Server 實例上沒有任何活動，因此它進入了空閒模式或關閉了它的程序。
在反複呼叫函式或程式碼後，在某些時候它會成功而不會引發錯誤，之後，它似乎繼續工作（直到我再次重新啟動 SQL Server 實例）。
如果我將包含此函式的數據庫更改為“SQL Server 2016”兼容模式，則該函式始終會成功，即使在重新啟動 SQL Server 實例之後也是如此。因此，這似乎是 SQL Server 2019 特有的問題。
函式中的程式碼如下所示（使用匿名名稱），函式包含 2 個單獨的“IF”塊，類似於以下內容；但是我可以只使用以下程式碼塊導致錯誤。此程式碼中的 2 個視圖是同一 SQL Server 實例中其他數據庫的視圖。
DECLARE @MyID INT = 150589; DECLARE @MyType VARCHAR(25) = 'Test'; DECLARE @ExpirationDate DATE; BEGIN IF @MyType = 'Test' BEGIN DECLARE @Test1 INT; WITH CTEuniqueName(PersonID, DateComplete) AS (SELECT T1.PersonID, MAX(T2.DateComplete) AS DateComplete FROM dbo.TABLE1 AS T1 WITH(NOLOCK) LEFT JOIN dbo.VIEW2 AS T2 WITH(NOLOCK) ON T2.ID = T1.ID WHERE T1.ID = @MyID AND T2.SecondID IN(SELECT SecondID FROM dbo.TABLE3 WITH(NOLOCK) WHERE Name = 'TEST') GROUP BY T1.PersonID) SELECT @ExpirationDate = CASE WHEN V3.TimeFrame > 0 THEN DATEADD(DAY, TimeFrame, CONVERT(DATE, CTE1.DateComplete)) ELSE NULL END FROM CTEuniqueName AS CTE1 INNER JOIN dbo.VIEW2 AS V2 WITH(NOLOCK) ON V2.ID = CTE1.ID AND V2.DateComplete = CTE1.DateComplete INNER JOIN dbo.VIEW3 AS V3 WITH(NOLOCK) ON V3.QuizID = V2.QuizID WHERE TS.SecondID IN(SELECT SecondID FROM dbo.TABLE3 WITH(NOLOCK) WHERE Name = 'TEST'); END; END;此外，在啟動後發生錯誤的時間段內，如果我在 SSMS 視窗中執行 2 個儲存過程（呼叫上述函式，並且兩個過程都返回一個結果集），則這些過程成功返回各自的結果集在 SSMS 視窗中，然後 SSMS 切換到消息面板並顯示“無效對象名稱”錯誤。
有誰知道為什麼這可能僅在 SQL Server 2019 中間歇性失敗（並且似乎僅在 SQL Server 啟動或一段空閒時間之後）？或者，任何人都可以建議如何解決此類問題？我試過檢查 SQL 錯誤日誌；我也嘗試過執行包含函式呼叫的 Profiler - 這些都沒有提供任何線索。
附加資訊：
- 執行
DBCC FREESYSTEMCACHE(N'ALL');確實會導致相同的錯誤暫時再次發生（與重新啟動伺服器的方式相同）- 該函式不可內聯（由於 CTE），因此標量 UDF 內聯似乎不是問題的原因
- 發生這種情況時，SQL Server 錯誤日誌中沒有錯誤
- 我嘗試在同一實例上使用新創建的數據庫來重現該問題，僅使用函式所需的表，但這些函式總是在新數據庫中成功完成
最初，這似乎是與 SQL Server 2019 中添加的新標量 UDF 內聯功能相關的錯誤，因為您提到禁用內聯解決了該問題。進一步檢查，由於函式定義中存在 CTE，因此無法內聯該函式。
這是我（失敗的）重現該問題的嘗試：
USE [master]; GO DROP DATABASE IF EXISTS [256861OtherDatabase]; GO CREATE DATABASE [256861OtherDatabase]; GO USE [256861OtherDatabase]; GO CREATE TABLE dbo.TableForView2 ( ID int IDENTITY(1,1) NOT NULL, DateComplete datetime NOT NULL, SecondID int NOT NULL, QuizID int NOT NULL, CONSTRAINT PK_TableForView2 PRIMARY KEY (Id) ); GO CREATE TABLE dbo.TableForView3 ( ID int IDENTITY(1,1) NOT NULL, Timeframe int NOT NULL, QuizID int NOT NULL, CONSTRAINT PK_TableForView3 PRIMARY KEY (Id) ); GO USE [master]; GO DROP DATABASE IF EXISTS [256861]; GO CREATE DATABASE [256861]; GO USE [256861]; GO CREATE TABLE dbo.TABLE1 ( ID int IDENTITY(1,1) NOT NULL, PersonID int NOT NULL, CONSTRAINT PK_TABLE1 PRIMARY KEY (Id) ); GO CREATE VIEW dbo.View2 AS SELECT ID, DateComplete, SecondID, QuizID FROM [256861OtherDatabase].dbo.TableForView2 d GO CREATE TABLE dbo.TABLE3 ( ID int IDENTITY(1,1) NOT NULL, SecondID int NOT NULL, [Name] varchar(50) NOT NULL, CONSTRAINT PK_TABLE3 PRIMARY KEY (Id) ); GO CREATE VIEW dbo.View3 AS SELECT ID, Timeframe, QuizID FROM [256861OtherDatabase].dbo.TableForView3 d GO CREATE FUNCTION dbo.TestFunction ( @MyID INT = 150589, @MyType VARCHAR(25) = 'Test' ) RETURNS date AS BEGIN; DECLARE @ExpirationDate DATE; IF @MyType = 'Test' BEGIN DECLARE @Test1 INT; WITH CTEuniqueName(ID, DateComplete) AS (SELECT T1.PersonID, MAX(T2.DateComplete) AS DateComplete FROM dbo.TABLE1 AS T1 WITH(NOLOCK) LEFT JOIN dbo.VIEW2 AS T2 WITH(NOLOCK) ON T2.ID = T1.ID WHERE T1.ID = @MyID AND T2.SecondID IN(SELECT SecondID FROM dbo.TABLE3 WITH(NOLOCK) WHERE Name = 'TEST') GROUP BY T1.PersonID) SELECT @ExpirationDate = CASE WHEN V3.TimeFrame > 0 THEN DATEADD(DAY, TimeFrame, CONVERT(DATE, CTE1.DateComplete)) ELSE NULL END FROM CTEuniqueName AS CTE1 INNER JOIN dbo.VIEW2 AS V2 WITH(NOLOCK) ON V2.ID = CTE1.ID AND V2.DateComplete = CTE1.DateComplete INNER JOIN dbo.VIEW3 AS V3 WITH(NOLOCK) ON V3.QuizID = V2.QuizID WHERE V2.SecondID IN(SELECT SecondID FROM dbo.TABLE3 WITH(NOLOCK) WHERE Name = 'TEST'); END; RETURN @ExpirationDate; END; GO我注意到該函式被標記為
is_inlineable = 0insys.sql_modules，所以我再次執行了函式創建程式碼，並執行了以下擴展事件會話：CREATE EVENT SESSION [inlining] ON SERVER ADD EVENT sqlserver.tsql_scalar_udf_not_inlineable ADD TARGET package0.event_file(SET filename=N'inlining') WITH (STARTUP_STATE=OFF) GO它產生了 1 個帶有
blocked_reason“CTE”的事件，符合Inlineable 標量 UDF 要求（在最初發布此問題後更新為包括 CTE）。這對我來說似乎仍然是錯誤的行為。實際上，我唯一的建議是重寫函式以避免 CTE。這可能會解決該問題，並且還可以允許內聯函式。
今天在開發人員測試另一個問題時遇到了這個問題。在他的 SQL 2019 上，他時不時遇到“無效的對象名稱”
$$ name of UDF $$”。 找到這個執行緒 - 並閱讀新的智能查詢處理和 UDF 內聯。
我現在可以重現該場景並將其轉發給 MS，不知道此過程，但我想我可以弄清楚那部分。
當使用在較舊的 SQL 伺服器上創建的 UDF 並在 2019 伺服器上恢復備份時，問題似乎出現了（至少在我們的情況下）。
那麼“is_inlineable”在 UDF 上為 1，即使它在正文中有 CTE。在 SQL 2019 上更改 UDF 後，問題就消失了。
這可能會影響我們所有升級到 SQL 2019 的客戶，因此我認為這非常關鍵。
下面是一個複制腳本。在 15.0.2070.41 測試
最好的祝福
/不同的
-- First steps are on non-SQL2019 server, used 14.0.2027.2 for this test -- 1. Create Local database "InlineUDF" in SQL Server below 2019 -- 2. Execute create part of script USE InlineUDF CREATE TABLE Test ([Id] INT, [ParentID] INT NULL, [Name] NVARCHAR(10)) INSERT INTO Test (Id, ParentID, Name) SELECT 1, NULL, 'Root' UNION SELECT 2, 1, 'Child' UNION SELECT 3, 1, 'Child1' UNION SELECT 4, 1, 'Child2' UNION SELECT 5, 1, 'Child3' UNION SELECT 6, 3, 'Child4' UNION SELECT 7, 6, 'Child5' UNION SELECT 8, 5, 'Child6' UNION SELECT 9, 2, 'Child7' GO CREATE FUNCTION [dbo].[ChildrenFilter] ( @parentId int ) RETURNS int AS BEGIN DECLARE @returnValue int DECLARE @result int BEGIN -- Recursive statement selecting InspectionJob inclusive all childs WITH ChildrenCountRecursive(id) AS ( SELECT Test.[Id] FROM Test WHERE Test.[Id] = @parentId UNION ALL SELECT Test.[Id] FROM Test INNER JOIN ChildrenCountRecursive AS tr ON Test.[ParentID] = tr.[Id] ) -- End of recursive statement -- Check inspection job and all childs for vesseltype and metadata. SELECT @result = COUNT(ChildrenCountRecursive.Id) FROM ChildrenCountRecursive END IF (@result >= 2) SELECT @returnValue = @parentId ELSE SELECT @returnValue = null RETURN @returnValue END GO -- 3. Backup database, and restore on SQL 2019 server -- 4. Examine Inlineable on UDF USE InlineUDF SELECT is_inlineable, NAME FROM sys.sql_modules m INNER JOIN sys.objects o on o.object_id = m.object_id WHERE o.name = 'ChildrenFilter' -- UDF is inlineable. -- 5. Clear proc cache - and run query. DBCC FREESYSTEMCACHE(N'ALL'); select * from Test WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id)) -- 6. Update compatibility level to 150 ALTER DATABASE [InlineUDF] SET COMPATIBILITY_LEVEL = 150 --7. Clear cache and try again - first time it fails - second time it's good. DBCC FREESYSTEMCACHE(N'ALL'); select * from Test WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id)) -- 8. Alter UDF GO ALTER FUNCTION [dbo].[ChildrenFilter] ( @parentId int ) RETURNS int AS BEGIN DECLARE @returnValue int DECLARE @result int BEGIN -- Recursive statement selecting InspectionJob inclusive all childs WITH ChildrenCountRecursive(id) AS ( SELECT Test.[Id] FROM Test WHERE Test.[Id] = @parentId UNION ALL SELECT Test.[Id] FROM Test INNER JOIN ChildrenCountRecursive AS tr ON Test.[ParentID] = tr.[Id] ) -- End of recursive statement -- Check inspection job and all childs for vesseltype and metadata. SELECT @result = COUNT(ChildrenCountRecursive.Id) FROM ChildrenCountRecursive END IF (@result >= 2) SELECT @returnValue = @parentId ELSE SELECT @returnValue = null RETURN @returnValue END GO -- 9. Examine Inlineable on UDF SELECT is_inlineable, NAME FROM sys.sql_modules m INNER JOIN sys.objects o on o.object_id = m.object_id WHERE o.name = 'ChildrenFilter' -- UDF is inlineable. -- 10. Clear proc cache - and run query. DBCC FREESYSTEMCACHE(N'ALL'); GO select * from Test WHERE Id IN (SELECT [dbo].[ChildrenFilter](Id)) -- Now it works...