I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses `FieldName LIKE '%searchterm%'` on multiple columns. This query usually finishes in about 2-5 seconds. But sometimes (at seemingly random points in time) the server goes into a state where the query takes ~120 seconds to finish. This state can last from a couple of minutes to several hours. I have indications that you can end the slow state by calling `DBCC FREEPROCCACHE`, but I'm not completely sure. I'm using SQL Server 11.0.3153.

I've used the profiler tool to trace the server. Most of the time the query requires 900 CPU and 500 000 reads, but in the slow state it's 100 000 CPU and 7.5*10^6 reads.

The query looks something like this:

    SET @search = REPLACE(@search, ' ', '%');
    SET @search = '%' + @search + '%';
    
    SELECT
        ...
    FROM
        Table1 t1,
        Table1 t2
        /* The real query uses 3 more joined tables left out here */
    WHERE
        t1.id = t2.t1_id
        AND (
            OR (t1.col1 LIKE @search)
            OR (t2.col1 LIKE @search)
            OR (t1.id IN (SELECT t1_id FROM Table3 t3 WHERE t3.col1 LIKE @search)
            )
	ORDER BY
		SomeCol DESC,
		SomeOtherCol DESC
	OFFSET @row_offset ROWS FETCH NEXT @row_count ROWS ONLY

Can anyone tell me what's going on here? What can be the cause of the inconsistent behavior?