Revisiting Inside Tempdb….
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).
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.