Temp table (#tname) causing database blocking on 'drop table #tname' call w/in stored procedure

  • SQL 2008 SP1 - Cluster running Win2008.

    I have a stored procedure that gets executed multiple times per second. The procedure creates a temp table, performs work on the temp table, then drops the temp table.

    The drop statement blocks other SPIDs performing the same procedure...

    The drop statement causes blocking across the entire platform.

    This appears to have started Friday afternoon....

    Any infomation as to why dropping a temp table would cause blocking is greatly appreciated.

  • there is a forum post on here somewhere where someone compared explicitly dropping a temp table created inside a proc, vs letting the system destroy it automatically when the proc finished and the table dropped out of scope.

    It was the procedureal argument where you should clean up after yourself and destroy any objects you created kind of mentality.

    the post ran the two procs with identical code(except the explicit drop table) a million times, and the one that let the system destroy them was a little bit faster. I think this was a Jeff Moden post)

    i suspect that dropping a table explicitly, before the transaction ends (implicit transaction for the proc that created the table) is causing waiting and row locks in temp.sys.tables, as the transaction has to complete and the locks released before letting someone else drop theirs.

    so my suggestion is to NOT explicitly drop the temp table inside the proc itself. Let SQL do it for you.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi Lowell –

    Thanks for the response… I originally modified the procedure removing the explicit drop w/out much success.

    Here’s what we did to alleviate the blocking:

    The blocking was caused from the ‘drop table #temp’ at the end of the procedure.

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

    Had to research SPID = -4. Here is the simplified definition.

    -4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

    Here is what we did:

    We ran extensive blocking and process execution queries leveraging DMV’s… The interesting part was the waitresource = (2:1:103).

    We executed:

    --undocumented but full of rich information…

    DBCC page (2,1,103,3) –(dbid,file,page,printoption(0,1,2,3))

    The result set is quite extensive.

    The piece of information we needed was ‘metadata: objected = 75’

    Then we ran:

    Use tempdb

    Select object_name(75)

    --result set

    Sysmultiobjrefs

    Once the result set from ‘select object_name(75)’ was identified we worked with Microsoft to address the issue.

    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.

    Please notice the second bullet – this is what was causing our problem. The stored procedure created a temp table, inserted data into the temp table and then created an index. At the end of the procedure we simply dropped the temp table. I simply commented out the ‘create index’ statement and immediately the blocking subsided.

    This is a bug in SQL 2008, which started in SQL 2005. I was told by Microsoft the bug would not be fixed until SQL Denali is rolled out.

    We actually considered implement TF1118 - but it would not have solved our issue...

  • cheshirefox - thanks for posting this. This is very good information.

    One point:

    cheshirefox (11/23/2010)


    Please notice the second bullet – this is what was causing our problem. The stored procedure created a temp table, inserted data into the temp table and then created an index. At the end of the procedure we simply dropped the temp table. I simply commented out the ‘create index’ statement and immediately the blocking subsided.

    By not having an index, you are forcing a table scan on the temp table. If you have a very small number of rows in the temp table, this might be acceptable.

    You CAN build an index as part of the CREATE TABLE statement - as long as it is an index that is created as part of a PRIMARY KEY or UNIQUE constraint. So, if your index is unique, you can still have it by moving it into the CREATE TABLE statement.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • Hi Wayne -

    You are right... I just created the index at table creation time.

    Matt

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply