This can happen due to many factors like lack of indexes, out of dates statistics, if it returns a large number of records, etc.
I observed a similar behavior recently in a production environment. The stored procedure had the code similar to the following;
WHERE Col1 = @Col1
AND Col2 = @Col2
AND RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(CAST(Comments AS VARCHAR(2000)),'<br />',''),CHAR(10),''),CHAR(13),''),' ',''))) <> ''
Let me give you some background information about this table.
- Col2 and Col3 are UNIQUEIDENTIFIER
- Comments column is ntext and does not have indexes
- All three columns Col1, Col2 and Col3 has indexes
- Col2 and the PK is the clustered index of the table
- Table has 825,452,256 records (825 million)
When executing the stored procedure very first time, application has received time outs which prompt us to check in database side what is happening. When I checked I noticed it is executing stats creation statement for the Comments column. (This column has never used in WHERE clause before)
SELECT StatMan([SC0], [LC0], [SB0000]) FROM (SELECT TOP 100 PERCENT [SC0], [LC0], step_direction([SC0]) over (order by NULL) AS [SB0000]
FROM (SELECT SUBSTRING ([Comments], 1, 100)++substring([Comments], case when datalength([Comments])<=400 then 101 else datalength([Comments])/2-99 end,
datalength([Comments])) AS [SC0], datalength([Comments]) AS [LC0] FROM [dbo].[Tab1] TABLESAMPLE SYSTEM (1.517414e-001 PERCENT)
WITH (READUNCOMMITTED) ) AS _MS_UPDSTATS_TBL_HELPER ORDER BY [SC0], [SB0000] ) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)
And also no plan was created. Which means before creates the plan SQL Server needs the stats created for Comments column which slows down the execution. That is the reason it is executing the above statement.
However I see this behavior in SQL Server is unnecessary because the Comments column is rapped by many string functions and it will anyway use CLUSTERED INDEX SEEK or SCAN depending on the no. of rows it output.
It was able to by pass the above stats creation step, after creating the STATS manually with 0 records. Here I choose 0 records otherwise it takes lot of time since this table is a huge one.
CREATE STATISTICS Comments_Stats ON Tab1 (Comments) WITH SAMPLE 0 ROWS,NORECOMPUTE
After creating this stats, now SQL Server can create a plan for the stored procedure and it complied quickly and runs as expected.