5

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
  • 5
    If you're able to reproduce this issue each time after a reboot of the server, I'd suggest opening a ticket with Microsoft support on it. Kind of sounds like you have found a possible bug. – Taryn Jan 7 at 14:01
  • Can you make a minimal reproducible example and reproduce the problem via a dbfiddle at all? – John Eisbrener Jan 7 at 14:32
  • @John-Eisbrener - I may attempt to create a small example if I can get it to work; there are lots of tables and data involved in the current code, but I'll see if I can reduce it and still have the issue occur. – PhilipD Jan 7 at 15:08
  • @PhilipD We go through our SA (Software Assurance) to submit a request. – Taryn Jan 7 at 15:23
3

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.

  • 1
    I hope we can get a repro of this. I don't believe it's a known issue. – David Browne - Microsoft Jan 8 at 0:32
  • I replaced the development databases with backup copies from production again today, and now the problem occurs again, but only 1 or 2 times and then it starts working correctly. If I restart SQL Server, the error occurs again 1 or 2 times before starting to work. The error is still "Invalid object name 'CTEuniqueName'" (at the "FROM CTEuniqueName" line in the function). – PhilipD Jan 12 at 17:55
  • I have also discovered just now that if there is no activity in SQL Server (this is Sunday afternoon and there are no other users) - if I wait 10-15 minutes, and try the same SQL statements again, I get the same error again (at least once, sometimes twice in a row if I repeat the execution of the statements). Then it starts working correctly again. – PhilipD Jan 12 at 18:09
  • @Josh-Darnell I tried creating the same error starting with your code, but couldn't get the error to occur. Then, I tried the following - in the same database server I created 2 new databases (set to SQL Server 2019 mode), copied the tables (including all data) that are used by this function from the existing databases into the new databases, and also copied the function. I restarted SQL Server, and called the function with specific parameters in the new database, and it always worked. Immediately calling the same function and parameters in the original database still caused the error. – PhilipD Jan 14 at 11:44
  • To follow on from the previous comment, I had a query window open with a statement to call the function with specific parameters. With the database context set to the new database, immediately after restarting SQL Server, I executed the statement a few times and it never failed. I then changed the context to the original database and executed the statement again and it failed a couple of times before starting to succeed. The original database is almost 80GB with about 500 tables - I don't know how much of that database I would need to copy before the error started happening. – PhilipD Jan 14 at 11:50

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.