SQL SERVER – Recompile All The Stored Procedure on Specific Table

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';
GO

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

,
Previous Post
SQL SERVER – 2005 Improvements in TempDB
Next Post
SQL SERVER – Fix : Error : Server: Msg 131, Level 15, State 3, Line 1 The size () given to the type ‘varchar’ exceeds the maximum allowed for any data type (8000)

Related Posts

6 Comments. Leave new

  • Hi,

    could you please tell me how to find the sps which are marked for recompile.

    Thanks in advance

    Reply
  • Here is a quick way:

    SELECT NAME
    FROM sysobjects
    WHERE type = ‘P’ AND uid = 1
    ORDER BY Name

    Reply
  • I’m trying to find out if I can use any of the following to select between one of two CTEs in a master data set.
    Which one will work best.
    IIF()
    Switch()
    CASE()

    Code starts out with With CTE### as
    (
    select (fields)
    from (
    select (fields)
    from
    where (filters)
    groub by (groups)
    )Pull
    Pivot
    (Sum()
    for month in
    (1,2,3,….) as Pvt###
    Group by ()
    Union all
    select (fields)
    from (
    select (fields)
    from
    where (filters)
    groub by (groups)
    )Pull
    Pivot
    (Sum()
    for month in
    (1,2,3,….) as Pvt###
    Group by ()
    )
    Select ()
    from CTE###
    group by ()
    Order by ()

    __________
    thanks

    Reply
  • Dear sir ,

    in one company i faced interview for T-SQL developer
    in there technical person asked me what is recompilation
    of store procedure ? i m not able to give answer so please
    let me know answer of this question

    regards
    sharad

    Reply
  • Reply
  • mahesh kumar
    May 31, 2011 4:37 pm

    The function or expression “like” in a query from .Net SqlClient Data Provider in [usf] submitted from 15814-68893 by utsav can cause index suppression resulting in poor performance due to a scan being performed instead of a seek.

    Reply

Leave a Reply

Menu