SQL Server sp_recompile on a table

Question

When sp_recompile is run against a table, I understand that all stored procedures and triggers dependent on that table will be recompiled.

What I don't understand is what parameters SQL Server uses for this recompile. I can't see how parameter sniffing would factor in here. Does it compile an execution plan that is 'generic' using something similar to OPTIMIZE FOR UNKNOWN?

I feel like I'm missing something really obvious.

Does anyone have an understanding of this?

It doesn't recompile the stored procedures. It *marks* them for recompile. They'll be recompiled the next time they are run. — Aaron Bertrand, Apr 25 '13 at 01:41
Am I correct that this is the same thing that happens when indexes are rebuilt or statistics refreshed? — PseudoToad, Apr 25 '13 at 15:04
Aaron, if you could put this in as an answer, I'd be happy to give you credit on it. — PseudoToad, May 01 '13 at 18:58

score 3 · Accepted Answer · answered Apr 29 '13 at 09:30

3

sp_recompile do not executes a recompilation of the objects itself. It deletes only all saved execution-plans. This forces an recompilation by the next call of the object (with the parameters of this next call).

answered Apr 29 '13 at 09:30

Stefan

46
1

SQL Server sp_recompile on a table

1 Answers1