{"id":6171,"date":"2013-08-01T14:45:11","date_gmt":"2013-08-01T14:45:11","guid":{"rendered":"http:\/\/craftydba.com\/?p=6171"},"modified":"2016-04-21T21:21:31","modified_gmt":"2016-04-21T21:21:31","slug":"page-anatomy-part-2","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6171","title":{"rendered":"Page Anatomy – Part 2"},"content":{"rendered":"

I am going continue my series of articles describing how information is stored and organized in a Microsoft SQL Server database. Today, I am going to talk about the Database Boot Page<\/span>.<\/p>\n

All of the information is a gleamed from SQL Server 2008 Internals book by Kalen Delaney<\/a>. If it is not on your bookshelf, I suggest you go out and by a copy. The online MCM training videos from Paul Randal<\/a> are also an excellent resource.<\/p>\n

A single page (8K) is the smallest unit in which SQL Server allocates resources. However, most times the storage engine allocates resources in extents<\/a> (64K) which are a block of 8 pages. <\/p>\n

There are two types of extents. Mixed extents which contain data from a variety of objects and uniform extents which contain data from one object. Please see image from MSDN below.<\/p>\n

\"\"<\/a><\/p>\n

Now that we know how pages are grouped, where are the different pages in our databases located? The list below shows the first occurrence of each page. More details will be covered in-depth in future page articles.<\/p>\n

Fixed Page Locations<\/u><\/span><\/p>\n

    \n
  1. File Header page is located on page zero.<\/li>\n

    <\/p>\n

  2. Page Free Space (PFS) page is located on page one.<\/li>\n

    <\/p>\n

  3. Global Allocation Map (GAM) page is located on page two.<\/li>\n

    <\/p>\n

  4. Shared Global Allocation Map (SGAM) page is located on page three.<\/li>\n

    <\/p>\n

  5. Differential Change Map (DCM) page is located on page six.<\/li>\n

    <\/p>\n

  6. Minimally Logged (ML) page is located on page seven.<\/li>\n

    <\/p>\n

  7. Database Boot page is located on page nine.<\/li>\n<\/ol>\n

    Where are the data or index pages, the building blocks of every table? There are other pages types inside the database but they do not start at fixed locations. The list below shows all the different page types that you might encounter.<\/p>\n

    Page Types<\/u><\/span><\/p>\n

    1 = Data page
    \n 2 = Index page
    \n 3 = Text mix page
    \n 4 = Text tree page
    \n 7 = Sort page
    \n 8 = GAM page
    \n 9 = SGAM page
    \n 10 = IAM page
    \n 11 = PFS page
    \n 13 = Boot page
    \n 15 = File header page
    \n 16 = Diff map page
    \n 17 = ML map page<\/p>\n

    Again, I will be using my sample [pubs] database that contains one [title] table. The script<\/a> is enclosed just in-case you do not already have it.<\/p>\n

    There are two quick ways to dump the database boot page. The code snippet demonstrating both solutions is below.<\/p>\n

    \r\n--\r\n-- Show the database boot page\r\n--\r\n\r\n-- 1 - database boot page\r\ndbcc dbinfo ('pubs') with tableresults;\r\n\r\n-- enable output to client \r\ndbcc traceon (3604, -1);\r\n\r\n-- 2 - database boot page\r\ndbcc page (pubs, 1, 9, 3);\r\n\r\n-- disable output to client \r\ndbcc traceoff (3604, -1);\r\n\r\n<\/pre>\n
    <\/span><\/p>\n
    The first way is to call the DBCC DBINFO<\/span> undocumented command with the name of the database. The ‘with tableresults’<\/span> option can be used to save the data in a tabular format.  <\/p>\n
    The following fields are of real interest to me.<\/p>\n
      \n
    1. dbi_dbname = name of the database.<\/li>\n
    2. dbi_dbid = id associated with the database.<\/li>\n
    3. dbi_cmptlevel = compatibility level of the database.<\/li>\n
    4. dbi_dbccLastKnownGood = last time a CHECKDB was ran.<\/li>\n<\/ol>\n
      The full first output can be viewed here<\/a>.<\/p>\n
      The second way is to use trace flag 3604 in conjunction with DBCC PAGE command to dump the page information into the query output window.  Supply the undocumented command with the name of the database, the file number, the page number, and the print option (see below).  <\/p>\n
      Print Option Decode<\/u><\/span><\/p>\n
      0 – print just the page header
      \n1 – page header plus per-row hex dumps, plus the page slot array
      \n2 – page header plus whole page hex dump
      \n3 – page header plus detailed per-row interpretation<\/p>\n
      The database boot record is on page nine in the database.  The full second output can be viewed here<\/a>.<\/p>\n
      Next time I will be talking about Global Allocation Map<\/span> (GAM) Pages.  <\/p>\n","protected":false},"excerpt":{"rendered":"
      I am going continue my series of articles describing how information is stored and organized in a Microsoft SQL Server database. Today, I am going to talk about the Database Boot Page. All of the information is a gleamed from SQL Server 2008 Internals book by Kalen Delaney. If it is not on your bookshelf, I suggest you go out and by a copy. The online MCM training videos from Paul Randal are also an excellent resource. A single page (8K) is the smallest unit in which SQL Server allocates…<\/p>\n","protected":false},"author":1,"featured_media":6098,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[901,909,31,908,535,903,536,537,15,910,911,28,29],"class_list":["post-6171","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-db-admin","tag-administrator","tag-database-boot-page","tag-database-developer","tag-dbcc-dbinfo","tag-dbcc-page","tag-dbcc-traceoff","tag-dbcc-traceon","tag-flag-3604","tag-john-f-miner-iii","tag-page-9","tag-page-type-13","tag-sql-server","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6171","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6171"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6171\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6098"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6171"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6171"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6171"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}