Sort SQL Server database table index fragments through DBCC

Yesterday, I checked a very slow table, which was not maintained for more than two years, and the number of logical scan fragments was as high as 99.81%. So I began to track and record the table. Today, we checked the overview of all the tables with the DBCC showcontig with tableresults command, and then referred to the description of fields in msdn, and found that the problem was serious. Scandensity (this is the ratio of "best count" to "actual count. If all the content is continuous, the value is 100. If the value is smaller than 100, some fragments exist.) Some are even 16.6666666666667, and other scanning density and other indicators are particularly unsatisfactory.

• Check:

Scan results of a table:

Bytes --------------------------------------------------------------------------------------------------------------------------

DBCC showcontig is scanning the 'userinfo' table...
Table: 'userinfo' (1646628909); index ID: 1, Database ID: 7
A table-level scan has been performed.
-Number of scanned pages ......: 367
-Number of scan zones ....................: 48
-Number of Area switches ......: 355
-Average number of pages in each partition ......: 7.6
-Scan density [optimal count: actual count] ......: 12.92% [46: 356]
-Logically scan fragments...: 95.37%
-Area scan fragments ......: 47.92%
-Average number of available bytes per page ......: 2996.8
-Average page density (full) ..........: 62.98%
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator. Bytes --------------------------------------------------------------------------------------------------------------------------

Explanation ):

Number of scanned pages: If you know the approximate size of the rows and the number of rows in the table or index, You can estimate the number of pages in the index. Look at the number of scanned pages. If it is significantly higher than your estimated number of pages, it indicates there are internal fragments.

Number of scanned extended disk partitions: divide the number of scanned pages by 8, rounded to the next maximum value. This value should be consistent with the number of scan extended disk areas returned by DBCC showcontig. If the number returned by DBCC showcontig is high, external fragments exist. The severity of fragments depends on the value displayed just now, which is higher than the estimated value.

Number of switches in the extended Disk Area: this number should be equal to the number of scanned extended disks minus 1. If it is high, external fragments exist.

Average number of pages on each extended Disk Area: this is the number of scanned pages divided by the number of scanned extended disk areas, generally 8. If the value is smaller than 8, external fragments exist.

Scan density [Best Value: actual value]: DBCC showcontig returns the most useful percentage. This is the ratio of the Best Value of the extended disk to the actual value. The percentage should be close to 100% as much as possible. If it is low, external fragments exist.

Logical scan fragmentation: the percentage of unordered pages. The percentage should be between 0% and 10%. If the percentage is high, external fragments exist.

Extended Disk Area scan fragmentation: the percentage of unordered extended disk areas in scanning index leaf-level pages. The percentage should be 0%. If the percentage is high, external fragments exist.

Average number of available bytes per page: the average number of available bytes on the scanned page. The higher the description, the more internal fragments are. But before you use this number to determine whether there are internal fragments, consider the fill factor ).

Average page density (integrity): the opposite of the percentage of average available bytes on each page. A low percentage indicates that internal fragments exist.

• Sort database fragmentation Indexes

Seeing this is not right, decisively go to msdn to find relevant information, found the msdn to the database index to fragment the T-SQL, executed a bit, found that the effect is not bad.

Use DBCC showcontig and DBCC indexdefrag to fragment the indexes in the database: (the following example shows a simple method, sorts all indexes with the number of fragments in the database above the declared threshold .)

/*Perform a 'USE <database name>' to select the database in which to run the script.*/-- Declare variablesSET NOCOUNT ON;DECLARE @tablename varchar(255);DECLARE @execstr   varchar(400);DECLARE @objectid  int;DECLARE @indexid   int;DECLARE @frag      decimal;DECLARE @maxfrag   decimal;-- Decide on the maximum fragmentation to allow for.SELECT @maxfrag = 30.0;-- Declare a cursor.DECLARE tables CURSOR FOR   SELECT TABLE_SCHEMA + '.' + TABLE_NAME   FROM INFORMATION_SCHEMA.TABLES   WHERE TABLE_TYPE = 'BASE TABLE';-- Create the table.CREATE TABLE #fraglist (   ObjectName char(255),   ObjectId int,   IndexName char(255),   IndexId int,   Lvl int,   CountPages int,   CountRows int,   MinRecSize int,   MaxRecSize int,   AvgRecSize int,   ForRecCount int,   Extents int,   ExtentSwitches int,   AvgFreeBytes int,   AvgPageDensity int,   ScanDensity decimal,   BestCount int,   ActualCount int,   LogicalFrag decimal,   ExtentFrag decimal);-- Open the cursor.OPEN tables;-- Loop through all the tables in the database.FETCH NEXT   FROM tables   INTO @tablename;WHILE @@FETCH_STATUS = 0BEGIN;-- Do the showcontig of all indexes of the table   INSERT INTO #fraglist    EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')       WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');   FETCH NEXT      FROM tables      INTO @tablename;END;-- Close and deallocate the cursor.CLOSE tables;DEALLOCATE tables;-- Declare the cursor for the list of indexes to be defragged.DECLARE indexes CURSOR FOR   SELECT ObjectName, ObjectId, IndexId, LogicalFrag   FROM #fraglist   WHERE LogicalFrag >= @maxfrag      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;-- Open the cursor.OPEN indexes;-- Loop through the indexes.FETCH NEXT   FROM indexes   INTO @tablename, @objectid, @indexid, @frag;WHILE @@FETCH_STATUS = 0BEGIN;   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',      ' + RTRIM(@indexid) + ') - fragmentation currently '       + RTRIM(CONVERT(varchar(15),@frag)) + '%';   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',       ' + RTRIM(@indexid) + ')';   EXEC (@execstr);   FETCH NEXT      FROM indexes      INTO @tablename, @objectid, @indexid, @frag;END;-- Close and deallocate the cursor.CLOSE indexes;DEALLOCATE indexes;-- Delete the temporary table.DROP TABLE #fraglist;GO

After execution, the system returns the index scan count, movement count, and delete count (pages scanned, pages moved, and pages removed ). The effect is quite obvious, and then compare the scan results:

Bytes -----------------------------------------------------------------------------------------------------------------

DBCC showcontig is scanning the 'userinfo' table...
Table: 'userinfo' (1646628909); index ID: 1, Database ID: 7
A table-level scan has been performed.
-Number of scanned pages ......: 237
-Number of scan zones...
-Number of Area switches ......
-Average number of pages in each partition ......: 7.6
-Scan density [optimal count: actual count] ......: 96.77% [30: 31]
-Logically scan fragments...: 2.95%
-Area scan fragments ......: 29.03%
-Average number of available bytes per page ......: 200.3
-Average page density (full) ..........: 97.52%
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.

Bytes ------------------------------------------------------------------------------------------------------------

The results are quite different, with fragments greatly reduced, the average number of available bytes per page greatly reduced, the scanning density increased, and the average page density reached the ideal near saturation value. It seems that some commands of DBCC and the online series of msdn are quite good. Although some fragmentation indicators have been reduced for the time being, as long as there are operations, fragments will certainly be generated. After a period of tracking, we can make subjective judgments on the overall situation.

Link: http://msdn.microsoft.com/zh-cn/library/ms188796.aspx

Http://msdn.microsoft.com/zh-cn/library/ms175008 (V = SQL .90). aspx