CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2016
Underhill Julian
Underhill Julian

Reputation: 29

Stored procedure execution plan WITH RECOMPILE

I have a stored procedure that runs every 5 minutes in a job on SQL server. The job will run for 80% of the time with no results , this is expected, but when it does have data to process it is a very long process. The code is like this below simplified.

IF exists (Select top 1 col1 from tbl1 where processed = '0' )
BEGIN
    HUGE PROCESS with multiple selects joins and updates
END

How will the execution plan evaluate this SP? Is this a rare case that using with WITH RECOMPILE is the best option?

Upvotes: 0

Views: 896

Answers (1)

gotqn
gotqn

Reputation: 43636

If you use Include Actual Execution Plan option in the SQL Server Management Studio, you will see that when the IF expression is evaluated to false its body's operators are not included in the execution plan.

So, there is no need to worry - the SQL Engine will use correct execution plan and will not touch the data.

The recompile option can be helpful in particular queries but I believe you can skip it for now.

Upvotes: 1

Related Questions