question

jasmy avatar image
jasmy asked

OPTION(RECOMPILE);

Can we use OPTION(RECOMPILE) in stored procedures? Can anyone suggest the Consequences of using OPTION(RECOMPILE)
sql-server-2012performance-tuning
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
You can use OPTION(RECOMPILE) in a query in your stored procedure. What it will do is to create a new query plan each time you execute the query. Normally, you don't want this to happen, but there are occasions when you may, for example when SQL Server is "smart" enough to create a parametrized plan for you, and you really don't want that to happen, but rather an individual plan for each value of parameters. That might be the case when you have a partitioned table, and therefore want SQL Server to use partition elimination to only seek or scan in one partition of an index rather than the whole index. That might also be the case when you have unevenly distributed data in an index, and SQL Server has created a plan which for some values is not optimal. For a stored procedure, you can create it with the keyword WITH RECOMPILE. That will force SQL Server to create a new plan for the whole stored procedure each time it's executed. So OPTION(RECOMPILE) applies to a query (which could reside in a stored procedure) and WITH RECOMPILE applies to a whole stored procedure. As I said to begin with: There are rare occasions when you want SQL Server to create a new plan each time a query or proc is run. Normally, you are fine with keeping whatever plan SQL Server has created. If you have auto update stats enabled (is enabled by default), SQL Server will in most cases realise when your data distribution changes, and decide for itself to throw out old plans.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Awesome answer. One additional point, a query, or a procedure, with the RECOMPILE hint won't be stored in cache at all.
3 Likes 3 ·
jasmy avatar image jasmy commented ·
Thank you Magnus Ahlkvist . It seems the best answer
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.