ObjID in Page Header












5















I am reading this article: Inside the Storage Engine: Anatomy of a page.



I have a database MyDB, and there is a table MyTable in the database.



I have the following questions:





  1. If I do as follows:



    (1) use the following query to find the object ID for a table:



    Use MyDB;
    select sys.objects.name, sys.objects.object_id from sys.objects where (name = 'MyTable');


    (2) then use the following command to find all pages allocated to MyTable:



    dbcc ind(MyDB, 'MyTable', -1);


    (3) then in the result table, I choose one of the data pages (page type = 1), and use the following command:



    DBCC TRACEON(3604);
    DBCC PAGE (MyDB, 1, 17386, 3);


    Then in the dumped content (page header) of step (3), the m_objId (AllocUnitId.idObj) field should equal to the object ID obtained in step (1). Is that correct?



    And whether that works for both user tables and system base tables, such as sys.syscolpars base table?



    Based on my test, the above two conclusion are both correct.



  2. What is the meaning of Metadata: ObjectId? In article, the ‘metadata: objectId’ <> m_objId. But from my own tests, the ‘metadata: objectId’ always equals to m_objId. Why? The original article does not explain the metadata clearly.



I am using SQL Server 2005, 2008, 2008 R2, 2012 and 2014










share|improve this question
















bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • If you don't get an answer to this it might be worth trying Paul Randal. I don't believe he frequents this forum but I know I've seen him on sqlservercentral.com, otherwise his details are available on his blog: sqlskills.com/blogs/paul. Just FYI, Paul's team wrote a lot of the internals of DBCC back in the 2005 days so he'll most likely have an answer.

    – dwjv
    Dec 9 '15 at 9:36











  • Also, with question 1, yes you are correct.

    – dwjv
    Dec 9 '15 at 9:48











  • There's a "Leave a Reply" form under the article, you could ask your question right there.

    – Andriy M
    Dec 9 '15 at 12:33
















5















I am reading this article: Inside the Storage Engine: Anatomy of a page.



I have a database MyDB, and there is a table MyTable in the database.



I have the following questions:





  1. If I do as follows:



    (1) use the following query to find the object ID for a table:



    Use MyDB;
    select sys.objects.name, sys.objects.object_id from sys.objects where (name = 'MyTable');


    (2) then use the following command to find all pages allocated to MyTable:



    dbcc ind(MyDB, 'MyTable', -1);


    (3) then in the result table, I choose one of the data pages (page type = 1), and use the following command:



    DBCC TRACEON(3604);
    DBCC PAGE (MyDB, 1, 17386, 3);


    Then in the dumped content (page header) of step (3), the m_objId (AllocUnitId.idObj) field should equal to the object ID obtained in step (1). Is that correct?



    And whether that works for both user tables and system base tables, such as sys.syscolpars base table?



    Based on my test, the above two conclusion are both correct.



  2. What is the meaning of Metadata: ObjectId? In article, the ‘metadata: objectId’ <> m_objId. But from my own tests, the ‘metadata: objectId’ always equals to m_objId. Why? The original article does not explain the metadata clearly.



I am using SQL Server 2005, 2008, 2008 R2, 2012 and 2014










share|improve this question
















bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • If you don't get an answer to this it might be worth trying Paul Randal. I don't believe he frequents this forum but I know I've seen him on sqlservercentral.com, otherwise his details are available on his blog: sqlskills.com/blogs/paul. Just FYI, Paul's team wrote a lot of the internals of DBCC back in the 2005 days so he'll most likely have an answer.

    – dwjv
    Dec 9 '15 at 9:36











  • Also, with question 1, yes you are correct.

    – dwjv
    Dec 9 '15 at 9:48











  • There's a "Leave a Reply" form under the article, you could ask your question right there.

    – Andriy M
    Dec 9 '15 at 12:33














5












5








5








I am reading this article: Inside the Storage Engine: Anatomy of a page.



I have a database MyDB, and there is a table MyTable in the database.



