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