SQL Server index maintenance
2012-03-08 00:30:09| Category: Default Categories | Tags:SQL Server Index | Report | Font size Subscription
Pages & Extents (pages and extents)
The most basic data storage unit for SQL Server 2000 is the 8K storage space of page,1. When allocating storage space, SQL Server 2000 does not allocate 1 page at a time, and the basic storage allocation unit is a contiguous space of 8 page, called extent.
For the page, extents, and index structures of SQL Server 2000, refer to: Msdn-pages and extents, msdn-table and index Architecture.
Page Split (Slice)
When SQL Server is insert/update, if the page you want to update is already full and cannot hold the new data, SQL Server splits half of the page's data, reassigning a page to store it, and then insert/ Update operation, the operation of splitting a full data page into two data pages is called page split.
Page split occurs both on the data page and the index page. In the insert operation, if the free space on the page is less than the size of the record to be inserted, the page split will be done, and if there is a widening field in the table, the length of the widening field becomes larger resulting in a free space on the original page. The page split will be performed.
Index fragmentation (indexed fragmentation)
There are two types of index fragmentation for SQL Server: external fragmentation and internal fragmentation.
External Fragmentation:
The Index page is called external fragmentation when the logical order is not contiguous. When index is established, the storage of the index page is logically contiguous. You may want to insert this new index between two indexes when you insert an operation. If there is enough space in the index page at the indexed insertion position, the new index value is inserted directly in the Indexes page, and if space is required for the index page to be full or insufficient for the new indexed value, SQL Server will page split, Removes the index page portion of the insertion position data to free up space to insert a new index, and the moved data is reassigned to a new page in another location. Thus, as the insert operation increases, the index page is logically less contiguous.
The index structure is logically contiguous when the sample index has just been established:
If you need to insert a new index with an index value of 2, the index structure after insertion is as follows:
After inserting, the index page structure becomes logically discontinuous.
External fragmentation does not have a significant impact on query performance when a particular record is returned through index, or when a recordset is returned without specifying a sort. When you need to return a recordset of the specified sort, additional processing of the logically non-contiguous index page is required during the sorting process, and for large data volumes, if the index page is many, the external fragmentation is very serious and requires expensive query costs. In addition, external fragmentation has an impact on cache efficiency.
External fragmentation uses two indicators to describe the continuity of the page and the degree of continuity of the extent.
Internal Fragmentation:
If the storage space does not reach the maximum storage capacity in the Index page, it is called internal fragmentation. Regardless of the effect of the fill factor factor, when index is established, the index structure is logically contiguous, and each index page is stored full and fully utilized. The delete operation causes the index page to be idle; in the External fragmentation example, page split at the insert operation also causes the index page to appear idle.
Severe internal fragmentation, causing the index page to occupy much larger storage space than is actually required. The index scan in the query increases the logical READS, I/O, and other operations, resulting in performance issues.
Internal fragmentation uses the page's average page's degree of inactivity/utilization as an indicator.
Fill factor:
If there is a setting or if SQL Server automatically maintains a fill factor value, each index page is not stored full when the index is created, and a portion of free space is reserved based on the fill factor value. In the external fragmentation example, if the first index page is not full, then when inserting a new index with an index value of 2, you do not need to split the index page to improve the insert operation.
Fill factor is used in tables that require frequent insert/update operations, avoiding a large number of page split occurrences. Obviously, the use of fill factor is similar to the internal fragmentation, but it has greatly improved the large number of insert operations and the structure of the various data page. For fill factor, it is not convenient to accurately evaluate what values are best, and Microsoft recommends that SQL Server be automatically maintained. Improper fill factor settings, as with internal fragmentation, affect SQL Server performance.
DBCC Showconfig
Used to display data, index fragmentation information.
DBCC Showcontig (Tbluseritem,pk_tbluseritem)
Displays fragmentation information for index pk_tbluseritem in table Tbluseritem.
DBCC Showcontig (Tbluseritem) with all_indexes
Displays fragmentation information for all indexes in the table Tbluseritem.
DBCC Showcontig with All_indexes
Displays fragmentation information for all indexes in the current database.
Examples of the results performed are as follows:
DBCC showcontig scanning ' tbluseritem ' table ...
Table: ' Tbluseritem ' (1077578877); Index id:1, Database Id:8
TABLE level scan performed.
-Pages scanned ..............: 56933........
-extents scanned .......: 7563.............
-Extent switches .......: 7565.............
-Avg. Pages per Extent ...............: 7.5
-Scan Density [best count:actual Count] ....: 94.07% [7,117:7,566]
-Logical Scan fragmentation.......: 0%.
-Extent Scan fragmentation........: 0.03%.
-AVG. Bytes free per Page ............: 114.3
-Avg. Page Density (Full) ............: 98.59%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Page scanned: The number of page actually scanned. You can approximate the actual data page count from the size of each row, the total number of rows, and internal fragmentation is more serious if the number of page scanned far exceeds the actual number of data page calculated.
Extents scanned: The ideal value is to round the page SCANNED/8 to the smallest integer. If the extents scanned is greater than the theoretical value, there is a certain degree of external fragmentation.
Extent switches: The ideal value is extents scanned minus 1, exceeding this value indicates the presence of external fragmentation.
Avg. Pages per Extent: Ideal value is 8, less than 8 exists external fragmentation.
Scan Density [Best count:actual Count]: This is the most meaningful value that DBCC SHOWCONTIG returns, for the desired extents number than the actual extents amount, the reaction external One of the important statistical information of fragmentation. The ideal value is 100%, not less than 60%.
Logical Scan Fragmentation: Another very meaningful value that indicates the non-contiguous extent of the page, one of the important statistics that reacts external fragmentation. should be between 0% and 10%, not higher than 15%.
Extent Scan Fragmentation: Indicates the degree of discontinuity of the extents, with an ideal value of 0%.
Avg. Bytes free per page: The average number of idle bytes on each page. A high value indicates that there is a internal fragmentation, but the fill factor factor needs to be excluded.
Avg. Page Density (Full): with Avg. The Bytes free per page is a percentage parameter of the opposite, and a lower value indicates the presence of internal fragmentation.
In addition, DBCC SHOWCONTIG has several optional parameters that can be used, specifically refer to online help.
Resolving fragmentation issues
1. Drop the original index and rebuild the indexes
In this process, the index is drop and rebuild, which will block all queries during this period, and using this method on all clustered index and non-clustered index may cause the non-clustered index to be rebuilt two times.
The advantage is that the index is completely rebuilt to achieve optimal conditions. This method should be used if both external and internal fragmentation are quite serious.
2. Using the DROP_EXISTING clause
Using the DROP_EXISTING clause, you can avoid non-clustered index being rebuilt two times.
3. DBCC Dbreindex
You can specify only the table name without specifying the index name, which automatically rebuilds all of the table's indexes, which is easier than writing multiple drop index and CREATE INDEX statements. This command also rebuilds the table's primary key and unique constraints, statistics, without additional manipulation of these constraints and statistics.
DBCC Dbreindex is able to take advantage of multi-CPU processing, which is relatively fast for table operations where the amount of data is large and fragmentation very serious.
This method completes the operation in a transaction where there is sufficient free space in the data file to satisfy the rebuild of all indexes and related objects, or the operation may fail or the rebuild is not very thorough, for example, logical fragmentation may still be greater than 0 after the rebuild. For tables with very large data volumes, more free space is required, which should be given special attention.
4. DBCC Indexdefrag
DBCC Indexdefrag operates in two steps, first compressing each index page, releasing the extra page, and then reorganizing the root nodes of the index page so that the logical order of the index page is consistent with the physical storage order. That is, the logical order is continuous in the direction of physical storage.
Of the 4 methods, the other three must be in the database offline situation, because all queries that use these indexes will be blocked during the operation. DBCC Indexdefrag can be executed in the case of database online, but not fully organized. Because, on the one hand, the lock's index page is ignored during execution, and it is not newly assigned to reorder the page, but is reorganized in the originally allocated page space. The goal of this command is to make the logical order of the index page consistent with the physical storage order, and DBCC Indexdefrag will not take action to eliminate this physical storage interval if there is a gap between the logically contiguous two page or extent physical storage. Therefore, if the index page occupies a very large space, accessing the index page space may increase the overhead of head positioning and movement, thereby increasing the cost of I/O operations to some extent.
Expert advice: 60%<scan density<75%, 10<logical fragmentation<15, use DBCC INDEXDEFRAG;SCAN density<60%, Logical When fragmentation>15, use DBCC DBREINDEX.
In addition, in table design, the following points can be considered:
1. For table with insert/update operation, select a suitable fill factor.
2. Design the widening field as a constant width field. For example, item number, order number, customer code, vendor code, and so on.
3. Create a delete tag instead of physically deleting the data.
For example, some large systems, an object may have dozens of fields. The usual practice is to accommodate all these fields with a table, but for system access efficiency, you can use two table to store them. The main table holds the key, most frequently accessed field properties, as far as possible, without using the variable width field, and storing additional, descriptive, less-accessible field properties from the table. This is a one-to-one association, but for most cases the query that only needs to access the properties of the primary table can greatly improve access performance.
Reference:randy dyess-sql Server Index fragmentation and its Resolution.
SQL Server index Maintenance
