What does it mean to "recompile" a stored procedure on a SQL Server?

Question

What does it mean to "recompile" a stored procedure on a SQL Server? Should this be a manual process or an automated one? What are the pros and cons of this process? Thank you!

Answer 1

It's strictly an internal operation within SQL Server.

As a database is changed by such actions as adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them

Here's MSDN's article on Recompiling Stored Procedures

When you issue an ALTER PROC statement, you're also causing a recompile of the stored proc.

Answer 2

Use of Recompile makes SQL Server to recompile to SP, hence instead of using existing plan for same SP, Database engine will create new execution plan.

It can be both Automated and Manual process depending upon your requirement. You can make SP recompile every time when it get executed by including RECOMPILE "hint" in your SP. Or run

Exec sp_recompile N'yourSP'

to manually recompile.

And as @Michael mentioned, too many recompilations can be very bad since for each compilation database engine has to create new execution plan which could be costly operation. So generally automatic recompilation of SP should be avoided, and even manually recompilation should be tested in development environment first before using it production to be sure that it really does help.