SQL Server Data Page Structure

SQL Server Data Page Structure

In the forum, I often asked how sqlserver stores basic table data. Since I don't want to check msdn, I 'd like to talk about it here.

The basic table data is stored on the data page. The data stored by sqlserver is stored on the disk in units of "pages ".

Disk read/write data is read to the memory page by page.

This is called page split or "fragment "(For details, seeArticleI will explain it to you at the end, because you need to understand sqlservr first.

The data page structure can be clearly explainedSqlserver sometimes needs to read two pages to read the data you need.

If the table is indexed, you can use the re-indexing method to solve the problem. If no index is created, there is no way.

Because the index table is created, the data in the table is placed on the index page of the B-tree data structure, rather than the data page of the heap data structure.

 

It seems that there are too many questions. Now let's talk about how the data page stores the data:

 How is each row of data stored on the data page?

Structure composition: Each sqlserver data page is roughly divided into three parts: the page header, the offset at the end of the page, and the data storage

Assume that there is a table with the following structure:
Create Table Test (
A int,
B INT
)

It stores data on the page (1 is the database data file ID, that is, fileid, and 100 is the pageid page number ).

The page structure is roughly as follows:

 

In the header of the pagePage Properties, IncludingPage numberAnd records the starting position of the idle part of the current page.

In this way, when sqlserver inserts new data, it can quickly find the starting position, and the offset at the end of the page.

Records the starting position of each data row. In this way, SQL server will not be able to quickly find each record.

Confuse the previous record with the next record. On the legend page, there are two records: (1,100) and (2,200 ).

The starting position of the record is 96, and the starting position of the second record is 111. It starts from 126 and is free space.

 

When the data rows on the page change, sqlserver not only needs to modify the data itself, but also the offset value,

To ensure that sqlserver can continue to accurately manage each row on the data page

 

Do you understand? It's time to go to bed. It's hard to finish the work.

Supplement:

StartingFragment Page splittingIf there is an index, it is also called"Index fragmentation ",So how are these fragments caused and how can they be avoided and repaired?

The cause is simple: when you delete a record in the table, sqlserver will go to the page where the record is located.

Then delete the data. After you delete the data, the data stored on the data page becomes discontinuous.Data fragmentation"

That is, the data stored on the page is not continuous. When you delete multiple records and these records are on the same page, it will cause data congestion dissatisfaction.

The entire page, When you insert a record, this record will not be inserted to the place where you deleted the record last time, if sqlserver

The last data page (these data pages are linked by a two-way linked list) is inserted at a location, a new page is opened without a location, and then inserted

Record

 

If there are two main drawbacks of these fragments:

Drawback 1: Sqlserver when searching for records, you need to search for multiple pages to find the record (especially using the table scan execution plan). sqlserver is in a certain order.

In this way, you only need to read one page to the memory. sqlserver now needs to read two pages to the memory (especially sqlserver

When a table is used to scan the execution plan ),Memory usage increases, search time increases

 

Drawback 2:A page can be used to store continuous data. Now it takes two pages to store the data, resulting in a waste of disk space and additional usage.

Disk Space

 

How to avoid and fix:

It is best to create a clustered index on the table, and then re-sort the data by re-indexing or re-organizing the index in the order of the fields that created the index.

Storage, re-indexing will re-sort the data pages and"Full Data"Page again"Full ",And arranged in sequence (for more details, refer to msdn)

Let's take a look at how the data is stored in an orderly manner by clustered index.

Msdn:In addition to improving query performance, clustered indexes can also be regenerated or reorganized as needed to control table fragments.You can also create a clustered index for the view.

The clustered index sorts and stores the data rows based on the key values of data rows in the table. Each table can have only one clustered index, because data rows can only be stored in one order.

Only one order of storage means that when you create a clustered index or primary key, you may create a clustered index or a composite primary key on multiple columns.

Sqlserver will only sort the fields in the leftmost column when you create an index, but it is only one column. It does not mean that the first column is sorted first,

Sort by the second column, and then by the third column ......

There are many obfuscation !!!!!!!!!!!!!!!

 

If there is no clustered index on the table, this problem cannot be solved. Tables without clustered indexes are all heap data structures. That is to say, there is no data arrangement method.

Unless a clustered index is added to sort the data in an orderly manner, non-clustered indexes cannot be solved because non-clustered indexes only add indexes to the table, but the data still follows the "heap"

The data structure is arranged, because the SQL Server Page types include index pages, data pages, lob pages, and row overflow pages. For details, see

This one I wrote earlierArticle: table store Organizational Structure

Http://www.cnblogs.com/lyhabc/archive/2012/09/20/2695818.html

 

Msdn articles for reference:

Clustered index design guide

Http://msdn.microsoft.com/zh-cn/library/ms190639 (V = SQL .100). aspx

Create a clustered Index

Http://msdn.microsoft.com/zh-cn/library/ms186342.aspx

Create a non-clustered Index

Http://msdn.microsoft.com/zh-cn/library/ms179325 (V = SQL .100). aspx

Non-clustered index design guide

Http://msdn.microsoft.com/zh-cn/library/ms189280.aspx

 

Therefore, many books have mentioned that it makes sense to create a clustered index for a table with a large data volume.