Database space management

Database Space Management Study Notes

The basic unit of data storage in SQL Server is page (Page). Disk I/O operations are performed at the page level.

The smallest unit for SQL Server to read or write data is the page in units of 8 KB.

The beginning of each page is a 96 B header, which is used to store system information about the page. Includes the page number, page type, available space on the page, and the allocation unit ID of the object that owns the page. Different types of data are stored in different types of pages.

On a normal data page, data rows are placed sequentially immediately following the header of the page. At the end of the page is a row offset table that contains one entry for each row in the page.

Each entry records the distance between the first byte of the corresponding line and the top of the page. The entries in the row offset table are in the reverse order of the rows in the page.

------------------Insert Song Dashen’s note here----------------------- -

Except for the space occupied by the page header and the space occupied by the row offset matrix, the remaining space in the middle is used for data rows. There is also other information in a data row used to represent the row of data. The specific structure is as follows: status bit A 1 byte status bit B 1 byte fixed-length data type length 2-byte fixed-length data content specific Number of fixed-length data byte columns 2 bytes NULL bitmap column number/8 bytes Number of variable-length columns Offset matrix of 2-byte variable-length columns Number of variable-length columns * 2 bytes Variable-length columns Data specific variable length data bytes occupy at least 1+1+2+2+1=7

--------------------------- ----------------------------------------

To output the specified page, you can use: DBCC PAGE(, , , )

Db_id can be obtained from the results of sp_helpdb.

File_id can be obtained from the result of sp_helpfile.

Format_id is the output format you specified.

Before running DBCC PAGE, you also need to turn on the trace flag 3604

In SQL Server, rows cannot span across pages, and the data of all fields belonging to the same row must be placed on the same page. The maximum data size of a page is 8 060 B (8 KB).

So the maximum total length of a row composed of general data type fields cannot exceed 8 KB.

However, this restriction does not include data in Text/Image data type fields.

The data of these types of fields will be stored separately in the LOB (LargeObject) page.

Starting from SQL Server 2005, data types such as varchar(max), nvarchar(max), and varbinary(max) are provided.

If the total length of rows containing fields of this type of data does not exceed 8 KB, the data will still be stored together in the ordinary data page.

If the total length exceeds 8 KB, SQL Server will separate the data of these fields and store them separately in a page called Row-Overflow.

Extent is a collection of 8 physically contiguous pages, used to manage pages effectively. All pages are stored in extents.

• Unified area, owned by a single object. All 8 pages in the zone can only be used by one object.

• Mixed area, shared by up to 8 objects. Each of the 8 pages in the zone can be owned by a different object. But a page can always belong to only one object.

Pages are usually allocated to a new table or index from the mixed extent. When the table or index grows to 8 pages, it changes to using the uniform extent for subsequent allocations.

DBCC SHOWFILESTATS

This command can directly read the area allocation information from system allocation pages such as GAM and SGAM, and directly calculate how many areas in the database file have been allocated. CSP_SPACEUSED (+ UpdateUSAGE is accurate)

Dbcc ShowContig Let's analyze the size of the database space.

Log files are not organized by page/section.

The database engine internally divides each physical log file into multiple virtual log units. The virtual log unit has no fixed size, and the number of virtual log units contained in a physical log file is not fixed.

Every time the log file automatically grows, at least one virtual log unit will be added.

If a log file has experienced multiple small automatic growths, the number of virtual log units in it will be much larger than that of a normal log file.

This situation will affect the efficiency of log file management, and even cause the database to start taking a long time.

A transaction log is a wrapper file. When a database is created, the logical log files start at the beginning of the physical log files.

New log records are added to the end of the logical log and then expanded towards the end of the physical log.

When the end of the logical log reaches the end of the physical log file, the new log record will wrap around to the beginning of the physical log file and continue writing backwards.

DBCC SQLPERF(LOGSPACE) can check the usage of log files.

The Tempdb system database is a global resource available to all users connected to an instance of SQL Server. In SQL Server2005, it is used to save the following:

User objects

• User-defined tables and indexes.

• System tables and indexes.

• Global temporary tables and indexes.

• Local temporary tables and indexes.

• table variables.

• The table returned in a table-valued function.

Internal Objects

• Worktable used for cursor or spool operations and temporary large object (LOB) storage.

• Work files for hash join or hash aggregation operations.

• Used for intermediate sorting results of operations such as creating or rebuilding an index (if SORT_IN_TEMPDB is specified),

or intermediate sorting results of certain GROUP BY, ORDER BY, or UNION queries.

Use at least 9 pages per internal object: one IAM page, one 8-page extent.

Version Store

• Row versions generated by data modifying transactions in a database using snapshot isolation level or read committed isolation level (based on row versioning).

• Row versions generated by data modification transactions for functions such as online index operations, multiple active result sets (MARS), and AFTER triggers.

A major feature of Tempdb space usage is that only some objects, such as temporary tables and table variables created by users, can be managed using management views such as sys.allocation_units and sys.partitions.

The space usage of tempdb cannot be tracked using sp_spaceused. (Inaccurate)

sys.dm_db_file_space_usage This view can reflect the space usage distribution of tempdb in several major categories.

Is it a user object (user_object_reserved_page_count), a system object (internal_object_reserved_page_count)

, or a version store (version_store_reserved_page_count).

The above is the content of database space management. For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!