SQL Server: Effects of using 'WITH RECOMPILE' in proc definition?
This makes the proc rebuild the plans of all queries every time it's run.
Useful it the values of the proc parameters affect the filter selectivity.
Say, the optimal plan for this query:
SELECT *
FROM orders
WHERE order_date BETWEEN @begin_report AND @from_report
will be a full scan if the date range is large or an index scan if it's small.
Created using WITH RECOMPILE
, the proc will build the plan on each execution; without one, it will stick to a single plan (but will save time on recompilation itself).
This hint is usually used in procs processing large volumes of data and doing complex reports, when the overall query time is large and time for rebuilding the plan is negligible compared with the time saved by a better plan.
Dan Esparza
As a Software Developer I really like Go / Docker / C# / ASP.NET / MVC / Bootstrap / ReactJS and Flux / yarn / webpack / AppVeyor, CircleCI, RedGate Ants, and that my favorite comic has its own website. As a budding software entrepreneur I like Hacker News, Trello, Stripe, Github, CircleCI, Balsqmiq mockups, Pingdom, CloudFlare, MailGun, DigitalOcean and Amazon EC2. As a cook, I really like the French & Good Eats. As an American, I like College Football and Baseball. As an iPhone & iPad user, I like Reeder, & 1Password. As a lover of the interwebs, I like Pinboard.in, Pocket, Flickr, Yelp, Keepass, and Dropbox As a network and web security hobbyist, I follow Bruce and was illuminated by the Base Rate fallacy. As a geek, I really enjoy learning about quantum physics and I'm amazed by the double slit experiment. Feynman was a rock star in my book.
Updated on June 12, 2022Comments
-
Dan Esparza about 2 years
My understanding of the
WITH RECOMPILE
option with stored procedures is generally limited to using the clause with a single stored proc call as a trailing parameter:exec sp_mystoredproc 'Parameter1', 2, '1/28/2011' with recompile
What are the effects of including
WITH RECOMPILE
in the actual proc definition? Does this recompile the proc every time it's executed? Or just the next time the proc is altered?Example:
CREATE PROCEDURE [dbo].[sp_mystoredproc] (@string1 varchar(8000) ,@int2 int = 2 ,@dt_begin DATETIME with recompile AS ... proc code ...