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