none
sp_recompile on tables

    Question

  • Hello -

    After we deploy to prod, we notice that sometimes stored procedures are running slow.  After we run sp_recompile, then the run normal.  We are thinking about proactively running sp_recomile on on all tables that will be modified during a deployment.  Are their potential negative consequences to this?  If 50 stored procs reference a table, then all 50 of those procs will then be marked for recompile, and will be recompiled on next execution, correct?  Also, if the parms going into any of those procs start to vary outside of their normal bounds, will this have a negative impact on performance?

    Thank you in advance!

    Friday, July 19, 2013 6:48 PM

Answers

  • Hello -

    After we deploy to prod, we notice that sometimes stored procedures are running slow.  After we run sp_recompile, then the run normal.  We are thinking about proactively running sp_recomile on on all tables that will be modified during a deployment. 

    .

    Are their potential negative consequences to this?

    sp_recompile causes stored procedures and triggers to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. why does the SQL server keep in cache if there is no consequences to build it every time?!? yes there is consequences, as bulding the execution plain can be longer sometime then the execution of the query it self :-)

    If 50 stored procs reference a table, then all 50 of those procs will then be marked for recompile, and will be recompiled on next execution, correct?

    you can do something smarter and just recompile the execution that you need.example causes stored procedures and triggers that act on the Customer table to be recompiled:

    EXEC sp_recompile N'Sales.Customer';
    GO

    you do not have to recompile all the cache.

    Also, if the parms going into any of those procs start to vary outside of their normal bounds, will this have a negative impact on performance?

    i am not sure that i understand what you mean, but as the execution plain is build the first time you run the query then it is possible that next time you use different parameter value then the first time and then you get slow query since the execution was build for the first parameter. some time it is smart to divide the SP into several SP which are all the same. using main SP you point to the specific SP . like for parameter 1-100 you run SP-01, for parameters 101-1000 you run the SP-02. in this way (when this is the best way) the server will build an execution plain Which corresponds to the parameter. like always it is not always the best way but some time it can bring Very serious optimizationץ

    signature

    Friday, July 19, 2013 7:37 PM

All replies

  • sp_recomile just regenerate the new execution plan after deleting the older cached one, if you see no changes in DB structure and data and you dont want to change the plan then no need to sp_recomile, but once you first time in prod it is advisable to add this if you done good amount of changed used in those procs.

    so if you recompile a table stored procedures and triggers that act on the table to be recompiled the next time that they are run


    Thanks- Prajesh Please mark the post as answered if it answers your question


    • Edited by Prajesh Friday, July 19, 2013 7:30 PM edited data to better answer
    Friday, July 19, 2013 7:29 PM
  • Hello -

    After we deploy to prod, we notice that sometimes stored procedures are running slow.  After we run sp_recompile, then the run normal.  We are thinking about proactively running sp_recomile on on all tables that will be modified during a deployment. 

    .

    Are their potential negative consequences to this?

    sp_recompile causes stored procedures and triggers to be recompiled the next time that they are run. It does this by dropping the existing plan from the procedure cache forcing a new plan to be created the next time that the procedure or trigger is run. why does the SQL server keep in cache if there is no consequences to build it every time?!? yes there is consequences, as bulding the execution plain can be longer sometime then the execution of the query it self :-)

    If 50 stored procs reference a table, then all 50 of those procs will then be marked for recompile, and will be recompiled on next execution, correct?

    you can do something smarter and just recompile the execution that you need.example causes stored procedures and triggers that act on the Customer table to be recompiled:

    EXEC sp_recompile N'Sales.Customer';
    GO

    you do not have to recompile all the cache.

    Also, if the parms going into any of those procs start to vary outside of their normal bounds, will this have a negative impact on performance?

    i am not sure that i understand what you mean, but as the execution plain is build the first time you run the query then it is possible that next time you use different parameter value then the first time and then you get slow query since the execution was build for the first parameter. some time it is smart to divide the SP into several SP which are all the same. using main SP you point to the specific SP . like for parameter 1-100 you run SP-01, for parameters 101-1000 you run the SP-02. in this way (when this is the best way) the server will build an execution plain Which corresponds to the parameter. like always it is not always the best way but some time it can bring Very serious optimizationץ

    signature

    Friday, July 19, 2013 7:37 PM