(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)
Earlier today I was answering a question on the #sqlhelp tag on Twitter and I mentioned using object ID 99 as a value, because SQL Server will never set a table to be object ID 99. And so I thought it would make a good topic for a quick blog post.
So what is object ID 99? It’s a reserved object ID that the Storage Engine uses for any data file pages that are part of the allocation system and not owned by a real table. The list of such pages includes things like file header pages, PFS pages, GAM pages, the boot page, and so on – the pages used by the allocation system to track what’s happening at the database level. It doesn’t include IAM pages, as those are part of a table.
You can read more about the various allocation pages in my post Inside The Storage Engine: GAM, SGAM, PFS and other allocation maps.
Let’s look at the page header of a GAM page:
DBCC TRACEON (3604); DBCC PAGE (N'master', 1, 2, 0); -- dbname, file ID, page ID, dump style -- Page (1,2) is the first GAM page in a data file -- Dump style 0 = just the page header GO
Page @0x00000002F83A6000 m_pageId = (1:2) m_headerVersion = 1 m_type = 8 m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x0 m_objId (AllocUnitId.idObj) = 99 m_indexId (AllocUnitId.idInd) = 0 Metadata: AllocUnitId = 6488064 Metadata: PartitionId = 0 Metadata: IndexId = 0 Metadata: ObjectId = 99 m_prevPage = (0:0) m_nextPage = (0:0) pminlen = 90 m_slotCnt = 2 m_freeCnt = 6 m_freeData = 8182 m_reservedCnt = 0 m_lsn = (5081:482:5) m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0 m_tornBits = 39122617 DB Frag ID = 1
You can see that the m_objId is 99. Since SQL Server 2005, allocation pages are the only ones where the m_objId is the actual object ID. In all other cases, the m_objId and m_indexId are derived from the allocation unit ID that the page is allocated to, and the allocation unit ID reported by DBCC PAGE is calculated as:
(m_objId << 16) | (m_indexId << 48)
(where << means left-shift and | means logical-OR)
You can read more about this in my post Inside the Storage Engine: How are allocation unit IDs calculated?
The only other ‘not real’ object ID you might see is a value of 0 being reported by DBCC CHECKDB, when it can’t figure out which object a page really belongs to – see Disaster recovery 101: Object ID 0, index ID -1, partition ID 0 for more details.
8 thoughts on “The Curious Case of… object ID 99”
This morning we waked up with an almost non-operative SQL Server 2012 Enterprise instance, with hundreds of requests stacking, most of them with a PAGELATCH_UP wait type on a very specific resource.
Using DBCC Page to get son info about that resource, pointed to a specific page in one only file of tempdb. It was not an GAM, SGAM, PFS page (as for it position in the file) and it happened to be a page of the object_id 99.
Only restarting the instance has resolve the issue. I was not able of getting information about which of the running queries (if was only one) was causing this.
There are a lot of questions about this incident coming to my head:
— with an 8 file tempdb, why all the contention was in 1 file?
— Maybe object creation rate building new metadata pages for that objects?
Anyway, I am adding here only for the sake of curiosity.
Thanks for your work and all your help !!!
You missed the most important piece of information – what was the exact page ID?
And what are all the file sizes? And which version?
Sorry for my lack of details.
select @@version = Microsoft SQL Server 2012 (SP4) (KB4018073) – 11.0.7001.0 (X64) Aug 15 2017 10:23:29 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)
8 tempdb files initially configured with 12 Gb.
The exact page was: 2:3:3578624.
Querying sys.dm_os_waiting_tasks, there were lots of connections waiting for PageLatch_UP on that page.
By doing DBCC Page, I was able to see that the object_id was 99.
I would have liked to have been able to get more information, but you know is difficult to get time when the business is not working.
That’s exactly divisible by 511232 to it’s a GAM page (which extents are available or in use). It’s very unusual to see contention on a single GAM page, unless in tempdb that file 3 is the only one that’s grown, as that page ID says it’s at least ~27GB. That could be the issue and you can easily check if it’s the largest file by far. If so, you need to enable trace flag 1117 as a startup parameter.
Oh, that’s for sure the cause. I don´t know how I did the maths in the panic time but I was completely sure It was not a GAM page.
I have checked that we had TF 1117 not enabled.
Thank you so much for your help !!!!
I don´t know the kind of maths I did during the panic time I was sure it wasn´t a GAM or SGAM page :-)
We will enable TF 1117 and will be monitoring closely.
Thank you so much for your help.