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. Even when the server is in the slow state it only seems to affect connections made by my .NET application, I can still execute the stored procedure quickly via SQL Server Management Studio.
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 ( (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?