SQL Server Data organization structure

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.