SqlServer Data Organization Structure

Each page on a page is 8 KB, and eight consecutive pages are called extents in a single zone. For example, the size of a DB in 2.18MB is about 2.18 MB (2,293,760 bytes) 2,293,760 b8kb280 pages 35 areas one page can only be viewed by one object all the records in the data table belong to which page SELECTtop10 % physloc %, sys. f

Each page on a page is 8 KB, and eight consecutive pages are called extents in a single zone. For example, the size of a DB in 2.18MB is about 2.18 MB (2,293,760 bytes) = 2,293,760 B/8kb = 280 pages = 35 zones one page can only be owned by one object. view which file the records in the data table belong to and which page the SELECT top 10% % physloc %, sys. f

Page

Each page is 8 KB, and eight consecutive pages are called extents in one zone,

For example, a DB of 2.18MB has a zone.

2.18 MB (2,293,760 bytes) = 2,293,760 B/8 KB = 280 pages = 35 areas

A page can only be owned by one object

View the file and page of the record in the data table

SELECT top 10
% Physloc %,
Sys. fn_physlocFormatter (% physloc %) AS RID
FROM tableName

-- Note: In a 64-bit system, the format compiled by sys. fn_physlocFormatter is sometimes incorrect. You need to manually calculate the format based on physloc. The calculation method is as follows:

In bytes, such

0x0702000001002200 is displayed after Flashback
Zero x 0022000100000207

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

DBCC TraceOn (3604)
DBCC page (Database Name, 1,40995, 0)
DBCC TraceOff (3604)

Unified Zone

The eight pages in the partition are all

Hybrid Zone

The eight pages in the partition can be shared by a maximum of eight objects. New tables or indexes are allocated to pages in a hybrid partition. When this table or index grows to eight pages (including the table data and its indexes and other data sizes ?), All future allocations will use the same type of zone.

GAM page

Global Allocation Map ing (Global Allocation Map), including the page header and some other overhead, there are 8 000 bytes or 64 000bit available, each bit represents a zone (8 pages ),0 indicates used, 1 indicates Free Zone.

64000 bits represent 64000*8 page pages in 64000 zones, that is, 2 ^ 6*1000*2 ^ 3 * 8KB = 2 ^ 12*1000 * 1000B, which is about 4 GB space. That isEach 4 GB space of a file corresponds to a GAM page.

SGAM page

Share a global partition. Similar to GAM, a bit represents a partition. The difference is that its 1 represents a hybrid partition and has available space. 1 represents unused or no available space.

Relations between GAM and SGAM:

Current usage	GAM bit settings	SGAM bit settings
All unused	1	0
Hybrid zone or unified type zone (unified zone) in full use)	0	0
Hybrid areas not used on pages	0	1

Example:

How do I find a hybrid zone with available space?

Directly find the zone where SGAM corresponds to bit 1, and the corresponding GAMbit in this zone is 0.

Tip:

If no unified zone is available, SqlServer will first find the GAM page, allocate a zone, and use one of the pages as a hybrid zone.

First 8 pages

The next GAM page appears on every 511 230 pages after the first GAM page (page 2) (approximately 4 GB space ), the next SGAM page appears on every 511 230 pages after the first SGAM page (page 3.

The page where the page number of each database file is 0 is the file header page, andEach file has only one page (the file header page with the page number 0).

Page 0 is the header Page, and Page 1 is the Free Space Page (PFS ).

In SQL Server, the first eight data pages are fixed, so SQL server can quickly confirm which zones are available and which ones are unavailable.

For example:

Page 1	Page 1	Page 1	Page 1	Page 1	Page 1	Page 1	Page 1
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	Retained page	Retained page	DCM page	BCM page

Except for the BOOT page of the database, from page 9th to page 8th are the storage information of the internal system table of SQLServer2008, and then from page 173rd to page 174th are the unallocated pages. Because the first page starts from 0, the size of the first page is exactly the same as that of the database data file we see.

See the previous operation. database size: 2.18 MB (2,293,760 bytes) = 2,293,760 B/8 KB = 280 pages = 35 zones.

Page 1	Page 1	Page 1	~	Page 1	174th ~ Page 1
M_type = 1	M_type = 13	M_type in (1, 2, 10)			N/
Data Page	Boot page	Mainly information about internal system tables			Unallocated

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

DBCC CheckPrimaryfile ('d: \ MSSQL \ Data \ GPOSDB. mdf ', 2)

Alter database productdb set online -- set the online status
Select * From sys. dm_exec_connections -- view the link

Select * from sys. database_files -- view the file group

--- View the FileID and PageID of a specific data record