SQL Server performance Optimization (7) database file organization

First, basic unit "page"

SQL Server uses a 8KB page to store the data. Physical I/O operations are also performed at the page level. There are many types of pages, specific references (MSDN). We focus more on the structure of the data page, including three parts: the header (96bytes), the data area (data rows and free space), and the row offset array (slots, at least 7bytes):

Why the size of the data page is 8k, what are the pros and cons, there are two articles explained well (data page structure, the bad side). In conclusion,

A. Data area size = (8096-header 96bytes+ offset array 7byte) = 7 993bytes, this is the maximum amount of space a page can allocate to our data usage. Our data is placed in a row of this data inside the write.

B. If our line of data contains two int columns, then we can store 7,993% (4+4) = 999.125, and the last 7byte space cannot be deposited, which is a waste of 7bytes in this page. Such as

C. Fruit our row of data contains two columns ( a char (4000) and an int, a total of 4004byte), that each page can only store one row, wasting 3 989bytes, it is obvious that this is not a good database design.

D. Free_space_in_bytes can be used to view the page idle situation.

SELECT   db_name(database_id),SUM(free_space_in_bytes)/ 1024x768  as 'free_kb' fromsys.dm_os_buffer_descriptorsWHEREdatabase_id<> 32767GROUP  bydatabase_idORDER  by SUM(free_space_in_bytes)DESCGO

E. Tables that contain varchar, nvarchar, varbinary, or sql_variant columns are not constrained by this restriction. There are further explanations on MSDN for row overflow data over 8 KB.

Second, the upper "area" of the page

District is the basic unit of management space. A zone is eight physically contiguous pages (that is, ten KB). This means that there are 16 extents per megabyte in the SQL Server database.

For historical reasons, SQL serve has two types of zones: the same area, the mixed zone.

Mixed area: 8 pages in the zone, which can be different tables, indexes, etc.

Unified Zone: 8 pages in the zone, is the same table, index.

When you create a new table, a record is inserted in the blend area, and when the table or index of the mixed area that it occupies grows to 8 pages, it becomes a subsequent assignment using the uniform zone, each of which expands by 84KB (8 pages). The question is how do you manage both of these partitions?

1. Global Allocation Mapping Table (Gam:global Allocation map Pages)---for Unified zone management Services

This is a page, a total of 64,000 bits (8000bytes) of the mask bitmap (reference bitmap algorithm), with a bit to manage a byte management 4Gbyte. So every 4G of data files have a GAM page to manage (64000 * 64/1024/1024).

2. Shared Global Allocation Mapping table (sgam:shared global Allocation map Pages)---for mixed area management services

Principle with the Global Allocation mapping table.

II. structure of a table without indexes

is the organization of tables in SQL Server (where partition 1, partition 2 is for ease of administration, partitioning the table, and putting it into different hard disk data files. By default, the table has only one partition. ）。 Table on the hard disk storage form, there are heaps and B-tree two forms.

At the bottom of the graph, the three leaf nodes, data, LOB, and row overflow are the collections of data stored on the hard disk. As you can understand, SQL Server has a data page of 8k on the hard disk, with three types of pages: data, LOB, row overflow. For page structure, refer to page and extents: https://technet.microsoft.com/zh-cn/library/ms190969 (v=sql.105). aspx

Data (In_row_data): A data row or index row that contains all data except large object (LOB) data. The type of page is data or index.

LOB (lob_data): Some large object data, such as: text, ntext, image, XML, varchar (max), nvarchar (max), varbinary (max), and so on. The type of the page is text/image.

Row overflow (row_overflow_data): If some of the data is too large to exceed 1 data pages.

II. structure of the clustered index

To use a clustered index, you must understand the concept of a clustered index and how it works. Online to the aggregate index description of the data there are many, the most popular explanation is to take the dictionary example: if we use pinyin in the Xinhua dictionary to check a Chinese character "rope", is the first few pages from the dictionary of the letter "S" began, and then check the "suo", and then find the word "rope", according to its page, Turn to the page where the word is located.

Of course this requires that

1. All Chinese characters in the Xinhua dictionary are arranged in phonetic alphabet order.

2. The Xinhua dictionary has a phonetic lookup appendix in front of it.

Clustered index is also the reason,

1. There is a "phonetic" list of all the data, that is, the index.

2. All data is arranged sequentially on the hard disk according to this index, which is the B-tree.

B-Tree concept: http://www.baidu.com/link?url=5cmF2QROgNE6JUpvbu_ 9oqwyhdtgvn-1wscae98nb6ek70h6tc4s3s3skgfvalboiwheubljiai6moivawk5jq&wd=&eqid= Ae27fb0e0003091b00000004555ae6b3&ie=utf-8

Structure of the clustered index

To be continued, table organization and index structure: https://technet.microsoft.com/zh-cn/library/ms189051 (v=sql.105). aspx

Reference: Index Concept classification: https://technet.microsoft.com/zh-cn/library/ms175049 (v=sql.105). aspx

Clustered index structure: https://technet.microsoft.com/zh-cn/library/ms177443 (v=sql.105). aspx

SQL Server performance Optimization (7) database file organization