2nd week page _sql The basic unit of data storage in Server

Original: 2nd week page _sql The basic unit of data storage in Server

Last week laid the groundwork for a discussion of how SQL Server executes a query. I also mentioned that the page is a 8kb cache. Today we're going to focus on the page and dig out more details from a performance tuning perspective.

Pages are the foundation of SQL Server, and everything in SQL Server is related to pages. When we want to improve query performance, we can reduce the read of pages required by SQL Server to specify queries. In the second month when we discuss the index, we find that the structure of the index is also made up of pages. When you don't know what the page is, you can't tune and troubleshoot SQL Server.

data page Structure

In SQL Server, the size of the page is always 8kb in size, with different types of pages: data pages, index pages, system pages, and so on. Today we learn more about the data pages where SQL Server stores our table data. A data page is always made up of three parts:

1. Header (page header)
2. Data area (Payload)
3. Line offset Array (row offsets array)

In SQL Server, the header is always the length of a byte (not constrained by the type of the page), which stores general information about pages such as the page Id,object ID. The data area is the most interesting part of the page, because our records exist there. SQL Server gives you 8192 bytes (8kb) of space, where 8096 bytes is given to the data area. So it's easy to calculate how many records can fill a page, just take 8096 divided by the length of the record (which contains at least 7 bytes of internal row overhead). When you take the results, you get the number of records you can store on a page.

In SQL Server, page operations must be read or written on a full page, so our goal is always to store as many records as possible on a single page. SQL Server cannot read part of the page from your store, or write a portion of the page to the store. I/O operations are always done at least at the page level.

Finally at the bottom of the page you will see something called the line offset array. The row offset array uses 2 bytes to store the offset of each record in the page position. The first record always starts with a 96 offset, just immediately following the page header. You can give an overview of the structure of the data page you just introduced.

deep parsing of data pages

Let's look at a simple table definition:

1 CREATE TABLECustomers2 (3FirstNameCHAR( -) not NULL,4LastNameCHAR( -) not NULL,5AddressCHAR( -) not NULL,6ZipCodeCHAR(5) not NULL,7RatingINT  not NULL,8ModifiedDateDATETIME  not NULL,9 )Ten GO

For such a table definition, it is easy to figure out how many records we can store on one page. The size recorded here is 224 bytes long (50+50+100+5+4+8+7). 8096/224 = 36.14, which means you can store up to 36 records on a page. The rest of the space-here is bytes (8096-224*36) is wasted, because the data page belongs to only one specified database object and cannot be shared with other objects. Worst case scenario, when your table defines a record length of 4031bytes, you are wasting 4029 bytes space on every page. When you define a field with a variable-length type like varchar, the situation changes because SQL Server allows the variable-length column to be stored on a different page.

If you want to know how much space is wasted on each page after your database design, you can query the following buffer pool by using the dynamic management view (DMV) below: sys.dm_os_buffer_descriptors Each record displayed from this dynamic management view (DMV) represents every page that is currently saved in the cache pool, and when you query this dynamic management view on a machine with large memory, this operation consumes memory. The Free_space_in_bytes column tells you how much space is free on the current page. The following query can tell you how much space is wasted in each database in SQL Server:

1 SELECT2db_name(database_id),3SUM(free_space_in_bytes)/ 1024x768  as 'free_kb'4  fromsys.dm_os_buffer_descriptors5 WHEREdatabase_id<> 327676 GROUP  bydatabase_id7 ORDER  by SUM(free_space_in_bytes)DESC8 GO

This is a query that I perform frequently in my system (for example, when doing a SQL serve health Check), in order to find out which database has a bad table design.

Summary

I hope this performance tuning will help you better understand the data pages in SQL serve and how important they are to performance tuning. As you can see, focusing on table design or not will directly affect how much data pages are used for a table.

If you want to know about the data page more For details, I also recommend watching SQL Server quickie on this topic.

Next week we'll explore more details of the SQL Serve area , which are also important to us.

2nd week page _sql The basic unit of data storage in Server