I use WITH RECOMPILE or OPTION(RECOMPILE) quite often; the latter is what you use when you only want to recompile an individual query within a procedure. You put it at the end of the query - e.g.

SELECT A, B, C

INTO [MyNewtable]

FROM [MyTable]

WHERE Whatever

OPTION(RECOMPILE);

I have an ETL application which runs a lot of SQL procedures to process the data that has been loaded. When the procedures are created - and in some cases when they first run - the tables are empty. The next time they run there may be millions of records in each table. The initial plan may be a very bad one by that time.

I had one proc that ran in seconds under test, but sometimes took minutes or hours in production. Eventually I realised that it was sometimes trying to do a triangular join without using a critical index, even though the index was there. In that case the original plan was based on the statistics for the table just BEFORE it was loaded with a lot of data, when the index probably would not have been needed. The solution was to

before the query, so the

then produced a good plan.

Another case where RECOMPILE seems to help are procedures which use IF ... ELSE to run different queries depending on a parameter. I'm not sure how clever SQL Server is about these, but a plan where one query runs would not be good if next time a different one did.

As to doofledorfer's question about compile time - I don't know how long it takes but I assume milliseconds. Not good if you're running a fast transaction thousands of times, but no problem at all when you're doing a data load which takes seconds with RECOMPILE and minutes without it.