SQL Server Data organization structure

Page pages

Each page 8KB, a continuous 8 pages called a district extents,

For example, a DB area of 2.18MB is approximately

2.18 MB (2,293,760 bytes) =2,293,760b/8kb=280 pages = 35 districts

A page can only be owned by one object

View which file belongs to a record in the datasheet which page

SELECT Top 10
%%physloc%%,
Sys.fn_physlocformatter (%%physloc%%) As RIDs
From TableName

--Note: In the 64-bit system, the format of Sys.fn_physlocformatter is sometimes wrong, it needs to be calculated by hand according to Physloc, the method of calculation is

Flashbacks in bytes, such as

0x0702000001002200 flashbacks after the
0x0022000100000207

The first four bits 0022 indicates the slot number 2*16+2 = 34, the next four bits 0001 represents the file number, and the remaining 00000207 indicates the file number 2*16*16+7 = 519

DBCC TraceOn (3604)
DBCC page (database name, 1,40995,0)
DBCC Traceoff (3604)

Unified Zone

The 8 pages in the zone are all one object

Mixed zone

The 8 pages in the zone can be shared by up to 8 objects, and a new table or index assigns pages from a mixed-type zone. When the table or index grows to 8 pages (contains all data sizes such as the table itself and its index)? ), all subsequent allocations use a uniform type of zone.

GAM page

Global Allocation map, with header and some other overhead, 8 000 bytes or 000bit bits available, each bit bit represents a zone (8 pages),0 means used, and 1 represents a free zone .

the 64,000 bit bits represent 64,000 extents 64000*8 page pages, i.e. 2^6 *1000*2^3*8kb=2^12*1000*1000b, approximately 4G space. That is , each 4GB space of a file corresponds to a GAM page.

Sgam page

Shared global partitions, like the GAM a bit represents a zone, the difference is that his 1 represents a mixed area and has free space; 1 indicates unused or no free space

GAM and sgam Relationship table:

Current Use conditions	GAM bit bit setting	Sgam bit settings
Not used at all	1	0
Mixed zone or uniform type Area (uniform zone) that has been used all	0	0
Mixed areas with pages not in use	0	1

Example:

How do I find a mixed area with free space?

Directly find the sgam corresponding bit 1, at this time the corresponding gambit is 0.

Tips:

If no unified extents are available, SQL Server finds the GAM page, assigns a zone, and uses one of the pages as a blending area.

Top 8 Page

The next GAM page appears on each of the 511 230 pages (after approximately 4G space) after the first GAM page (page 2), and the next Sgam page appears on every 511 230 pages after the first SGAM page (Page 3).

Each database file has a page with the page number 0 as the header page, and each file has only one page (the header page and the page number is 0).

Page 0 is the header file page, and Page 1 is the free Space page for pages Space,pfs.

The first 8 data page orders in SQL Server are fixed, so SQL Server can quickly confirm which zones are available and which are not.

Such as:

No. 0 Page	1th page	2nd page	3rd page	4th page	5th page	6th page	7th page
M_type=15	m_type=11	M_type=8	M_type=9	M_type=0	M_type=0	M_type=16	M_type=17
Header File page	PFS Page	GAM page	Sgam page	Keep page	Keep page	DCM Page	BCM Page

In addition to page 9th for the boot page of the database, pages 8th through 173th are related to the SQLSERVER2008 internal system tables, and then from 174th page to No. 279 page are unassigned pages. Because the first page starts at 0, it's just 280 pages, which is exactly the same size as the database data files we see.

See the previous operation, database size: 2.18 MB (2,293,760 bytes) =2,293,760b/8kb=280 pages = 35 extents.

8th page	9th page	10th page	~	173th page	174th to No. 279 page
M_type=1	M_type=13	M_type in (1,2,10)			N/A
Data page	Boot page	Primarily for internal system table related information			Not assigned

Http://files.cnblogs.com/files/thaughtZhao/InternalsViewerInstaller.rar

DBCC checkprimaryfile (' D:\MSSQL\Data\GPOSDB.mdf ', 2)

ALTER DATABASE PRODUCTDB set online--setting online status
Select * from sys.dm_exec_connections--view links

SELECT * FROM sys.database_files--view filegroups

---See the Fileid, PageID for the specific data record

SQL Server Data organization structure