I have noticed that after inserting many rows in one table many times the stored procedure on that table executes slower or degrades. This happens quite often after BCP or DTS. I prefer to recompile all the stored procedure on the table, which has faced mass insert or update. sp_recompiles marks stored procedures to recompile when they execute next time.
Example:
----Following script will recompile all the stored procedure on table Sales.Customer in AdventureWorks database.
USEÂ AdventureWorks;
GO
EXEC sp_recompile N'Sales.Customer';
GO
----Following script will recompile specific stored procedure uspGetBillOfMaterials only.
USEÂ AdventureWorks;
GO
EXEC sp_recompile 'uspGetBillOfMaterials';
GOReference : Pinal Dave (https://blog.sqlauthority.com), BOL
