hello, it's relation to myoriginal question here, because i have some test with stuff query in sql 2008.
CREATE PROCEDURE test
@kind NVARCHAR(10), @id NVARCHAR(10),
@startday date, @endday date
SET NOCOUNT ON;
IF @kind = '1'
SELECT * FROM dbo.tbl1 where id = @id
IF @kind = '2'
my stuff query with @startday, @endday
Sometime, i execute proc with @kind = 2, it's run 2s (normal). but sometime it's run 30s or more or expire time, with same value of @startday and @endday.
I have some test, if i execute proc with @kind = 1 first, and run with @kind = 2 then i have problem, it's take slow. But i release cache plan of proc, and excecute with @kind = 2 first, or i execute with OPTION (RECOMPILE), it's run very fast.
I guess it's a problem of parameter sniffing or something else ? What do you think ?