4

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?

Improve this question
3
  • 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.
    – user168304
    Commented Dec 17, 2018 at 18:40
  • 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.
    – user168304
    Commented Dec 17, 2018 at 19:09
  • Yes. The database does have views.
    – user168304
    Commented Dec 18, 2018 at 20:08

1 Answer 1

Reset to default
1

The SQL Server engine doesn't explicitly call sp_recompile in order to remove plans from the cache. As far as I know anyway. There might be features of SQL Server that do this, but I haven't heard of it.

The mostly likely culprit is that some individual or user process is calling sp_recompile. Hopefully you can tune the tracing process you mentioned to capture the user executing this call.

I recently noticed that calling this on a table or view takes a SCH-M lock on that table or view, which would explain the blocking (no reads or writes can take place against the table with this lock in place). It does not take very long on the systems I have access to, so I'm not sure why it would cause blocking for a long time.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.