Using the WITH RECOMPILE option in SQL Server

Barrie Sosinsky, Contributor

When you create a stored procedure using the CREATE PROCEDURE statement, you can use the WITH RECOMPILE option to generate a new query plan every time the procedure executes. This option can be used in place of the FOR REPLICATION option, and the two cannot be used at the same time. To force a stored procedure to recompile, use the system stored procedure sp_recompile on a table or view that your stored procedure references.

You might wonder why WITH RECOMPILE might be worth using, and why it doesn't slow down performance in many cases. It is true that you loose the benefit of a compiled query plan, but a stored procedure almost always runs faster than a straight SQL statement. When a stored procedure runs it has be advantage of using previously parsed text, as well as a previously constructed query tree. WITH RECOMPILE trades off the small amount of time that it might take to build a new query plan, against the possibility that you will use a bad query plan that will cost you many more times the build time.

Use WITH RECOMPILE when your passed parameters to a stored query have a different distribution of data. In situations like these your optimized query plans would often turn out to be different as well. Also, use the WITH RECOMPILE option when you find that the parameters passed to the stored query result in very different execution times. Chances are that a stored query plan yielding very different

    Requires Free Membership to View

times needs to be modified for the individual parameters.

Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

This was first published in March 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.