Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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';

to

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.

EDIT

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.

Print

share|improve this question
    
Your EventSubClass column indicates that there was a schema change and that caused the recompile. This leads me to think you might be using # temp tables. Can you confirm this? –  Nic Sep 20 '12 at 18:14
    
No, I am not using #temp tables. –  Rafael Colucci Sep 20 '12 at 21:14
    
Any possible changes in the underlying table schema at all? Would it be possible to post the code for the proc (anonamyzing object names)? –  Nic Sep 20 '12 at 21:29
    
No one is changing the schema, thats I am sure. I just posted the code. –  Rafael Colucci Sep 20 '12 at 21:30
    
If you remove the CONTAINS predicate, does it still cause a recompile? –  Jon Seigel Sep 20 '12 at 22:29
show 4 more comments

1 Answer

It won't actually cause a recompilation, rather you have a completely new query and so it will have to be compiled for the first time.

If these sp_executesql statements are being called from within a stored procedure there might be some other cause for the recompile (such as a temp table or option).

I would recommend following the steps in http://support.microsoft.com/kb/243586 to troubleshoot exactly what statement is causing your recompilations.

share|improve this answer
    
Those statements are not being called from within a stored procedure and I know what statemens are causing my recompilations but i have no ideia why since they are very very simple (but they use a fulltext search). –  Rafael Colucci Sep 20 '12 at 16:41
    
Do you know if the plan stays in cache between executions? –  Nic Sep 20 '12 at 16:44
    
No, I dont and I have do idea how to check it. :) –  Rafael Colucci Sep 20 '12 at 16:47
    
Here's a basic look into items in the cache SELECT TOP 100 ISNULL(DB_NAME(dbid), 'ResourceDB') , objectid , usecounts , cacheobjtype , objtype , TEXT AS SQL FROM sys.dm_exec_cached_plans decp CROSS APPLY sys.dm_exec_sql_text(plan_handle) dest ORDER BY usecounts DESC; –  Nic Sep 20 '12 at 16:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.