Sql-server – After SQL restart, CTE causes invalid object name error for a short period in SQL Server 2019

ctefunctionssql serversql-server-2019

We have some SQL code that has been in production for some time, in a SQL Server 2016 database; but it is raising an error in a SQL Server 2019 database for the first hour or so after restarting SQL Server (anywhere from 5-10 minutes to an hour or more, probably depending on the level of activity in SQL Server). The error is "invalid object name" for a CTE (Common Table Expression).

We have a production environment with several databases in SQL Server 2016. We have now set up a new development/test environment with SQL Server 2019 (on a Windows Server 2016 machine, with 24GB of RAM and 4 CPU cores) so that we can test with SQL Server 2019. The databases on this test server are restored copies of the production databases from production backups. All the databases in the test environment have the compatibility level set to 150 (SQL Server 2019).

Early each morning, we started to see some issues with a couple of functions that use CTEs, where the function would raise errors like this:

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'.

The errors stopped happening after a short period of time and didn't happen again until the next morning.

The error was happening in a pair of stored procedures that were called one after the other, both of which called the same (user-defined SQL) function. With some testing, I learned that I could sometimes cause the same error by just calling the function, and then by just executing a block of code from the function.

I also discovered that I could consistently cause the error by restarting the SQL Server instance and calling the function or the code block. This is probably also why it was only failing early in the morning – there had been no activity on the SQL Server instance for several hours before that, so it had gone into idle mode or shut down its processes.

After repeatedly calling the function or code, at some point it would succeed without raising the error, and after that, it seemed to keep working (until I restarted the SQL Server instance again).

If I change the database that contains this function to "SQL Server 2016" compatibility mode, then the function always succeeds, even immediately after restarting the SQL Server instance. So, it seems to be an issue specific to SQL Server 2019.

The code in the function looks like the following (with anonymized names), and the function contains 2 separate "IF" blocks similar to the following; however I can cause the error with just the following block of code. The 2 views in this code are views to other databases in the same SQL Server instance.

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;

Also, in the time period after startup that the error is occurring, if I run the 2 stored procedures (that call the above function, and both procedures return a result set) in an SSMS window, then the procedures successfully return their respective result sets in the SSMS window and then SSMS switches to the Messages panel and displays the "invalid object name" error.

Does anyone know why this might be intermittently failing only in SQL Server 2019 (and seemingly only after SQL Server startup or a period of idel time)? Or, can anyone suggest how to troubleshoot issues like this? I have tried checking the SQL error log; and I have also tried running the Profiler with function calls included – neither of these has provided any clues.

Additional information:

  • Running DBCC FREESYSTEMCACHE(N'ALL'); does cause the same error to temporarily occur again (in the same way that restarting the server does)
  • The function is not inlineable (due to the CTE), so it doesn't seem like scalar UDF inlining is the cause of the problem
  • There are no errors in the SQL Server error log when this occurs
  • I tried to reproduce the problem using newly created databases on the same instance, with just the tables required by the function, but the functions always complete successfully in the new database

Best Answer

Initially, it seems like this was a bug related to the new Scalar UDF Inlining feature added in SQL Server 2019, since you mentioned that disabling inlining resolved the problem. On further inspection, the function cannot be inlined due to the presence of a CTE in the function definition.

Here's my (failed) attempt to reproduce the issue:

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

I noticed that the function is marked as is_inlineable = 0 in sys.sql_modules, so I ran the function creation code again with the following Extended Events session running:

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

It produced 1 event with a blocked_reason of "CTE," which lines up with the Inlineable scalar UDFs requirements (that were updated to include CTEs after this question was initially posted).

This still seems like buggy behavior to me. The only suggestion I have, really, is to rewrite the function to avoid the CTE. This might work around the problem, and also could allow the function to be inlined.

Related Question