I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the
sp_executesql stored procedure.
Sometimes the query changes (not the parameters, but the query itself). I know this causes SQL Server to invalidate the plan.
If I change this query:
exec sp_executesql N'SELECT nome from customer where name = @name' ,N' @name varchar(50)' , @name=N'rafael';
exec sp_executesql N'SELECT nome, address, phone from customer where name = @name' ,N' @name varchar(50)' , @name=N'rafael';
The plan will be invalidated.
Does this also cause a re-compilation?
Those queries are not the queries I am running, they are only examples.
I am not using
#temp tables. Bellow are 2 queries that are being recompiled:
SELECT TOP 1000 X, Y, Z FROM MyTable WHERE Contains(VarcharMaxField, @Clause) AND XXX IN (SELECT data FROM fn_ArrayTable(@Pub)) AND YYY = 1 AND ZZZ < DateAdd(n, -10, GetDate()) ORDER BY A, B, C; SELECT TOP 1000 A, B, C FROM MyTable WHERE Contains(VarcharMaxField, @Clause) AND (XXX IN (@Pub0,@Pub1)) AND (AAA= 1) AND (BBB< DateAdd(n, -10, GetDate())) ORDER BY A, B C;
I had to change the fields due to an internal company policy. The
fn_ArrayTable is a function that returns a comma separated string as a table.