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?
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).