I have the following questions:





  1. If I do as follows:



    (1) use the following query to find the object ID for a table:



    Use MyDB;
    select sys.objects.name, sys.objects.object_id from sys.objects where (name = 'MyTable');


    (2) then use the following command to find all pages allocated to MyTable:



    dbcc ind(MyDB, 'MyTable', -1);


    (3) then in the result table, I choose one of the data pages (page type = 1), and use the following command:



    DBCC TRACEON(3604);
    DBCC PAGE (MyDB, 1, 17386, 3);


    Then in the dumped content (page header) of step (3), the m_objId (AllocUnitId.idObj) field should equal to the object ID obtained in step (1). Is that correct?



    And whether that works for both user tables and system base tables, such as sys.syscolpars base table?



    Based on my test, the above two conclusion are both correct.



  2. What is the meaning of Metadata: ObjectId? In article, the ‘metadata: objectId’ <> m_objId. But from my own tests, the ‘metadata: objectId’ always equals to m_objId. Why? The original article does not explain the metadata clearly.



I am using SQL Server 2005, 2008, 2008 R2, 2012 and 2014










share|improve this question
















I am reading this article: Inside the Storage Engine: Anatomy of a page.



I have a database MyDB, and there is a table MyTable in the database.



I have the following questions:





  1. If I do as follows:



    (1) use the following query to find the object ID for a table:



    Use MyDB;
    select sys.objects.name, sys.objects.object_id from sys.objects where (name = 'MyTable');


    (2) then use the following command to find all pages allocated to MyTable:



    dbcc ind(MyDB, 'MyTable', -1);


    (3) then in the result table, I choose one of the data pages (page type = 1), and use the following command:



    DBCC TRACEON(3604);
    DBCC PAGE (MyDB, 1, 17386, 3);


    Then in the dumped content (page header) of step (3), the m_objId (AllocUnitId.idObj) field should equal to the object ID obtained in step (1). Is that correct?



    And whether that works for both user tables and system base tables, such as sys.syscolpars base table?



    Based on my test, the above two conclusion are both correct.



  2. What is the meaning of Metadata: ObjectId? In article, the ‘metadata: objectId’ <> m_objId. But from my own tests, the ‘metadata: objectId’ always equals to m_objId. Why? The original article does not explain the metadata clearly.



I am using SQL Server 2005, 2008, 2008 R2, 2012 and 2014







sql-server metadata






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 9 '15 at 8:58









Andriy M

16.1k63473




16.1k63473










asked Dec 9 '15 at 7:24









user2347976user2347976

1074




1074





bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 4 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • If you don't get an answer to this it might be worth trying Paul Randal. I don't believe he frequents this forum but I know I've seen him on sqlservercentral.com, otherwise his details are available on his blog: sqlskills.com/blogs/paul. Just FYI, Paul's team wrote a lot of the internals of DBCC back in the 2005 days so he'll most likely have an answer.

    – dwjv
    Dec 9 '15 at 9:36











  • Also, with question 1, yes you are correct.

    – dwjv
    Dec 9 '15 at 9:48











  • There's a "Leave a Reply" form under the article, you could ask your question right there.

    – Andriy M
    Dec 9 '15 at 12:33



















  • If you don't get an answer to this it might be worth trying Paul Randal. I don't believe he frequents this forum but I know I've seen him on sqlservercentral.com, otherwise his details are available on his blog: sqlskills.com/blogs/paul. Just FYI, Paul's team wrote a lot of the internals of DBCC back in the 2005 days so he'll most likely have an answer.

    – dwjv
    Dec 9 '15 at 9:36











  • Also, with question 1, yes you are correct.

    – dwjv
    Dec 9 '15 at 9:48











  • There's a "Leave a Reply" form under the article, you could ask your question right there.

    – Andriy M
    Dec 9 '15 at 12:33

















If you don't get an answer to this it might be worth trying Paul Randal. I don't believe he frequents this forum but I know I've seen him on sqlservercentral.com, otherwise his details are available on his blog: sqlskills.com/blogs/paul. Just FYI, Paul's team wrote a lot of the internals of DBCC back in the 2005 days so he'll most likely have an answer.

– dwjv
Dec 9 '15 at 9:36





