SQLServer database file management

Source: Internet
Author: User
MySQL database file management du Fei was destined to deal with databases in 2014. The tasks received in succession were related to database management, the database optimization of a civil aviation system, the high availability of databases of a real estate enterprise, and some operations.

MySQL database file management du Fei was destined to deal with databases in 2014. The tasks received in succession were related to database management, the database optimization of a civil aviation system, the high availability of databases of a real estate enterprise, and some operations.

SQL Server database file management

Du Fei

In 2014, we were destined to deal with databases. The tasks we received in succession were related to database management, database optimization of a civil aviation system, high Database Availability of a real estate enterprise, database read/write separation of a mobile phone chain enterprise, and database migration of a quick connection enterprise all of a sudden what's wrong? As a result, I want to write a few articles on database management. Today is the first article.

Concerning the management of database files, I often say that they are often mixed up in the rivers and lakes. If it takes a long time, there are basically some database file management problems, such:

1. SQL Server data file space is full

2. Log Files soar

3. the file cannot be shrunk.

4. How to automatically increase and contract files

This article focuses on these issues. Of course, to be proficient in database space management, you need to first understand the relevant theoretical knowledge. First, let's take a look at the space management of data files.

Data File space management

Each SQL Server database has at least two operating system files: one data file and one log file. A data file contains data and objects, such as tables, indexes, stored procedures, and views. The log file contains the information required to restore all transactions in the database. To facilitate allocation and management, you can set up data files and put them in a file group. The basic unit of data storage in SQL Server is page. The size of each page is 8 KB. The minimum unit for SQL Server to read or write data is also page, so 1 MB has 128 pages. Rows cannot be cross-page (the maximum number of lines on a page is 8,060 bytes, 8 KB, 1024*8). Page data of the Text/Image type is not included. For columns of variable length type, if the number of rows exceeds 8060, one or more variable-length columns are moved from the column with the maximum length to the page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer is maintained on the original page, if the total size of a row is smaller than 8060, it will be moved back. processing time will be extended when performing queries and other operations, because these records will be processed synchronously instead of asynchronously.

As shown in:

But there is another concept. We need to know that zone refers to a set of eight physically consecutive pages. If these eight physically consecutive pages belong to the same table, this partition is called a unified partition. If the eight pages belong to at least two different tables. This zone is called a hybrid zone.

Although each page has 8 KB, it does not mean that the 8 KB is used to store specific data. Each page starts with a 96-byte page header to store system information about the page. For example: page number, page type, available space of the page, and the Object ID that owns the page (that is, the object on which the page is used ). Different types of data are stored on different types of pages. As shown in, various page types in the data file and their contents are displayed:

In this case, many pages are used for Data and Text/Image types, while many management pages such as GM, SGAM, and PFS are distributed at the beginning of a Data file. Through these pages, SQL Server knows which pages in the data file are used and which pages are not used.

When a table or index requires more space, SQL Server needs to find the space that can be used for allocation. If the table or index is still less than eight pages, SQL Server must find the space that can be used to allocate a hybrid zone. If a table or index has eight or more pages, SQL Server must find a free, unified partition. Then, SQL Server needs to know which areas have been allocated and which areas can be used. This requires the global allocation ing page and the shared global allocation ing page, referred to as: GAM/SGAM.

GAM records the zones that have been allocated and used for what purpose. A gam page has a data bit for each zone in its covered space. If the value is 1, it is the Free Zone and can be used for allocation. If the value is 0, the zone is used. GAM can identify about 64000 zones, that is, 4G space. If the size exceeds 4 GB, a gam page is enabled to identify the next 4 GB space.

SGAM records the areas that are currently mixed and have at least one unused page. It can also identify 64000 zones, with approximately 4 GB space. If the bit is 1, it uses a hybrid zone and at least one page can be allocated. If the value is 0, it indicates a uniform zone or a hybrid zone, but there is no idle page.

SQL Server can easily find the desired page. If you need a new zone that is not used at all, you can use any zone with the corresponding bit value of 1 on the GAM page. If you need to find a zone with available space, such as a hybrid zone with one or more free pages, then it can look for a zone with the corresponding GAM value 0 and SGAM value 1. If there is no zone of mixed type with available space, SQL Server will use the GAM page to find a new zone and allocate it to a zone of mixed type, and then use a page in the zone. If there is no free zone, the file is full.

SQL Server can quickly lock the GAM page in a file because it is always located on the third page of any database file (page 2 ). The SGAM page is on the fourth page (3 ). The next GAM page appears on every 511 230 pages after the first GAM page (page 2), and the next SGAM page appears on the first SGAM page (page 3) in the future, there will be 511 million pages. The page with 0 for each database file is the file header page, and each file has only one page. Page 0 is the header Page, and Page 1 is the Free Space Page (PFS ). And the first eight pages of each database remain unchanged.

Below, we can use the DBCC Page command to view the Page information of a database. The syntax of this command is:

Dbcc page (Database Name | Database ID, file number, page number, and output options)

Output options: 0: default value, the title and page title of the output buffer; 1: The title and page title of the output buffer (each row is output respectively), and the row Offset Table; 2: the title of the output buffer, the page title (the overall output page), and the row Offset Table. 3: The column value is displayed when the title is output.

However, dbcc traceon (3604) must be enabled before this command is required ).

A complete PAGE contains four parts: BUFFER, page header, DATA, and offset table, indicating the cache, page header information, DATA, and offset table.

BUFFER: the cache part used to identify the position of the page in the memory.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.