question

Raj More avatar image
Raj More asked

why to sp_recompile?

What are the cases when one should run a sp_recompile?

sql-server-2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

When there is a large change in the data that could affect the accuracy of the statistics that guide the optimisers decisions when it creates a query plan. If the mass update or insertion of data takes place after a procedure has its query plan generated then the plan could be 'non-optimal', setting sp_recompile for the tables affected will cause the optimiser to create a new plan.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

As other answers have mentioned, SQL is pretty good at updating stats and then recompiling plans. If you want to do it, just for your own peace of mind then jts_2003 explains a fair process below
1 Like 1 ·
Do I have to rebuild the index before I run `sp_recompile`?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

Even without large scale spot changes to the tables, you might see incremental changes over time that will affect the statistics on a table. You might need to schedule sp_recompile just occasionally, depending on the system requirements, load, execution plans, etc.. But, for this instance, it's usually enough to simply update to the statistics and then any procedures that use those statistics will be marked for recompile the next time they execute.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnStaffordDBA avatar image
JohnStaffordDBA answered

For tables where we know there is a lot of activity, we do the following:

  1. Reindex
  2. Update Statistics
  3. Run sp_recompile

So, the indexes should be in the best possible state, the query optimizer knows what state they're in and the sprocs will definitely get recompiled next time they're used, to take advantage of all this.

Not sure how necesary this is in SQL 2008, but we definitely found it worthwhile in SQL 2000.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.