mssql 2022 - random incorrect syntax ')'. errors

Question

MSSQL Version

Microsoft SQL Server 2022 (RTM-CU2) (KB5023127) - 16.0.4015.1 (X64) 
	Feb 27 2023 15:40:01 
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Windows Server 2022 Standard 10.0 <X64> (Build 20348: ) (Hypervisor)

All database with compatibility_level=160

Sometimes when connecting the application via mssql-jdbc-9.4.1.jre8 errors "incorrect syntax ')'" appear, although the prepared query and procedure definition are correct.

Errors appear randomly. Stored Procedures can execute correctly several dozen times, and that an error appears in 2 or 3 subsequent executions. After that, the procedure works fine again (object definition hasn't changed)

The problem occurs on various procedures, but has never occurred with database compatibility_level=150.

Any ideas what could be causing the problems ?

Example:

Error caught by XE

StatementText5

       select
             c1.Table1Id
       from
             [dbo].[Table1] c1 with (nolock)
             join [dbo].[Table5] c5 with(nolock) on c1.ColEntId is null and c5.TableId = c1.ColEntElId and c5.UsrId = @usr and c5.Nb = @nb and c5.IsD = 0
       where
             (c5.Dest = 1 and c1.IsDel = 1 and c1.ModDate > @dt)
             or (c5.Dest = 0)
			 
       union
	   
       select
             c1.Table1Id
       from
             [dbo].[Table1] c1 with (nolock)
             join [dbo].[Table2] c2 with (nolock) on c2.Table2Id = c1.ColEntElId and c1.ColEntId = @Table1EntId
             join [dbo].[Table3] c3 with (nolock) on c2.Table3Id = c3.Table3Id
             join [dbo].[Table4] c4 with (nolock) on c4.Table4Id = c3.Table4Id
             join [dbo].[Table5] c5 with(nolock) on c5.TableId = c4.TableId and c5.UsrId = @usr and c5.Nb = @nb and c5.IsD = 0
       
       where
            (c5.Dest = 1 
				and ((c1.IsDel = 1 and c1.ModDate > @dt) or (c2.IsDel = 1 and c2.ModDate > @dt) or (c3.IsDel = 1 and c3.ModDate > @dt) or (c4.IsDel = 1 and c4.ModDate > @dt))
			)
            or (c5.Dest = 0)

Procedure definition

create PROCEDURE [schema].[StoredProcedure]
       @usr int, 
       @Nb varchar(100),
       @dt datetime
AS
BEGIN
       StatementText 3

       StatementText 4
       
       StatementText 5
       
END

Answer 1

I have the same problem, but disabling the Query Store makes the problem (and the Query Store) go away.

I have tried to clear and fix the query store, but with no success

Answer 2

We experienced the same issue on a fresh SQL Server 2022 CU5 instance. The database was transferred with backup/restore from a SQL Server 2014 instance. The instance was set up with CU5, no CU were installed after the database was restored. No trace flags, no database scoped configurations, only set compat level to 160 and activated the query store. Also, no manual query store hints/plan forcing for individual queries.

The issue surfaced on 2 databases and 4 different queries, but it's still a relative rare case as these databases run 100K queries each day. All queries were syntactically fine; a rerun was always successful.

We've opened a support case with MS (TrackingID#2309050010002020) and deactivated the query store on one of the databases where the issue was happening each day. The deactivation helped.

MS Support confirmed that it's a known issue and the work on the patch was in progress (first half of September 2023) with no known ETA.

As an alternative to disabling of query store they also suggested activating the trace flag 12601 to disable QDS statement hints. I've done this on a test server and it seems to make the error go away (not 100% sure, because it was for another database where the issue surfaced only once).

I'll anyway probably prefer to deactivate the query store for a database experiencing the problem rather than changing an instance-wide setting, which will also have side effects on other databases, but I still wanted to have this alternative tested.

Answer 3

SQL Server 2022 CU11 was released today. In the fix list I noticed this https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2022/cumulativeupdate11#2787964:

Fixes an issue in which the Cardinality Estimation (CE) feedback generates an empty USE HINT clause as the query hint.

Since I have not run into this myself, I cannot vouch for anything, but it absolutely sounds like some that could result in this mysterious syntax errors.

Answer 4

I have been running CU11 for 8 days now without the error, so I think it is safe to conclude that CU11 DOES fix the problem. I used to get the error ~5 times/week. Remember to purge the query data after enabling the query store!

Answer 5

I don't know what is going on, but it is possible extract more information from the error_reported event by adding sql_handle and a few more fields to the event. I happen to have an article that discusses this on my web site, along with a view to analyse the information: https://www.sommarskog.se/Short%20Stories/trace-exceptions.html

Also, I note that you are on CU2, and CU4 was recently released. I have no idea if that resolves the issue, though.