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 (http://blog.SQLAuthority.com), BOL

About these ads

7 thoughts on “SQL SERVER – Recompile All The Stored Procedure on Specific Table

  1. 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

  2. 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

  3. 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.

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #035 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s