Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

The RECOMPILE Options Expand / Collapse
Author
Message
Posted Thursday, July 7, 2011 2:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 2:25 PM
Points: 94, 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.
Post #1137886
Posted Thursday, July 7, 2011 2:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, April 25, 2013 2:25 PM
Points: 94, Visits: 29
double post
Post #1137889
Posted Thursday, July 7, 2011 2:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, August 6, 2012 7:37 AM
Points: 37, Visits: 77
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.
Post #1137897
Posted Thursday, July 7, 2011 6:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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.
Post #1138035
Posted Thursday, July 7, 2011 6:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, September 5, 2014 8:36 AM
Points: 535, Visits: 808
Good reading and very informative. Thanks.

Hakim Ali
www.sqlzen.com
Post #1138046
Posted Thursday, July 7, 2011 6:22 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 3, 2014 1:22 PM
Points: 316, Visits: 133


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

Post #1138047
Posted Thursday, July 7, 2011 8:09 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:38 AM
Points: 3,675, Visits: 72,434
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
Post #1138172
Posted Thursday, July 7, 2011 9:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 11, 2014 3:58 AM
Points: 43, Visits: 467
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.

Post #1138214
Posted Thursday, July 7, 2011 10:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:10 AM
Points: 1,635, Visits: 1,972
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.
Post #1138267
Posted Thursday, July 7, 2011 10:26 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 4, 2014 8:10 AM
Points: 1,635, Visits: 1,972
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.
Post #1138276
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse