SQL 2008 Blocking on 'drop table #temp'

We experienced major database blocking caused by a 'drop table #temp' command at the end of a stored procedure.

Along with the obvious database blocking we were experiencing off blocking spids (-4 session_id as the blocker) and unusual wait resources (2:1:103). All the unusaul activity was predicated off the 'drop table' statement.

I had to research spid = -4... Here is the simplified definition...

-4 is the session_ID of the blocking latch owner that could not be determined due to internal latch state transitions...

Course of Action:
We executed blocking and process execution queries using many DMV's. We came across something pretty interesting. All the blocks had a waitresource of (2:1:103).



We executed the following: undocumented DBCC but full of information - (use it!)

dbcc page (2,1,103,3)



The result set is quite extensive but we were looking for the metadata:objectID... The objectID was 75.

Then we executed:

use tempdb

select object_name(75)

The result set was 'sysmultiobjrefs'.

During the findings I was working with Microsoft...

Here is what we found:

There is a bug in SQL 2008 which actually started in SQL 2005 and will not be fixed until Denali rolls out. Yea!!!!!

http://technet.microsoft.com/en-us/library/cc966545.aspx

About 2/3 of the way down you'll find:

Troubleshooting contention in DDL operations

This is directly from Microsoft:
Troubleshooting contention in DDL operations
Evaluate your application and query plans and see if you can minimize the creation of temporary tables. To do this, monitor the perfmon counters Temp Tables Creation Rate and Temp Tables For Destruction. You can also run SQL Profiler to correlate the values of these counters with the currently running queries. This will help you identify the queries that are causing the contention in system catalog. This might occur, for example, if a temporary object is being created inside a loop or a stored procedure.
Verify if temp objects (temp tables and variables) are being cached. SQL2005 caches Temp objects only when the following conditions are satisfied:
• Named constraints are not created.
• Data Definition Language (DDL) statements that affect the table are not run after the temp table has been created, such as the CREATE INDEX or CREATE STATISTICS statements.
• Temp object is not created by using dynamic SQL, such as: sp_executesql N'create table #t(a int)'.
• Temp object is created inside another object, such as a stored procedure, trigger, and user-defined function; or is the return table of a user-defined, table-valued function.

Notice the second bullet - that was our problem..

Once we saw the code, we realized the create index after the temp table insert was causing the issue.

I literally commented out the create index code, altered the procedure and the blocking subsided immediately.

As previously mentioned, this is a bug in SQL server that arises during high usage. Unfortunately, this procedure is executed multiple times per second and the system was unable to cache the temp tables.