SQL Server: Use sp_recompile to recompile the stored procedures, triggers, and user-defined functions

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

The Parameter Sniffing in SQL Server is a prevalent problem where the previous query execution plan resides in the cache area, and Query Optimizer is not generating new query execution plan for each execution.

At first glance, it looks OK because cache always results faster than frequent recompilation, but if data is rapidly changing, new Query Execution plan is needed.

You can read more about SQL Server Parameter Sniffing in the check below article:

SQL Server Parameter Sniffing

Now about the sp_recompile, using this you can explicitly recompile stored procedures, triggers, and user-defined functions. Once you recompile these objects using sp_recompile, will be recompiled the next time that it is run.

If you recompile object like table or view, all the stored procedures, triggers, or user-defined functions that reference the table or view will be recompiled the next time that they are run.

Sample:

Return Message: