Part of the SQL Server Wait Types Library.
Related deep dive: PAGELATCH_EX Wait Type.
DROPTEMP is recorded between attempts to drop a temporary object after a previous attempt failed, classically because a deadlock occurred while dropping a temp table. The engine backs off and retries, and the wait duration grows exponentially with each failed attempt, the same back-off pattern used for spinlocks and network collisions.
When it fires, the error log carries a matching message beginning “Drop Temp Obj:”, often alongside a deadlock graph.
Is It a Problem?
It should be absent or negligible on healthy modern systems. A SQL Server 2005-era bug made it notorious, but recent versions rarely produce it. If it does appear with real time, something is repeatedly interfering with temp object drops, and the error log entries plus deadlock graphs will name the collision.
Rising DROPTEMP is therefore a prompt to read the log, not to tune tempdb.
Common Causes
- Deadlocks involving temp object metadata during drops, with the back-off retries accruing the wait.
- Historic build defects (largely SQL Server 2005 era).
- Extreme temp object churn increasing the collision odds.
What To Do
- Pull the “Drop Temp Obj:” entries and any deadlock graphs from the error log and system_health; they identify the other party.
- Check build currency if the pattern is persistent; retry-storm defects belong to old builds.
- Reduce needless temp object churn in hot code paths (cached temp tables in procedures help several tempdb pressures at once).
How To See It
Rank it against everything else with Get-WaitStatistics; any visible amount pairs with error log evidence carrying the details.
Leave a Reply