Translation Basic storage unit for SQL Passion Week 2:sql Server--Data page

Week Basic storage unit for 2:SQL server-Data page

　　Last week we talked about how SQL Server executes a query, incidentally referring to the concept of a data page, and this time we'll take a closer look at some of the data pages from a performance tuning perspective.

A data page is the most basic storage unit of SQL Server, and everything is related to a data page. When we want to improve the performance of the query, we must find ways to reduce the number of page reads of the query. The structure of more indexes is also involved when you talk about indexes later. If you don't know what a page is, you won't be able to find the cause of SQL Server failure.

Structure of the data page

　　There are different types of pages in SQL Server: Data pages, index pages, system pages, and so on, but each page has a size of 8KB. Let's talk about the data page used to store the table data, a data page with three sections

1. Header page Headers
2. Data loading Area Payload
3. Rows offset array row offset array

The length of the header is 96bytes (depending on the type of page), and the page header typically places information such as the page ID, Object ID, and so on; The most important part of the page is in the data loading area, and our data records are stored in this area.

The entire page size is 8kb or 8192bytes, and the page header is removed, and the total space we use to store the data is 8096bytes, which also contains at least a 7bytes line offset array. So it's easier to figure out how many rows are stored on a page, as long as the sum of 8096 divided by the column length of the table can be calculated.

SQL Server reads and writes based on a full page and cannot read and write only part of the page, so a data page can store as many rows as possible, which means that the same number of pages that the database needs to read and write can be reduced.

　　Keep in mind that SQL Server IO operations are page level

　　The last part of the data page is the row Offset Array.

　　

　　Data page Internal parsing

　　Let's look at a simple table structure, and here's the CREATE statement:

　　　

CREATE TABLECustomers (FirstNameCHAR( -) not NULL, LastNameCHAR( -) not NULL, AddressCHAR( -) not NULL, ZipCodeCHAR(5) not NULL, RatingINT  not NULL, ModifiedDateDATETIME  not NULL,)GO

Through the definition of a table we can easily calculate how many records are stored on a data page.

Daily Record Length: + + + + 4 + 8 + 9 + 7 = 224bytes,

8096/224 = 36.14 records can be stored on a page.

There are actually 36 records stored on a page that occupy 224 * p = 8064 bytes,

The remaining 32bytes is not storing anything, because a page can only belong to one database object (here only belongs to the Customers table)! What's the worst case scenario, imagine that every record of our table, if 4031bytes, is a waste of 4029bytes of space on every data page. Typically we can use varchar types to address this situation, and SQL Server allows columns to be stored on different pages.

If you want to know how much page space is wasted by a table structure, you can view it from the system view: sys.dm_os_buffer_descriptors .

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

Each record represents a page that is currently stored in buffer pool, and note that calling this view consumes a considerable amount of memory.

free_space_in_bytesColumn represents the remaining space of the current page, and through the above statement, we can query the current instance, each database wasted space ranking. This is to find out which databases can have a bad table design.

　　

　　

Translation Basic storage unit for SQL Passion Week 2:sql Server--Data page