Post reply

Trace Flag T1118

  • July 6, 2012 at 2:31 am

    #258560

    I understand that trace flag T1118 helps allieviate contention on SGAM pages by ensuring uniform extents are allocated to tempdb structures.

    But how does this not just make the GAM page a new bottleneck? if the GAM page is now being utilized just as much as the SGAM was, wont it now be the point of contention?

  • July 6, 2012 at 3:04 am

    #1509532

    Firstly, 1118 doesn't affect TempDB. It affects all databases.

    Maybe, maybe not. Depends how large your temp tables are. SQL allocates the first 8 pages from mixed extents by default, with 1118 on it allocates the first 8 pages from a dedicated extent.

    So let's say you create a temp table and insert 60kb of data into it. That's 8 pages. Normally that means 8 hits to the SGAM looking for free pages in mixed extents and probably one or two writes to the SGAM to either mark as mixed a new extent or to mark as full an existing mixed extent. With 1118 on, that's one read and one write to the GAM to find and mark as allocated a new dedicted extent.

    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

  • July 6, 2012 at 6:23 am

    #1509639

    First of all, although I am not the thread starter I'd like to thank Gail for the short the concise answer.

    Unfortunately, there is no such thing as a free lunch, so what is the drawbacks of enabling T1118? Every table object (including temp tables and table variables) will require a full extent.


    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

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

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