Rookie Learn sqlserver--page and District

In order to deepen their understanding, clarify their knowledge points or vague, which has mastered, so opened blog Park blog. We hope to learn more about the communication opportunities offered by this platform, and fix your mistakes.

Why choose SQL Server, this blog park is mostly. NET Warrior, and now a program often can't get out of the database.

Gossip don't say, first come first: pages and districts

First look at the MSDN definition:

The basic unit of data storage in SQL Server is the page. The disk space allocated for the data file (. mdf or. ndf) in the database can be logically divided into pages (numbered 0 through n consecutively). Disk I/O operations are performed at the page level. In other words, SQL Server reads or writes all data pages.

A zone is a collection of eight physically contiguous pages that are used to manage pages efficiently. All pages are stored in the zone.

Seeing this makes me think of the logical structure of the hard disk, sectors and clusters.

Page:

The size of the page is 8KB, and the area is 8 physically contiguous pages that is 8*8KB=64KB

The first 96 bytes in the page store the system information, and the rest is the storage of our data.

-----------------------------------------------------------------------------

So what does the actual page look like?

With the 3604 trace open, the DBCC PAGE command can be used to output:

DBCC traceon (3604)DBCC page ('AdventureWorks2014', 1,783,3)--DBCC page ({' dbname ' | dbid}, FileNum, Pagenum [, PRINTOPT={0|1|2|3}])

***************************************************

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

PAGE: (1:783)

BUFFER:

BUF @0x000000013c03f480

bpage = 0x000000012dd48000 Bhash = 0x0000000000000000 Bpageno = (1:783)
Bdbid = 7 Breferences = 0 Bcputicks = 0
Bsamplecount = 0 BUse1 = 24783 bstat = 0x9
Blog = 0x15ab215a bnext = 0x0000000000000000

PAGE HEADER:

Here's what we're talking about. Header, size 96 bytes
Page @0x000000012dd48000

M_pageid = (1:783) m_headerversion = 1 M_type = It can be seen from the m_type that this is an IAM page
M_typeflagbits = 0x0 M_level = 0 M_flagbits = 0x200
M_objid (allocunitid.idobj) = M_indexid (allocunitid.idind) = 256
Metadata:allocunitid = 72057594053656576
Metadata:partitionid = 72057594047102976 Metadata:indexid = 1
Metadata:objectid = 1765581328 M_prevpage = (0:0) M_nextpage = (0:0)
Pminlen = m_slotcnt = 2 M_freecnt = 6
M_freedata = 8182 m_reservedcnt = 0 M_lsn = (42:6456:383)
m_xactreserved = 0 M_xdesid = (0:0) m_ghostreccnt = 0
M_tornbits = 653052196 DB Frag ID = 1

Allocation Status

GAM (1:2) = Allocated SGAM (1:3) = Not allocated
PFS (1:1) = 0x70 Iam_pg Mixed_ext Allocated 0_pct_full DIFF (1:6) = Not CHANGED
ML (1:7) = Not min_logged

Iam:header @0x000000000d21a064 slot 0, offset 0 slot 96 bytes, confirming that the previous page header with 96 bytes of record information

SequenceNumber = 0 Status = 0x0 objectId = 0
IndexID = 0 Page_count = 0 Start_pg = (1:0)

Iam:single Page Allocations @0x000000000d21a08e

Slot 0 = (1:903) Slot 1 = (1:914) Slot 2 = (1:915)
Slot 3 = (1:916) Slot 4 = (1:917) Slot 5 = (1:918)
Slot 6 = (1:919) Slot 7 = (1:920)

Iam:extent Alloc Status Slot 1 @0x000000000d21a0c2

(1:0)-(1:1240) = Not allocated Why is 1240 next 1248? Because a district is 8 pages,
(1:1248)-(1:2992) = Allocated
(1:3000)-(1:3032) = Not allocated
(1:3040)-(1:4160) = Allocated
(1:4168)-(1:4216) = Not allocated
(1:4224)-(1:4712) = Allocated
(1:4720)-= Not Allocated
(1:4728)-(1:4888) = Allocated
(1:4896)-= Not Allocated
(1:4904)-(1:4912) = Allocated
(1:4920)-(1:4928) = Not allocated
(1:4936)-(1:4976) = Allocated
(1:4984)-(1:4992) = Not allocated
(1:5000)-(1:5064) = Allocated
(1:5072)-(1:8880) = Not allocated
(1:8888)-(1:9048) = Allocated
(1:9056)-(1:26264) = Not allocated

DBCC execution is complete. If DBCC outputs an error message, contact your system administrator.

***************************************************

Area:

Once you understand the page, the area is easy to understand. 8 pages form a district, the size is 8*8kb=64kb.

There are two types of zones: mixed zone and unified zone

Take a look at the two definitions on MSDN:

• A unified zone, owned by a single object. All 8 pages in the zone can only be used by the owning object.

• Mixed area, can be shared by up to eight objects. Each page of page eight in the zone can be owned by a different object.

It's easy to understand that if our database has a lot of very few objects, such as indexes or tables with very few rows of data

Then we allocate a page to them enough to accommodate it so that it can be placed in a mixed area. If we allocate a zone 64KB to these small objects, then disk space will be wasted

If there is a large data table, it takes up more than one area of capacity, then we can use the unified area to manage the page of this object

The above is the understanding after I view MSDN, if the mistake to forgive, please give correct

Reference:

https://msdn.microsoft.com/zh-cn/library/ms190969 (v=sql.105). aspx

Http://www.cnblogs.com/Amaranthus/archive/2011/05/03/2035497.html

Rookie Learn sqlserver--page and District