SQLSERVER_ Storage Structure

The contents of this session are: 1. Summary of the filegroup concept. 2. Summary of the concept of the district. 2.0 tables with no indexes still allocate index space experimental validation. Experimental validation of 2.1 SQL Server partitioning practices. 3. Summary of the concept of the data page. 3.1 Drill down to the page header information stored in the EMP table.

SQL Server storage structure. Such as:

 

The logical storage structure of SQL Server is divided into three parts. 1.file_group (file group): • Divided into primary filegroups and user filegroups. The primary filegroup primarily stores database system information. • User file groups primarily store user information. The data files in the primary filegroup can be divided into: Master data file (. mdf) and secondary data file (. ndf) · The Master data file (. mdf), in addition to storage system and user data, also stores property information for all secondary data files and redo log files. sqlserver--> master Data file (. mdf)--secondary data file (. ndf) 2.extent (Zone): • The zone is the SQL Server allocation storage unit. Consists of 8 contiguous fixed-size data pages. (64K) · There are two types of zones in SQL Server: Mixed extent (mixed area) and uniform extent (unified zone). SQL Server does not assign an empty table allocation area to the table when the data is added. • Mixed zones can be used by multiple tables and indexes, and the unified zone can only be used by one table or index. • SQL Server will still assign a page to the table, storing IAM (index Allocation MAP) structure. (Experimental validation) • When a table or index allocation area is assigned, 8 data pages are first allocated on the blend area, and the subsequent allocation of the extents is allocated on the unified area. The first division of the mixed zone, followed by the practice of the unified area is to avoid one-time allocation of a zone to a small table. When a table has been assigned 8 data pages and requires more storage space, it will be renewed on the unified zone. 2.0 tables with no indexes still allocated index space experimental validation。one. Create a test table. 1> CREATE TABLE Test (a int,b varchar (ten)) 2> gotwo. Use the system stored procedure sp_spaceused to count the space usage of the test table. No allocation area was found. 1> exec sp_spaceused ' test ', true2> goname rows reserved data in                      Dex_size------------------------------------------------------------------------------------test 0 0 KB 0 KB 0 KBthree. Add data. 1> INSERT INTO test values (1, ' extent ') 2> Go (1 rows affected)Four. Review the statistics for the test table again. 1> exec sp_spaceused ' test ', true2> goname rows reserved data in           Dex_size-----------------------------------------------------------------------------------Test 1 8 KB 8 KB--you can see that there is no index and a page is assigned to the table. Experimental validation of the 2.1SqlServer partitioning procedure.  one. Testing in Test library. 1> use test2> Go has changed the database context to ' test '.two. Create a test table. 1> CREATE TABLE test1 (a int,b varchar (ten)) 2> go three. Add data to the test table after 8K. 14> DBCC EXTENTINFO (TEST,TEST1) 15> gofile_id     page_id     Pg_alloc    ext_size &nbs P  object_id   index_id    partition_number----------------------------------------------------- -----------------------------          1          90     &NB Sp   1           1  2105058535           0       nbsp          1          1          94           1           1  2105058535           0                1          1         109 &nbs P         1           1  2105058535           0 & nbsp              1          1         &NB   Sp       1           1  2105058535           0   & nbsp            1          1         &NBS   P       1           1  2105058535           0   & nbsp            1          1         &NBS   P       1           1  2105058535           0   & nbsp            1          1         118   &NBS P       1           1  2105058535           0 &nbsp              1          1         119 &nbs P         1           1  2105058535           0 & nbsp              1          1         184 &nbs P         8           8  2105058535           0 & nbsp              1          1         192 &nbs P         6           8  2105058535           0 & nbsp              1  --can be seen, ext_size (the size of the area, expressed as a page.) After the eighth data page (64k) is allocated, the size of the continuation assignment is 8 data pages. 3.data_page (Data page):date_page size fixed to 8K, cannot be modified. • Each data page has a 96-byte header that stores the page number, the page type, the available space for the page, and the object ID that owns the page (that is, which object is used for this page). 3.1 Experiment View the information for the page header in the pages stored by the EMP table. The first step: first get the EMP table stored file number + page number. (Page_id=file_id:page_num) used to order .:1> Select First,root,indid from sysindexes WHERE id=object_id (' emp ') and indid in (0,1) 2> gofirstrootindid0x590000 0001000X5900000001001 (1 rows affected) The above command option explains:--first,--The first page id--root,--The last page id--indid–-(0 means no clustered index, 1 means a clustered index)--(actually, the B-Tree index) Continue-- just look at the Frist column. The frist value is returned as follows: 0X590000000100 is 16 binary. Now artificially converted to file_id and Page_num. First step: Remove the 0x identifier and divide the values into one byte;59 00 00 00 01 00 Step Two: reverse these bytes. 00 01 | 00 00 00 59 The first two bytes represent the file ID, and the last four bytes represent the page number.--16 rpm and the resulting EMP table is stored in the file_id=1,page_num=89The second step uses the DBCC command to display the page header information. Performs the interpretation of the DBCC page (yzh,1,89,0) DBCC COMMAND---DBCC page ({' dbname ' | dbid}, FileNum, Pagenum [, printopt={0|1|2|3}]) the Prin Topt parameter has the following meanings: 0-print just the page header 1-page header plus per-row hex dum PS and a dump of the page slot array 2-page header plus whole page hex dump 3-page header plus detailed PE R-row Interpretation DBName | Dbid: Represents database name | Database idfilenum: File ordinal. Pagenum: page number. such as: DBCC page (yzh,1,89,0)--Query Display database Yzh, file 1th, page 89th, is simply the page header information. Execute DBCC PAGE (yzh,1,89,0) in the SSMs interface to get the following content. ------------------------------------------------------------------------------------------PAGE: (1:89) BUFFER:--The page information in the cache. BUF @0x0000000082fa6e40 bpage = 0x0000000082126000           Bhash = 0x0000000000000000 &nbs P         Bpageno = (1:89) bdbid = 5                   &NBSP ;        breferences = 0                      BCP uticks = 0bsampleCount = 0                     BUSE1 = 22258   &nbs P                    bstat = 0xc00009blog = 0x32159       & nbsp               bnext = 0x0000000000000000               page header: page @0x0000000082126000 m_pageid = (1:89)             & nbsp      m_headerversion = 1                  m_type = 1 &nbs P &nBsp;   m_typeflagbits = 0x4                 M_level = 0   &nbs P                      m_flagbits = 0x200            m_objid (allocunitid.idobj) =     M_INDEXID (allocunitid.idind) = in-Between     & Nbsp; metadata:allocunitid = 72057594039959552                                 metadata:partitionid = 72057594038910976         &NBS P                       METADATA:INDEXID = 1metadata:objectid = 557 5058         M_prevpage = (0:0)                   M_NEXTP Age = (0:0)      pminlen =                     &NB Sp   M_SLOTCNt =                       m_freecnt = 7328m_freedata = 881 &nbsp ;                   m_reservedcnt = 0                    M_LSN = (20:439:16) m_xactreserved = 0                   M_XDESID = (0:601)                   M_GHOSTRECCNT = 0m_tornbits =-33 8526208                Page Header header information, 96 bytesm_pageid-Current page numberm_headerversion-version number, always 1m_type-page type, 1 for data typem_typeflagbits-Data page index page is 4, the other page is 0.M_level = The level of the 0-b tree index. m_flagbits-page IDM_objid (allocunitid.idobj)-the corresponding object IDM_indexid (allocunitid.idind) = 256-index id,0 represents a heap, 1 represents a clustered index, and 2-250 means that a nonclustered index greater than 250 is the text or image fieldMetadata:objectid = 5575058-The id,-of the object to which the page belongs can be verified by sysobjects query. m_prevpage = (0:0)-Previous page of the data pagem_nextpage = (0:0)-The next page of the data pagePminlen = 32-The minimum value of a row of datam_slotcnt = 14-Number of rows of data in the pagem_freecnt = 7328-The remaining space in the page, with 7328 space left. - (note 1)m_freedata = 881-free space offset, used space capacity. m_lsn = (20:439:16) log record number. Allocation Statusgam (1:2) = Allocated SGAM (1:3) = Not allocated PFS (1:1) = 0x60 Mixed_ext ALLO cated 0_pct_full DIFF (1:6) = CHANGEDML (1:7) = not min_logged DBCC execution is complete. If DBCC outputs an error message, contact your system administrator. --------------------------------------------------------------------------------------------------- Note 1: A page size is a 8192 clause, the EMP table record has 14 rows, there should not be so much free space left. So I use the following command to query the EMP table using how many page.1> select O.name,sum (Reserved) as reserved from sysindexes i,sysobjects o2> where i.id= O.id and O.name= ' EMP ' 3> GROUP by o.name4> goname reserved------------------------------------E MP 2 (1 rows affected) as expected, the EMP table uses two page, and I only queried one of them.

SQLSERVER_ Storage Structure