Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of how to achieve SQL Server tablespace fragmentation recycling, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe you will have something to gain after reading this article on how to achieve SQL Server tablespace fragmentation recycling. Let's take a look.

1 the generation of lock fragmentation 1.1 the cause of fragmentation

1. In B-tree index, table data is physically stored according to the sort of clustered index. If the discretization of clustered index is serious, serious fragmentation may occur.

2. With the DML operation of the business, it will be accompanied by the splitting of data pages, which will also lead to the problem of table space fragmentation.

3. Large tables clean up invalid historical data through delete, and delete produces fragmented space.

1.2 impact of fragmentation

The more serious the table space fragmentation, the easier it is to affect the query efficiency of the table. This is because when the table fragmentation is serious, the database scans more "invalid pages" according to the execution plan to scan the data pages that meet the requirements, resulting in more IO consumption for query operations.

1.3 location fragmentation

1. In SQL Server, you can view some statistics of tablespace fragmentation by DBCC SHOWCONTIG. The specific syntax is as follows:

-- View fragmentation information of all indexes in the database use ${database name} DBCC SHOWCONTIG WITH ALL_INDEXES-- view fragmentation information of all indexes of a specified table DBCC SHOWCONTIG (${table name}) WITH ALL_INDEXES-view fragmentation information of a specified table and specified index DBCC SHOWCONTIG (${table name}, ${index name})

2. View index fragmentation through sys.dm_db_index_physical_stats ()

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID (Numbdb1'), OBJECT_ID (Numbdb1.dbo.users'), NULL, NULL, 'LIMITED'); SELECT * FROM sys.dm_db_index_physical_stats (DB_ID (Numbdb1'), OBJECT_ID (Numbdb1.dbo.users'), NULL, NULL,' DETAILED')

Focus on:

Avg_fragment_size_in_pages: the higher the value of this parameter, the better the performance of range scanning.

Avg_fragmentation_in_percent: for heap tables, this parameter represents the percentage of zone fragments; for index, this parameter represents logical fragments; the larger the parameter, the more serious the fragmentation of the table, which needs to be recovered through Reorganize or Rebuild Indexes.

Avg_page_space_used_in_percent: this parameter indicates the filling degree of the data page, which is generally less than 100%, but the smaller the parameter is, the more serious the fragmentation of the data page is. If you want to have the problem of data page usage, you must perform an index rebuild operation.

Fragment_count: number of fragmented data pages

Page_count: number of scanned data pages

3. View database fragmentation space Top table information through statistical information.

SELECT db_name () as DbName, t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM (a.total_pages) * 8 AS TotalSpaceKB, CAST (ROUND (SUM (a.total_pages) * 8) / 1024.00), 2) AS NUMERIC (36,2) AS total space MB, SUM (a.used_pages) * 8 AS total space KB CAST (ROUND ((SUM (a.used_pages) * 8) / 1024.00), 2) AS NUMERIC (36,2) AS Total use Space MB, (SUM (a.total_pages)-SUM (a.used_pages)) * 8 AS fragmentation Space KB, CAST (ROUND ((SUM (a.total_pages)-SUM (a.used_pages) * 8) / 1024.00, 2) AS NUMERIC (36) 2) AS fragmentation space MBFROM sys.tables tINNER JOIN sys.indexes I ON t.OBJECT_ID = i.object_idINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_idINNER JOIN sys.allocation_units an ON p.partition_id = a.container_idLEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_idWHERE t.is_ms_shipped = 0 AND i.OBJECT_ID > 0GROUP BY t.Name Total space occupied by s.Name and p.RowsORDER BY MB desc2 fragmentation processing

Since table data is physically stored according to clustered index sorting, when table fragmentation is serious, fragmented space can be reclaimed by reorganizing the clustered index, and there are many ways to rebuild the index, mainly as follows:

2.1 delete and rebuild the clustered index

In fact, this way is to delete the clustered index of the seriously fragmented table through drop index, and then rebuild the clustered index through create index or alter table. The characteristics of this method are:

After deleting the clustered index, it will affect the SQL execution efficiency of the table about querying using the index.

Performing a deletion of a clustered index will also cause nonclustered index reconstruction associated with the table

During the reconstruction of the clustered index, the corresponding Sch-M lock will be acquired, blocking the normal read and write operation of the business, and the creation of the clustered index will also lead to the corresponding reconstruction of the nonclustered index.

This method reorganizes the entire table data so that maximum fragmentation space can be reclaimed.

2.2 DROP_EXISTING

