2

I have hit a problem with SQL Server that results in it infinitely recompiling a function.

To reproduce, create a new database with the option Parameterization = Forced or execute the following on an existing DB:

ALTER DATABASE [DatabaseName] SET PARAMETERIZATION FORCED WITH NO_WAIT

Then execute the following script:

CREATE TABLE dbo.TestTable(
    ID int IDENTITY(1,1) NOT NULL,
    FullTextField varchar(100) NULL,
    CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED
    (ID ASC)
)
GO

IF NOT EXISTS(SELECT 1 FROM sysfulltextcatalogs WHERE name = 'FullTextCat')
    CREATE FULLTEXT CATALOG FullTextCat;
GO
    
CREATE FULLTEXT INDEX ON dbo.TestTable (FullTextField)  KEY INDEX PK_TestTable
    ON FullTextCat
    WITH
    CHANGE_TRACKING AUTO
GO

CREATE OR ALTER FUNCTION dbo.fn_TestFullTextSearch(@Filter VARCHAR(8000))
RETURNS TABLE
AS
    RETURN SELECT
        ID,
        FullTextField
        FROM dbo.TestTable
        WHERE CONTAINS(FullTextField, @Filter)
GO

SELECT * FROM dbo.fn_TestFullTextSearch('"a*"')

The query will never return. Running SQL Profiler to monitor SP:CacheInsert and SP:CacheRemove will show SQL server is doing this endlessly and the SQL logs will show countless "A possible infinite recompile was detected for SQLHANDLE" messages.

Setting the Parameterization = Simple works around the issue but we need this to be set to Forced for other reasons.

Has anyone come across this issue before and/or have a suggested solution?

Thanks,

Chuck

Stu
  • 30,392
  • 6
  • 14
  • 33
Chuck
  • 21
  • 1
  • No repro for me with the code provided. I also noticed that the execution plan didn't show any sign of parameterisation for me – Martin Smith Jan 19 '22 at 20:41
  • 1
    And no repro if I explicitly parameterise it either `EXEC sys.sp_executesql N'SELECT * FROM dbo.fn_TestFullTextSearch(@Filter)', N'@Filter VARCHAR(4)', @Filter = '"a*"'` – Martin Smith Jan 19 '22 at 20:48
  • 1
    What does `select @@version` return? There have been 14 [cumulative updates](https://learn.microsoft.com/en-us/sql/database-engine/install-windows/latest-updates-for-microsoft-sql-server) to SQL Server 2019 so far. Are you up-to-date? – AlwaysLearning Jan 19 '22 at 22:04
  • Thanks for the feedback. I was running CU13 and have now upgraded to Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64). Still have the problem when executing the query without explicitly parameterizing it. So this suggests there is some other setting or config at play in my environment that is having an effect. However, parameterizing it as per Martin's comment above works. I will see if I can use this approach rather than pure dynamic SQL for the use case I'm working on. – Chuck Jan 20 '22 at 21:57

1 Answers1

0

While I still experience the problem with the original code I provided, by following @Martin's approach of explicitly parameterizing the call to the function:

EXEC sys.sp_executesql N'SELECT * FROM dbo.fn_TestFullTextSearch(@Filter)', N'@Filter VARCHAR(4)', @Filter = '"a*"'

I have been able to successfully work around the problem.

Jeremy Caney
  • 7,102
  • 69
  • 48
  • 77
Chuck
  • 21
  • 1