You are a database administrator for AIOTestKing.com. You run SQL Server Profiler by using the SP:Recompile event class. You notice a large number of recompiles occurring.
You need to identify the possible cause of the large number of recompiles. What is the most likely cause of the problem?
A.
A database administrator created one new stored procedure.
B.
Ad hoc batches are not being retrieved from the procedure cache.
C.
The sp_executesql batches are not being retrieved from the procedure cache.
D.
A database administrator executed UPDATE STATISTICS statements on all tables that are referenced by the most common stored procedures.
Explanation:
The SP:Recompile event class indicates that a stored procedure, trigger, or user-defined function has been recompiled. The most probable cause for a massive recompiles occurring is that someone has issued an UPDATE STATISTICS command.