Interview Question of the Week #063 – How to Recompile Stored Procedure for Specific Table?

I have kept busy with SQL Consultancy now a days. Just the other day, while doing performance tuning exercise we had made major changes in the data stored in one of the main tables. The impact was so huge that we realized that it will impact how the SQL Server engine is building execution plans for that table. This brought up a question among us that – How to Recompile Stored Procedure for Specific Table? Well, this time the question was really not asked in the interview, but I really feel that it will be nice to address it here in this series.

Question: How to Recompile All the Stored Procedure for Specific Table?

Answer: Before I give answer, I must tell you that this is not something you should run every day. SQL Server is smart enough to do this exercise when needed as well as when the services are restarted. In our case, we were very certain that it was time when it would have not impacted the performance of the server and it was safe.

Here is the script which you can use to recompile all the stored procedures which are involving any specific table.

The 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

The following script will recompile specific stored procedure uspGetBillOfMaterials only.

USE AdventureWorks;
GO
EXEC sp_recompile 'uspGetBillOfMaterials';
GO

Reference: Pinal Dave (https://blog.sqlauthority.com)