WITH RECOMPILE option and stored procedure

03 January,2014 by Tom Collins

Using the WITH RECOMPILE option in a stored procedure generates a new execution plan each time the query is executed. This technique is often used for queries that require high performance in certain situations, such as using CASE block where different columns may be used for a search. But , with the flexibility WITH RECOMPILE offers , it comes with a cost of increased CPU usage

For a simple query , there may be minimal overhead, but for complex queries – some serious CPU time may be required to create a query plan. As the developer , you’ve requested what data to return , SQL Server deals with how to access the data.  SQL Server is managed by algorithms decide on a execution plan , based on cost parameters.

1)     Do you need the WITH RECOMPILE option in the stored procedure? Is it necessary to  recompile the stored procedure every time it executes?

2)     If WITH RECOMPILE is causing a performance bottleneck , can the code be rewritten to avoid recompiling.

3)     Create a report to identify stored procedures using WITH RECOMPILE. Present the report to the developers  and work with them to identify any which may be creating performance issues

4) Read more on How to improve Execution Plan Reuse

Read More

Top 5 habits of Performance Tuning Experts

SQL Server - Stored Procedure Checklist

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on WITH RECOMPILE option and stored procedure

sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer