Share via

Revisiting Inside Tempdb….

  • Article

As I prepare for my next PASS Summit talk for the upcoming 2012 Summit, Inside SQLOS 2012 I was reviewing my talk from last year, Inside Tempdb, and some of the questions and feedback I received. They say it is never too late to provide all the facts so I realized I neglected to post some more details about some of the questions I received from my talk with some answers.

Worktables have negative objectids

When I made this statement at my talk, someone came up after the session and said they discovered that user-defined temporary tables have an object_id < 0 on SQL Server 2012. This person wanted to know if I had seen this and how this relates to my statement about negative objiectid values for worktables.

I finally sat down and researched this question and have the answer. In SQL Server 2008R2 and previous versions, we generated objectids for user-defined temporary tables just like we do for any user-defined table (the details I’ll not discuss here). But in SQL Server 2012, we made a conscious change to the algorithm so that objectids for user-defined temporary tables would be a particular range of values. Most of the time we use hex arithmetic to define these ranges and for this new algorithm these hex values spill into a specific set of negative numbers for object_id, which is a signed integer or LONG type. So in SQL Server 2012, you will now always see object_id values < 0 for user-defined temp tables when looking at a catalog view like sys.objects.

What about worktables then? My statement in the presentation is still true and worktable objectids remain < 0. But there is a special signature to how we generate this objectid so that the engine knows the pages that are allocated belong to a worktable (because the page type for these are DATA pages) and don’t conflict with the negative id value range for user-defined temp tables. The objectid of a worktable will always be a combination of the fixed value 0X80000000 and a counter value we increment each time we create a new worktable. So an example worktable objected would be 0x80000001. Convert this to decimal as a LONG integer and you get –2147483647. You might wonder how I found a worktable page since allocation of these are not logged and there is no record of the allocation of these in system tables in tempdb. Since these are data pages, they have hashed buffers so you can see these pages in sys.dm_os_buffer_descriptors. Quick way to see this. Run a DBCC CHECKDB and look for DATA pages in sys.dm_os_buffer_descriptors in tempdb. This is easier to find if you don’t have any user-defined temp table activity that could also have pages allocated. When you do this you might run into a page header that looks like this from DBCC PAGE. Note the negative objid value for this page yet m_type = 1 (which is a DATA page).

image

One thing I did not mention at the session is the object id for sort pages (m_type = 7). These will always appear with an objid = 0.

Active transactions in tempdb

On one of my slides I mentioned that the transaction log for tempdb may appear to grow out of control because of an active transaction but that transaction involving user-defined temporary tables was the only scenario I had seen to cause this. Someone from the audience (thank you to whoever you were I don’t remember your name) mentioned that sorts may also cause this. And that information is definitely correct.

If you execute a query that requires a sort operation which requires a “spill”, then the engine must write sort pages to disk and that requires a transaction (technically it requires several transactions but one outer transaction keeps it all active) which will remain active until the sort operation is complete. If you run into a scenario where an active transaction prevents log truncation and you see output like this from DBCC OPENTRAN

Transaction information for database 'tempdb'.

Oldest active transaction:
    SPID (server process ID): 51
    UID (user ID) : -1
    Name          : sort_init
    LSN           : (50:376:631)
    Start time    : Sep  8 2012 11:35:09:983PM
    SID           : 0x010500000000000515000000271a6c07352f372aad20fa5b36110000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

then this indicates a long-running sort is preventing the log from truncating and it will grow until the sort is complete.

These were two topics that were follow-up from my Inside Tempdb talk. If you have seen this talk and have other topics that you have questions about post them as comments to this blog and I’ll respond to them. If they are larger enough topics I’ll edit this blog post with the details as I find the answers.

 

Bob Ward
Microsoft

Comments

  • Anonymous
    September 08, 2012
    Thanks for this useful and clear topic. Even if performances are not among my prefered and well-known topics, i have seen at least twice threads related to this article in the SQL Server Data Access Forum ( i think that some people , not knowing where to post , use this forum as a holdall, hoping that a moderator will do the correct move ). I have appreciated your remark "Someone from the audience (thank you to whoever you were I don’t remember your name) mentioned that sorts may also cause this". I knew the 1st reason, not the 2nd one. It seems that the speed of quick increase of the tempdb logfile is a problem too often overlooked by DBA especially when they are beginners. So , cheers for your article, even if i am not sure to have fully understood every sentence ( mainly because of my poor understanding and writing of the english language ) For the last time , thanks

  • Anonymous
    October 26, 2012
    One quick question reagarding PFS contention. I use a quer similar to one below insert into #a  select top * from a

  1. Can performance of select in above example impact the duration of latch in PFS page?
  2. Does SQL Server allocate extent one by one regardless of number of rows returned by select? Or do we have a logic to allocate more than one extend when SQL Server knows there is going to be more rows.
  3. I see a good difference in PAGELATCH_* (For PFS) wait time, When the select is faster compared with slower.
  • Anonymous
    February 03, 2016
    Thanks for the great article on this topic. We just experienced this twice this week and was wondering if there is a way to detect the open transaction alert it. We monitor log space growth and drive space, and that is a workaround for now. It would be great to detect the open transaction sooner rather than later and cause the log to growth very big. I have been querying the DMVs and it doesn't show that it has a open transaction. The only way to see it is DBCC OPENTRAN on tempdb. Any solution on how to detect this open transaction before it gets out of control?
  • Anonymous
    March 24, 2016
    Do you have any suggestions for how to resolve this long-running sort issue originating in an Access application which apparently has a form bound to sorted results of a query? We see just what you are describing above and would like to find a solution other than periodically killing sessions to allow for log truncation. Thanks.