SQL SERVER – Recompile All The Stored Procedure on Specific Table

SQL SERVER – Recompile All The Stored Procedure on Specific Table « Journey to SQLAuthority

SQL SERVER – Recompile All The Stored Procedure on Specific Table

June 29, 2007 by pinaldave

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’;

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

What is 13 + 3 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)