Exclusive Locks in TempDB

  • We have documentation for standardizing SQL queries, etc.  One of the sections referenced the following: 

     

    3.                  Tuning performance tips

     

    B.     Build temp tables with “CREATE/INSERT”, don’t use “SELECT INTO”. The “SELECT INTO” method is easy to code, but requires exclusive locks on the tempdb system tables for the duration of the query. It will block all other processes running at the same time that require creating temp tables, degrading system performance.

    I have never heard this before.  Is it correct? 

    Thanks, 

    Farrell

    I wasn't born stupid - I had to study.

  • Not currently. I believe that was a problem in SQL Server 6.5, and was fixed in 7.0.

    --
    Adam Machanic
    whoisactive

  • It's correct. There will be exclusive locks on system tables e.g. objects in tempdb.

     

  • peterhe,

    Post a reproduction that proves that.

    ... Or better, I'll just debunk this claim, which -- annoyingly -- keeps popping up all over the web in various "tips and tricks" pages:

    (query analyzer window #1)

    set transaction isolation level serializable

    begin transaction

    select *

    into #blah

    from sysobjects

    (now flip to qa window #2)

    select *

    into #blah

    from sysobjects

    ... if creating a temp table using INTO blocked other processes, given the serializable transaction level, the query in window #2 should now be blocked. But it isn't. That issue was fixed a long time ago.

    --
    Adam Machanic
    whoisactive

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

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