SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The RECOMPILE Options


The RECOMPILE Options

Author
Message
Marry Krissmess
Marry Krissmess
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 29
anil_kumar32 (7/7/2011)


Good things that comes with SP are security, less network traffic and SQL injection aviodance.



Attention with security. Sometimes SPs execute with admin permissions even when the user does not have this level. Network traffic is indeed reduced. BUT you can easily write an SP where injection is possible for example when you create the ad-hoc SQL inside the SP.
Marry Krissmess
Marry Krissmess
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 29
double post
rene.schulz@implico.com
rene.schulz@implico.com
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 84
Developers (mostly in the beginning stages of their career) often tend to use OPTION (RECOMPILE) an an excuse for introducing more and more adhoc T-SQL into the application and then tend to eliminate SP's altogether. And then they reach a stage where plan cache gets filled up to the brim again and again.


I'm not a native speaker, but this part in the blog is a little bit confused for me. I understand that T-SQL can fill up the plan cache by the described reasons in your article, but the opton RECOMPILE will not cache any statements in the cache. The explanation is also only related to T-SQL statement usage instead of SPs and not to the RECOMPILE option?

http://msdn.microsoft.com/en-us/library/ms181714.aspx

Instructs the SQL Server Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed.
kramaswamy
kramaswamy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1986 Visits: 1812
Could be wrong, but I think what he means is that, because these developers switch from using stored procedures to inline ad-hoc queries, the plan cache gets filled up by all the queries being executed. Even if they are deleted and repopulated each time they are run, each different query adds to the cache.
hakim.ali
hakim.ali
SSC Eights!
SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)SSC Eights! (914 reputation)

Group: General Forum Members
Points: 914 Visits: 1050
Good reading and very informative. Thanks.

Hakim Ali
www.sqlzen.com
anil_kumar32
anil_kumar32
Old Hand
Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)Old Hand (370 reputation)

Group: General Forum Members
Points: 370 Visits: 369


Hello Marry,

Pease understand if i wrote SP to avoid SQL Injection then obviously i would not any ad-hoc query within SP.

Regards,
Anil


mtassin
mtassin
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5338 Visits: 72521
Marry Krissmess (7/7/2011)
anil_kumar32 (7/7/2011)


Good things that comes with SP are security, less network traffic and SQL injection aviodance.



Attention with security. Sometimes SPs execute with admin permissions even when the user does not have this level. Network traffic is indeed reduced. BUT you can easily write an SP where injection is possible for example when you create the ad-hoc SQL inside the SP.



Only when you do that wrong. for an example of how to generate dynamic SQL in a stored proc and still be relatively safe from injection read Gail's blog

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
gary.strange-sqlconsumer
gary.strange-sqlconsumer
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 619
Hi Suresh,

Good article!

Is it really necessary to include the database name in all object references? I've always included schema.object but not database name as it's impossible to deploy the same code base on many databases if you hardcode the db name.

Surely the search for object statistics will start in the current database so adding the db name won't help. Also shouldn't we be using synonyms when making cross database queries.
chrisfradenburg
chrisfradenburg
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2194 Visits: 2068
If option recompile is used the plan doesn't remain in cache so it won't lead to cache bloat. Disclaimer, don't run this in production.

dbcc freeproccache
go

select * from msdb.dbo.sysjobs option (recompile)
go

select * from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.text like '%sysjobs%' option (recompile)
go

select * from msdb.dbo.sysjobs
go

select * from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where st.text like '%sysjobs%' option (recompile)
go




While looking into this I found that the recompiled statement didn't exist in sys.dm_exec_query_stats either which has performance troubleshooting implications.
chrisfradenburg
chrisfradenburg
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2194 Visits: 2068
OPTION (RECOMPILE) is to be only used as a temporary measure while a process for converting adhoc T-SQL code to Stored Procedures is underway.


I'm not sure I agree with this. I've seen cases where there's a statement that doesn't take a lot of resources to compile and depending on the parameters passed in different execution plans can be generated. Statements like that are good candidates for recompiles. Granted, it may be more efficient to compile it with a specific parameter but that depends on the situation. Using option recompile certainly isn't the first thing I would look at but there are times it may end up being the best tool.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search