I have been trying to analyse and optimize a runtime spike in a stored procedure in SQL Server 2014.

Background of the DB objects

I found that the stored procedure shows a spike in runtime when there are more number of records in a source table to process.

The stored procedure part that is lagging is a SELECT INTO temp table (say #tempTable2) statement, that joins another temp table (say #tempTable1) and a multi-statement table valued function (say testFunction)

Code format below :

SELECT t.col1, f.col2
INTO #tempTable2
FROM #tempTable1 t
OUTER APPLY testFunction(t.id) f

This function returns results very quick when I tries to test passing a single value like below:

SELECT * 
FROM testFunction(100)

But when the actual stored procedure statement runs, it is kind of stuck in the function for hours (e.g: if #tempTable1 has 12K records, taking 9 hours for the above statement to complete)

This function calls different other multi-statement table valued functions from it.

Also it queries from a table (say table1) which has full-text search enabled - uses full-text search functions such as CONTAINS and CONTAINSTABLE.

The table currently has 6 million records.

The full text index on the table has change tracking set to OFF & Full text index is configured for 3 text columns in the table. Also, The table has a clustered index on Id (int datatype) column.

This table is truncated and loaded daily and then below statement executes

ALTER FULLTEXT INDEX ON indx_table1 START FULL POPULATION

There is no other maintenance seems to be done for the Full text index table.

Checking sys.fulltext_index_fragments returns only 1 record with below details

max full-text crawl range for DB has value 4.

--

I doubt the bad performance of the query is due to the unmaintained Full text search function - but do not have any proof for it.

If anyone has some idea about this, can you please share your thoughts.

Edit: Inserted script for TVF(testFunction) and function called from it(fnt_testfunction3) in https://www.codepile.net/pile/6BDJNPA0

The table with full text search used in testFunction is tbl_FullTextSearch