In a database with only tables and indexes (ie no stored procs, functions, triggers, etc). We get a call to sp_recompile that blocks table selects / updates randomly (ie once a month but in a highly transactional system. Catching this via trace has been a challenge. The local DBA seems to only capture the statement in a blocking lock tree. It is my understanding that SQL Server uses some other method (ie. not a direct call to sp_recompile) to trigger the internal recompiles when needed for plan cache / object updates / aging.

I see from books online that you can call sp_recompile with a table object, which triggers a cascading flag change to recompile any objects related the next time it executes, but this seems to be directed toward other DB objects, not tables and indexes. So does anyone know if SQL Server calls / triggers sp_recompile directly?

  • 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). – CR241 Dec 17 '18 at 18:35
  • Correct. But when SQL Server deems a recompile is necessary, does it call sp_recompile on the object or use some other method to trip the flag? I am trying to determine if this is by design or a user / session / app triggering these odd recompile requests. – DBAQueryDude Dec 17 '18 at 18:40
  • I don't think app triggers recompile.. It's internal SQL server operation, it might be adding indexes or changing data in indexed columns, the original query plans used to access its tables should be optimized again by recompiling them.. For more info Check this.. stackoverflow.com/questions/6618061/… and this too.. serverfault.com/questions/51366/… – CR241 Dec 17 '18 at 18:47
  • If the plan for a table is needing recompile, then does SQL Server call sp_recompile for the flagging process or does it flag the recompile using a different method internally? Examples above are related to a stored procedure, which we don't have. Any recompiles would be triggered by a bad plan on a query... trying to understand if SS calls sp_recompile (thus causing our blocking) or this is from a user / app driven call. – DBAQueryDude Dec 17 '18 at 19:09
  • 1
    Does the db have views ? – Kin Shah Dec 17 '18 at 19:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.