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?
Best Answer
The SQL Server engine doesn't explicitly call
sp_recompilein 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.