Post reply

Temporary tables in TEMPDB

  • January 4, 2018 at 11:47 pm

    #341560

    Hi all,

     As i noticed in tempdb temporary tables that many tables are starting with #A..#B as shown below, for what purpose that tables are used? Whether it affects any performance? 

    [dbo].[#A000C3DB]
    [dbo].[#A00135F9]
    [dbo].[#A0018F32]
    .
    .
    .

    [dbo].[#BD72C1DF]
    [dbo].[#BEB48570]

    Regards

    Narine M

  • January 5, 2018 at 12:43 am

    #1974616

    They're either table variables or the shells of temp tables which SQL caches so that it doesn't have to do so much work to create new ones.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 5, 2018 at 5:55 am

    #1974651

    Thank you. Whether it creates any performance issue? How to truncate that tables?

  • January 5, 2018 at 6:27 am

    #1974664

    It prevents performance problems. That's why SQL caches the shells of temp tables, to make the process of creating new temp tables faster.
    And you don't truncate them, because they're empty (if any are not empty, then they're table variables being used by some code in your system)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2018 at 11:29 pm

    #1974848

    Thank you for the quick response. Great Work

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

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