SQL Server: Msg 8632, Internal error: An expression services limit has been reached.

Question

I'm in the process of migrating to a new server and discovered that some of my stored procedures get the exception:

Msg 8632, Level 17, State 2, Procedure spMyProcedure, Line 168 [Batch Start Line 23]  
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.  

This is not an overly complex procedure and I have many like this

I've googled this and found a number of posts with similar problems and the answers range from:

1. Delete the stored procedure, recreate it, rebuild it.
2. Downgrade compatibility mode from 2019 to 2017
3. Reduce the number of parameters in the IN clause
4. Don't use inline functions

I find it difficult to accept that older versions worked better than newer versions - and my "older version" was SQL Server 2019, but not the latest release of 2019 like the new server has.

The code below gets this exception if I use it in a procedure all by itself, however, its actually part of a much bigger UNION that has 7 more parts like this one:

SELECT DISTINCT     
Ls_Id AS [Agreement ID],        
Ls_Number AS [Agreement #],   
LsTp_Name AS [Agrmnt Type],  
LsTpSb_Name AS [Agrmnt Subtype],  
Ls_Lessor AS Grantor,  
Ls_Lessee AS Grantee,  
RecData,  
Ls_TractLocationText AS [Combined Tract Loc],  
Ls_Entities AS [Agreement Entities],   
[TA ID],   
[Record ID],   
[Record Description],   
[Record Source],   
[Record Type],   
[Record Location Text],   
[Record Entity]  
  
FROM            tbLease INNER JOIN  
                         tbLeaseTract ON tbLease.Ls_Id = tbLeaseTract.LsTr_Ls_Id INNER JOIN  
                             (SELECT        tbPad.Pd_Id AS [TA ID], CAST(tbPad.Pd_TaNo AS varchar) AS [Record ID], tbPad.Pd_Desc AS [Record Description], 'Pad' AS [Record Source], tbPadType.PdTp_Name AS [Record Type],   
                                                         tbPad.Pd_LocationText AS [Record Location Text], tbEntity.Ent_Name AS [Record Entity], tbPad2LeaseTract.Pd2LsTr_LsTr_Id  
                               FROM            tbPad INNER JOIN  
                                                         tbPad2LeaseTract ON tbPad.Pd_Id = tbPad2LeaseTract.Pd2LsTr_Pd_Id LEFT OUTER JOIN  
                                                         tbPadType ON tbPad.Pd_PdTp_Id = tbPadType.PdTp_Id LEFT OUTER JOIN  
                                                         tbEntity ON tbPad.Pd_Ent_Id = tbEntity.Ent_Id  
                               WHERE        (tbPad.Pd_IsActiveRow = 1) AND (tbPad.Pd_IsDeleted = 0)) AS pads ON tbLeaseTract.LsTr_Id = pads.Pd2LsTr_LsTr_Id LEFT OUTER JOIN  
                             (  
							   
							SELECT RcDt_Ls_Id, RecData = STUFF((  
								SELECT ', ' + RecData FROM (SELECT   
							RcDt_Ls_Id,  
							dbo.udf_FormatRecordingData(St_Abbrv, Cnt_Name, RcDt_EntryFileNo, RcDt_Book, RcDt_Page, RcDcTp_Name) AS RecData  
							FROM            tbRecordingData LEFT OUTER JOIN  
													 tbRecordingDocType ON tbRecordingData.RcDt_RcDcTp_Id = tbRecordingDocType.RcDcTp_Id LEFT OUTER JOIN  
													 tbState ON tbRecordingData.RcDt_St_Id = tbState.St_Id LEFT OUTER JOIN  
													 tbCounty ON tbRecordingData.RcDt_Cnt_Id = tbCounty.Cnt_Id) as zzzzRecData  
								WHERE RcDt_Ls_Id = x.RcDt_Ls_Id  
								order by RecData  
								FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, '')  
							FROM (SELECT   
							RcDt_Ls_Id,  
							dbo.udf_FormatRecordingData(St_Abbrv, Cnt_Name, RcDt_EntryFileNo, RcDt_Book, RcDt_Page, RcDcTp_Name) AS RecData  
							FROM            tbRecordingData LEFT OUTER JOIN  
													 tbRecordingDocType ON tbRecordingData.RcDt_RcDcTp_Id = tbRecordingDocType.RcDcTp_Id LEFT OUTER JOIN  
													 tbState ON tbRecordingData.RcDt_St_Id = tbState.St_Id LEFT OUTER JOIN  
													 tbCounty ON tbRecordingData.RcDt_Cnt_Id = tbCounty.Cnt_Id) AS x  
							GROUP BY RcDt_Ls_Id  
																					 ) AS tbRecData ON tbLease.Ls_Id = tbRecData.RcDt_Ls_Id LEFT OUTER JOIN  
                         tbLeaseTypeSub ON tbLease.Ls_LsTpSb_Id = tbLeaseTypeSub.LsTpSb_Id LEFT OUTER JOIN  
                         tbLeaseType ON tbLease.Ls_LsTp_Id = tbLeaseType.LsTp_Id  
WHERE        (Ls_Prj_Id = @Prj_Id) AND (Ls_IsActiveRow = 1) AND (LsTr_IsActiveRow = 1) AND (Ls_IsDeleted = 0) AND (LsTr_IsDeleted = 0) AND   
                         (LsTpSb_IsActiveRow = 1) AND (LsTpSb_IsDeleted = 0)  

Is there another work around for this that does not involve downgrading to 2017?

Thank you.

Answer 1

I think the problem might be here:

dbo.udf_FormatRecordingData(St_Abbrv, Cnt_Name, RcDt_EntryFileNo, RcDt_Book, RcDt_Page, RcDcTp_Name)

Microsoft did an improvement in SQL 2019, so that they now attempt to inline scalar functions. This can in many cases have drastic impact on performance to the better.

But as with all improvements, they can backfire, and it seems that in your case, it took your query over the brink.

You should be able to salvage the query by adding this hint:

OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'))

Although, there is one thing may want to try before that. To wit, you could replace the string aggregation you do with FOR XML PATH('') with the function string_agg, which made its debut in SQL 2017. It will only be a minor simplification to the query, but with some luck it could take you below the limit. And it would be an improvement to the query anyway.