If you don't get an answer to this it might be worth trying Paul Randal. I don't believe he frequents this forum but I know I've seen him on sqlservercentral.com, otherwise his details are available on his blog: sqlskills.com/blogs/paul. Just FYI, Paul's team wrote a lot of the internals of DBCC back in the 2005 days so he'll most likely have an answer.

– dwjv
Dec 9 '15 at 9:36













Also, with question 1, yes you are correct.

– dwjv
Dec 9 '15 at 9:48





Also, with question 1, yes you are correct.

– dwjv
Dec 9 '15 at 9:48













There's a "Leave a Reply" form under the article, you could ask your question right there.

– Andriy M
Dec 9 '15 at 12:33





There's a "Leave a Reply" form under the article, you could ask your question right there.

– Andriy M
Dec 9 '15 at 12:33










1 Answer
1






active

oldest

votes


















0














First of all, Paul mentions this about those "Metadata" fields in the blog post:




Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.




So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.



The other part of the question was eventually answered in the linked blog post as follows:




The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.




So the m_objId will match for system tables (and upgrades from SQL Server 2000). But for others, he says it's "part of the allocation unit ID." That's a bit vague.



Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:



m_objId (AllocUnitId.idObj) = 178
Metadata: ObjectId = 901578250


I can confirm that 901578250 matches what's in sys.objects object_id for this table.



So where does 178 come from?



Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?



In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).



So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit. It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).






share|improve this answer























    Your Answer








    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "182"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123262%2fobjid-in-page-header%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    0














    First of all, Paul mentions this about those "Metadata" fields in the blog post:




    Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.




    So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.



    The other part of the question was eventually answered in the linked blog post as follows:




    The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.




    So the m_objId will match for system tables (and upgrades from SQL Server 2000). But for others, he says it's "part of the allocation unit ID." That's a bit vague.



    Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:



    m_objId (AllocUnitId.idObj) = 178
    Metadata: ObjectId = 901578250


    I can confirm that 901578250 matches what's in sys.objects object_id for this table.



    So where does 178 come from?



    Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?



    In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).



    So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit. It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).






    share|improve this answer




























      0














      First of all, Paul mentions this about those "Metadata" fields in the blog post:




      Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.




      So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.



      The other part of the question was eventually answered in the linked blog post as follows:




      The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.




      So the m_objId will match for system tables (and upgrades from SQL Server 2000). But for others, he says it's "part of the allocation unit ID." That's a bit vague.



      Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:



      m_objId (AllocUnitId.idObj) = 178
      Metadata: ObjectId = 901578250


      I can confirm that 901578250 matches what's in sys.objects object_id for this table.



      So where does 178 come from?



      Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?



      In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).



      So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit. It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).






      share|improve this answer


























        0












        0








        0







        First of all, Paul mentions this about those "Metadata" fields in the blog post:




        Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.




        So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.



        The other part of the question was eventually answered in the linked blog post as follows:




        The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.




        So the m_objId will match for system tables (and upgrades from SQL Server 2000). But for others, he says it's "part of the allocation unit ID." That's a bit vague.



        Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:



        m_objId (AllocUnitId.idObj) = 178
        Metadata: ObjectId = 901578250


        I can confirm that 901578250 matches what's in sys.objects object_id for this table.



        So where does 178 come from?



        Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?



        In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).



        So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit. It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).






        share|improve this answer













        First of all, Paul mentions this about those "Metadata" fields in the blog post:




        Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.




        So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.



        The other part of the question was eventually answered in the linked blog post as follows:




        The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.




        So the m_objId will match for system tables (and upgrades from SQL Server 2000). But for others, he says it's "part of the allocation unit ID." That's a bit vague.



        Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:



        m_objId (AllocUnitId.idObj) = 178
        Metadata: ObjectId = 901578250


        I can confirm that 901578250 matches what's in sys.objects object_id for this table.



        So where does 178 come from?



        Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?



        In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).



        So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit. It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jul 19 '18 at 19:20









        jadarnel27jadarnel27

        6,02812038




        6,02812038






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Database Administrators Stack Exchange!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123262%2fobjid-in-page-header%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            Popular posts from this blog

            Liste der Baudenkmale in Friedland (Mecklenburg)

            Single-Malt-Whisky

            Czorneboh