Organizing SQL Server database table index fragmentation through DBCC

Yesterday examined a very inefficient table, two years without maintenance, logical scan fragments up to 99.Bayi%, the table is then started to focus on tracking and logging. Today, the DBCC SHOWCONTIG with tableresults command examines all of the tables, and then, referring to MSDN's description of the fields, finds the problem more serious. Scandensity (This is the ratio of "best count" to "actual count".) If all content is contiguous, the value is -; If the value is less than -, there are some fragments) some even in 16.6666666666667, other scanning density and other indicators are particularly unsatisfactory. Check: Randomly post scan results for a table:--------------------------------------------------------------------------------------------------------------- -----------DBCC Showcontig is scanning'UserInfo'table ... Table:'UserInfo'(1646628909); Index ID:1, Database ID:7a TABLE-level scan has been performed. -Number of pages scanned ....................:367-Number of scan zones ...................: --Zone switching times ....................:355-The average number of pages in each district ............:7.6-Scan density [best count: Actual Count] ....:12.92% [ $:356]    -Logical scan fragments ...........:95.37%-area scan fragments ..........:47.92%-The average number of bytes available per page ...........:2996.8-Avg. page density (full) ...........:62.98%DBCC execution is complete.    If DBCC outputs an error message, contact your system administrator. --------------------------------------------------------------------------------------------------------------- -----------The explanation is as follows (see explanation of Source Point, MSDN explanation too official, or simply explain it): Scan pages: If you know the approximate size of a row and the number of rows in a table or index, you can estimate how many pages are in the index.     Look at the number of pages scanned, if it is significantly higher than the number of pages you estimate, there is internal fragmentation. Scan extents: Divide the number of scanned pages by 8, rounding to the next highest value. The value should match the number of scan extents returned by DBCC SHOWCONTIG. If DBCC SHOWCONTIG returns a high number, it indicates that there is an external fragment.     The severity of the fragment depends on how much higher the value you just showed than the estimated value. Number of extents switches: This number should be equal to the number of scan extents minus 1.     High, the external fragments are indicated. Average pages per extents: This is the number of scanned pages divided by the number of extents scanned, typically 8.     Less than 8 indicates an external fragment. Scan density [Best value: Actual value]:DBCC Showcontig returns the most useful percentage. This is the ratio of the best and actual values of the extents. The percentage should be as close to 100% as possible.    Low, it means that there are external fragments. Logical scan Fragmentation: Percentage of unordered pages.     The percentage should be between 0% and 10%, and high will indicate an external fragment. Extents Scan Fragmentation: the percentage of the unordered extents that are in the scan index leaf level page.     The percentage should be 0%, and high will indicate an external fragment. Avg. bytes available on each page: the average number of bytes available on the page being scanned.     The higher is the internal fragment, but you should consider fill factor (fill factor) before you use this number to determine if there are internal fragments. Avg. page density (full): the inverse of the average percentage of bytes available on each page.    A low percentage indicates an internal fragment. Defragment the database fragment index see something so wrong, go to MSDN to find the relevant information, found MSDN there is a database index to defragment the T-SQL, executed a bit, found that the effect is good. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in the database: (The following example shows a simple way to defragment all indexes in the database that are above the declared threshold value.) )/*Perform A ' use <database name> ' to select the database in which to run the script.*/--Declare variablesset NOCOUNT on;declare @tablename varchar (255);D eclare @execstr varchar ( -);D Eclare @objectidint;D eclare @indexidint;D eclare @fragdecimal;D eclare @maxfragdecimal;--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 (ObjectNameChar(255), ObjectIdint, IndexNameChar(255), IndexIDint, LVLint, Countpagesint, CountRowsint, Minrecsizeint, Maxrecsizeint, Avgrecsizeint, Forreccountint, Extentsint, Extentswitchesint, Avgfreebytesint, Avgpagedensityint, Scandensitydecimal, Bestcountint, Actualcountint, Logicalfragdecimal, Extentfragdecimal);--Open the cursor. OPEN tables;--Loop through all the tablesinchThe database. FETCH NEXT from tables to @tablename; While @ @FETCH_STATUS=0BEGIN;--Do the showcontig of any indexes of the table INSERT into #fraglist EXEC ('DBCC Showcontig (" "+ @tablename +" ") With FAST, Tableresults, All_indexes, no_infomsgs');FETCH NEXT from tables to @tablename; END;--Close and deallocate the cursor. CLOSE tables;deallocate tables;--Declare the cursor forThe list of indexes to is 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 ( the), @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; When go executes, it returns the number of index scans, the number of moves, and the number of deletions (pages scanned, pages Moved, pages removed). The effect is still obvious, and then the scan results are compared:--------------------------------------------------------------------------------------------------------------- --DBCC Showcontig is scanning'UserInfo'table ... Table:'UserInfo'(1646628909); Index ID:1, Database ID:7a TABLE-level scan has been performed. -Number of pages scanned ....................:237-Number of scan zones ...................: to-Zone switching times ....................: --The average number of pages in each district ............:7.6-Scan density [best count: Actual Count] ....:96.77% [ -: to]    -Logical scan fragments ...........:2.95%-area scan fragments ..........:29.03%-The average number of bytes available per page ...........:200.3-Avg. page density (full) ...........:97.52%DBCC execution is complete. If DBCC outputs an error message, contact your system administrator------------------------------------------------------------------------------------------------------------The results are very contrasting and the fragmentation is greatly reduced, the average available bytes per page is significantly reduced, the scanning density increases, and the average page density reaches the desired near saturation value. It seems that some of the DBCC commands and MSDN Books Online are still pretty good. Although some debris indicators have been reduced temporarily, as long as there are certain operations will also have fragmentation, through a period of time tracking, to the overall situation of the subjective judgment. RELATED links: http://msdn.microsoft.com/zh-cn/library/ms188796.aspxhttp://msdn.microsoft.com/zh-cn/library/ms175008 (v=sql.90). aspx

Organizing SQL Server database table index fragmentation through DBCC