Rebuilding the index using DROP_EXISTING is also a deletion and reconstruction of the clustered index, but this approach makes some optimizations based on method 1:

When deleting a clustered index, the key value of the primary key index is retained, which avoids the reconstruction of the non-clustered index when deleting or rebuilding the clustered index.

During DROP_EXISTING re-indexing, normal business read and write operations will still be blocked.

This method reorganizes the entire table data so that maximum fragmentation space can be reclaimed.

Basic syntax:

CREATE INDEX ${index_name} ON T (${index_col}) WITH (DROP_EXISTING = ON) 2.3 DBCC DBREINDEX

DBCC DBREINDEX also achieves fragmentation recycling through index deletion and reconstruction. Depending on the database version (Enterprise or non-Enterprise Edition) and the index type (nonclustered or aggregation), this operation can be performed online or offline.

In the enterprise data engine, index reconstruction for nonclustered indexes can be performed online

During the cue reconstruction period, although the normal business read and write operations are not blocked, the execution efficiency of the corresponding DML operations will decrease.

During the reconstruction period, blocking business reading and writing

For online index reconstruction, it can be paused or terminated. However, the application during the pause will affect the efficiency of the DML execution of the table. If the index reconstruction operation does not continue later, please directly terminate rather than pause.

This method reorganizes the entire table data so that maximum fragmentation space can be reclaimed.

Basic syntax:

-- rebuild the specified index USE ${db_name}; GO DBCC DBREINDEX ('${schema_name}. ${table_name}', ${index_name}, 80); GO-- rebuild all indexes of the specified table USE ${db_name}; GO DBCC DBREINDEX ('${schema_name}. ${table_name}',', 70); GO2.4 DBCC INDEXDEFRAG

The implementation logic of this approach is quite different from the above three. DBCC INDEXDEFRAG does not completely reorganize the b-tree structure of the entire table:

According to the logical order of the index keys, DBCC INDEXDEFRAG reclaims the fragmented space by compressing the rows in the index page and then deleting the unnecessary fragmented data pages and deleting the completely fragmented data pages.

During the execution of this method, it does not block business read and write operations.

The effect of recyclable fragmented space in this way may not be as good as that of the above three index reconstruction methods.

Basic syntax:

DBCC INDEXDEFRAG (${db_name},'${schema_name}. ${table_name}', ${index_name}); 3 Space recovery

It should be noted that in the SQL Server database, we fragment the tablespace data, or truncate clears the invalid historical data, the free space is only free, and when new data is written, we give priority to using these empty data pages instead of applying for a new data space extension from OS. So this part will not be released directly to OS, and we need to shrink the data files of the database if we want to reduce the disk space usage of the entire OS.

1. Check the usage of data file space

-check database file space usage SELECT a.name [file name], cast (a. [size] * 1.0tick 128 as decimal (12jue 1)) AS [file setting size (MB)], CAST (fileproperty (s.nametemeringhouse space) / (8x16.0) AS DECIMAL (12jue 1)) AS [file occupied space (MB)], CAST ((fileproperty (s.name)) 'SpaceUsed') / (8 ~ 16.0) / (s.size/ (8 ~ 16.0)) * 100.0 AS DECIMAL (12 THEN 1)) AS [percentage of space occupied], CASE WHEN A.growth = 0 THEN' file size is fixed Will not grow 'ELSE' file will automatically grow 'end [growth mode], CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN' increment is fixed size 'WHEN A.growth > 0 AND is_percent_growth = 1 THEN' increment will be expressed as an integer percentage 'ELSE' file size is fixed Will not grow 'END AS [incremental mode], CASE WHEN A.growth > 0 AND is_percent_growth = 0 THEN cast (cast (a.growth*1.0/128as decimal (12L0) AS VARCHAR) +' MB' WHEN A.growth > 0 AND is_percent_growth = 1 THEN cast (cast (a.growth AS decimal (12L0) AS VARCHAR) +% 'ELSE' file size is fixed Will not grow 'end AS [growth value (% or MB)], a.physical_name AS [directory where the file resides], a.type_desc AS [file type] FROM sys.database_files an INNER JOIN sys.sysfiles AS s ON a.[ file _ id] = s.fileid LEFT JOIN sys.dm_db_file_space_usage b ON a [file _ id] = b.[ file _ id] ORDER BY a. [type]

2. Shrink the data file

This is the end of USE [${db_name}] GODBCC SHRINKDATABASE (nasty ${db_name}') GO's article on "how to achieve SQL Server tablespace fragmentation recycling". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to achieve SQL Server tablespace fragmentation recycling". If you want to learn more knowledge, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.