DBCC SHOWCONTIG (Transact-SQL)DBCC SHOWCONTIG (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

顯示指定資料表或檢視之資料與索引的片段資訊。Displays fragmentation information for the data and indexes of the specified table or view.

重要

未來的 Microsoft SQL Server 版本將移除這項功能。This feature will be removed in a future version of Microsoft SQL Server. 請勿在新的開發工作中使用此功能,並且儘速修改使用此功能的應用程式。Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. 請改用 sys.dm_db_index_physical_statsUse sys.dm_db_index_physical_stats instead.

適用於SQL ServerSQL Server (SQL Server 2008SQL Server 2008目前版本)Applies to: SQL ServerSQL Server ( SQL Server 2008SQL Server 2008 through current version)

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

DBCC SHOWCONTIG   
[ (   
    { table_name | table_id | view_name | view_id }   
    [ , index_name | index_id ]   
) ]   
    [ WITH   
        {   
         [ , [ ALL_INDEXES ] ]   
         [ , [ TABLERESULTS ] ]   
         [ , [ FAST ] ]  
         [ , [ ALL_LEVELS ] ]   
         [ NO_INFOMSGS ]  
         }  
    ]  

引數Arguments

table_name | table_id | view_name | view_idtable_name | table_id | view_name | view_id
檢查片段資訊的資料表或檢視。Is the table or view to check for fragmentation information. 若未指定,就會檢查目前資料庫中的所有資料表和索引檢視表。If not specified, all tables and indexed views in the current database are checked. 若要取得資料表或檢視識別碼,請使用 OBJECT_ID 函式。To obtain the table or view ID, use the OBJECT_ID function.

index_name | index_idindex_name | index_id
檢查片段資訊的索引。Is the index to check for fragmentation information. 若未指定,陳述式會處理指定之資料表或檢視表的基本索引。If not specified, the statement processes the base index for the specified table or view. 若要取得索引識別碼,請使用 sys.indexes 目錄檢視。To obtain the index ID, use the sys.indexes catalog view.

取代所有提及的WITH
指定 DBCC 陳述式所傳回之資訊類型的選項。Specifies options for the type of information returned by the DBCC statement.

FASTFAST
指定是否執行索引和輸出最小資訊的快速掃描。Specifies whether to perform a fast scan of the index and output minimal information. 快速掃描不會讀取索引的分葉或資料層級頁面。A fast scan does not read the leaf or data level pages of the index.

ALL_INDEXESALL_INDEXES
顯示指定資料表和檢視之所有索引的結果,即使指定了特定索引也是如此。Displays results for all the indexes for the specified tables and views, even if a particular index is specified.

TABLERESULTSTABLERESULTS
將結果顯示成含其他資訊的資料列集。Displays results as a rowset, with additional information.

ALL_LEVELSALL_LEVELS
維護這個項目的目的,只是為了與舊版相容。Maintained for backward compatibility only. 即使指定了 ALL_LEVELS,也只會處理索引分葉層級或資料表資料層級。Even if ALL_LEVELS is specified, only the index leaf level or table data level is processed.

NO_INFOMSGSNO_INFOMSGS
抑制所有嚴重性層級在 0 到 10 的參考用訊息。Suppresses all informational messages that have severity levels from 0 through 10.

結果集Result Sets

下表描述結果集中的資訊。The following table describes the information in the result set.

統計資料Statistic DescriptionDescription
掃描頁數Pages Scanned 資料表或索引中的頁數。Number of pages in the table or index.
掃描範圍Extents Scanned 資料表或索引中的範圍數目。Number of extents in the table or index.
範圍切換Extent Switches 當 DBCC 陳述式往返資料表或索引頁面時,在各範圍之間的移動次數。Number of times the DBCC statement moved from one extent to another while the statement traversed the pages of the table or index.
平均範圍平均頁數Avg. Pages per Extent 在頁面鏈結中,每個範圍的頁數。Number of pages per extent in the page chain.
掃描密度 [最佳計數:實際計數]Scan Density [Best Count: Actual Count] 這是一個百分比。Is a percentage. 它是最佳次數實際次數的比例。It is the ratio Best Count to Actual Count. 如果每個項目都是連續的,這個值就是 100;如果這個值小於 100,就會有某些片段存在。This value is 100 if everything is contiguous; if this value is less than 100, some fragmentation exists.

最佳次數是每個項目都連續連結時,理想的範圍變更數目。Best Count is the ideal number of extent changes if everything is contiguously linked. 實際次數是實際的範圍變更數目。Actual Count is the actual number of extent changes.
邏輯掃描片段Logical Scan Fragmentation 掃描索引分葉頁時所傳回失序頁面的百分比。Percentage of out-of-order pages returned from scanning the leaf pages of an index. 這個數字與堆積無關。This number is not relevant to heaps. 失序頁面是指配置給索引之下一個實體頁面的頁面,而不是目前分葉頁中下一頁 指標所指向的頁面。An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
範圍掃描片段Extent Scan Fragmentation 掃描索引分葉頁時之失序範圍的百分比。Percentage of out-of-order extents in scanning the leaf pages of an index. 這個數字與堆積無關。This number is not relevant to heaps. 失序範圍是索引目前頁面所在之範圍,實際上不是索引上一頁所在範圍之下一範圍的範圍。An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

注意:當索引跨越許多檔案時,這個數目沒有意義。Note: This number is meaningless when the index spans multiple files.
平均平均可用位元組Avg. Bytes Free per Page 掃描頁面的平均可用位元組數。Average number of free bytes on the pages scanned. 數目愈大,頁面的飽和度愈低。The larger the number, the less full the pages are. 如果索引沒有許多隨機的插入,數目低會比較好。Lower numbers are better if the index will not have many random inserts. 這個數目也受到資料列大小的影響;資料列愈大,這個數目也愈大。This number is also affected by row size; a large row size can cause a larger number.
平均頁面密度 (全滿)Avg. Page density (full) 平均頁面密度,這是一個百分比。Average page density, as a percentage. 這個值將資料列大小考慮在內。This value takes into account row size. 因此,這個值是更精確的頁面飽和度指示。Therefore, the value is a more accurate indication of how full your pages are. 百分比愈大,愈好。The larger the percentage, the better.

指定 table_id 和 FAST 時,DBCC SHOWCONTIG 會傳回只含下列資料行的結果集。When table_id and FAST are specified, DBCC SHOWCONTIG returns a result set with only the following columns.

  • 掃描頁數Pages Scanned
  • 範圍切換Extent Switches
  • 掃描密度 [Best Count:Actual Count]Scan Density [Best Count:Actual Count]
  • 範圍掃描片段Extent Scan Fragmentation
  • 邏輯掃描片段Logical Scan Fragmentation

當指定 TABLERESULTS 時,DBCC SHOWCONTIG 會傳回下列資料行以及上一表格所描述的 9 個資料行。When TABLERESULTS is specified, DBCC SHOWCONTIG returns the following columns and also the nine columns described in the previous table.

統計資料Statistic DescriptionDescription
Object NameObject Name 所處理之資料表或檢視的名稱。Name of the table or view processed.
ObjectIdObjectId 物件名稱的識別碼。ID of the object name.
IndexNameIndexName 所處理之索引的名稱。Name of the index processed. 堆積的這個值是 NULL。Is NULL for a heap.
IndexIdIndexId 索引的識別碼。ID of the index. 堆積的這個值是 0。Is 0 for a heap.
LevelLevel 索引的層級。Level of the index. 層級 0 是索引的分葉層級或資料層級。Level 0 is the leaf, or data, level of the index.

堆積的層級是 0。Level is 0 for a heap.
頁面Pages 組成索引或整個堆積的層級之頁數。Number of pages that make up that level of the index or whole heap.
資料列Rows 索引層級的資料或索引記錄數目。Number of data or index records at that level of the index. 堆積的這個值是整個堆積中的資料記錄數目。For a heap, this value is the number of data records in the whole heap.

若是堆積,從此函數傳回的記錄數目可能與針對該堆積執行 SELECT COUNT(*) 時所傳回的資料列數目不符。For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. 這是因為一個資料列可能包含數筆記錄。This is because a row may contain multiple records. 例如,在某些更新情況下,單一的堆積資料列可能有一筆轉送記錄以及一筆當做更新作業結果的轉送記錄。For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. 同時,在 LOB_DATA 儲存體中,會將多數大型的 LOB 資料列分割為多筆記錄。Also, most large LOB rows are split into multiple records in LOB_DATA storage.
MinimumRecordSizeMinimumRecordSize 索引或整個堆積的層級之最小記錄大小。Minimum record size in that level of the index or whole heap.
MaximumRecordSizeMaximumRecordSize 索引或整個堆積的層級之最大記錄大小。Maximum record size in that level of the index or whole heap.
AverageRecordSizeAverageRecordSize 索引或整個堆積的層級之平均記錄大小。Average record size in that level of the index or whole heap.
ForwardedRecordsForwardedRecords 索引或整個堆積的層級之轉送記錄數目。Number of forwarded records in that level of the index or whole heap.
ExtentsExtents 索引或整個堆積的層級之範圍數目。Number of extents in that level of the index or whole heap.
ExtentSwitchesExtentSwitches 當 DBCC 陳述式往返資料表或索引頁面時,在各範圍之間的移動次數。Number of times the DBCC statement moved from one extent to another while the statement traversed the pages of the table or index.
AverageFreeBytesAverageFreeBytes 掃描頁面的平均可用位元組數。Average number of free bytes on the pages scanned. 數目愈大,頁面的飽和度愈低。The larger the number, the less full the pages are. 如果索引沒有許多隨機的插入,數目低會比較好。Lower numbers are better if the index will not have many random inserts. 這個數目也受到資料列大小的影響;資料列愈大,這個數目也愈大。This number is also affected by row size; a large row size can cause a larger number.
AveragePageDensityAveragePageDensity 平均頁面密度,這是一個百分比。Average page density, as a percentage. 這個值將資料列大小考慮在內。This value takes into account row size. 因此,這個值是更精確的頁面飽和度指示。Therefore, the value is a more accurate indication of how full your pages are. 百分比愈大,愈好。The larger the percentage, the better.
ScanDensityScanDensity 這是一個百分比。Is a percentage. 它是 BestCountActualCount 的比例。It is the ratio BestCount to ActualCount. 如果每個項目都是連續的,這個值就是 100;如果這個值小於 100,就會有某些片段存在。This value is 100 if everything is contiguous; if this value is less than 100, some fragmentation exists.
BestCountBestCount 這是每個項目都連續連結時,理想的範圍變更數目。Is the ideal number of extent changes if everything is contiguously linked.
ActualCountActualCount 這是實際的範圍變更數目。Is the actual number of extent changes.
LogicalFragmentationLogicalFragmentation 掃描索引分葉頁時所傳回失序頁面的百分比。Percentage of out-of-order pages returned from scanning the leaf pages of an index. 這個數字與堆積無關。This number is not relevant to heaps. 失序頁面是指配置給索引之下一個實體頁面的頁面,而不是目前分葉頁中下一頁 指標所指向的頁面。An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.
ExtentFragmentationExtentFragmentation 掃描索引分葉頁時之失序範圍的百分比。Percentage of out-of-order extents in scanning the leaf pages of an index. 這個數字與堆積無關。This number is not relevant to heaps. 失序範圍是索引目前頁面所在之範圍,實際上不是索引上一頁所在範圍之下一範圍的範圍。An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

注意:當索引跨越許多檔案時,這個數目沒有意義。Note: This number is meaningless when the index spans multiple files.

當指定 WITH TABLERESULTS 和 FAST 時,結果集與指定 WITH TABLERESULTS 時相同,不過,下列資料行含有 Null 值:When WITH TABLERESULTS and FAST are specified, the result set is the same as when WITH TABLERESULTS is specified, except the following columns will have null values:

資料列Rows ExtentsExtents
MinimumRecordSizeMinimumRecordSize AverageFreeBytesAverageFreeBytes
MaximumRecordSizeMaximumRecordSize AveragePageDensityAveragePageDensity
AverageRecordSizeAverageRecordSize ExtentFragmentationExtentFragmentation
ForwardedRecordsForwardedRecords

RemarksRemarks

當指定 index_id 時,DBCC SHOWCONTIG 陳述式會往返於指定索引之分葉層級的頁面鏈結。The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. 如果只指定 table_id,或是 index_id 為 0,便會掃描指定資料表的資料頁面。If only table_id is specified or if index_id is 0, the data pages of the specified table are scanned. 這個作業僅需要意圖共用 (IS) 資料表鎖定。The operation only requires an intent-shared (IS) table lock. 這個方式可以執行所有更新和插入,但需要獨佔 (X) 資料表鎖定者除外。This way all updates and inserts can be performed, except those that require an exclusive (X) table lock. 這可讓您在執行速度和充分並行傳回的統計資料數目之間進行取捨。This allows for a tradeoff between speed of execution and no reduction in concurrency against the number of statistics returned. 不過,如果這個命令只用來量測片段化,我們建議您使用 WITH FAST 選項以獲得最佳效能。However, if the command is being used only to gauge fragmentation, we recommend that you use the WITH FAST option for optimal performance. 快速掃描不會讀取索引的分葉或資料層級頁面。A fast scan does not read the leaf or data level pages of the index. WITH FAST 選項不會套用到堆積。The WITH FAST option does not apply to a heap.

限制Restrictions

DBCC SHOWCONTIG 不會顯示 ntexttextimage 資料類型的資料。DBCC SHOWCONTIG does not display data with ntext, text, and image data types. 這是因為儲存文字和影像資料的文字索引已不存在。This is because text indexes that store text and image data no longer exist.

另外,DBCC SHOWCONTIG 不支援某些新功能。Also, DBCC SHOWCONTIG does not support some new features. 例如:For example:

  • 如果指定的資料表或索引進行資料分割,DBCC SHOWCONTIG 只會顯示指定資料表或索引的第一個資料分割。If the specified table or index is partitioned, DBCC SHOWCONTIG only displays the first partition of the specified table or index.
  • DBCC SHOWCONTIG 不會顯示資料列溢位儲存資訊及其他新的非資料列資料類型,如 nvarchar(max)varchar(max)varbinary(max)xmlDBCC SHOWCONTIG does not display row-overflow storage information and other new off-row data types, such as nvarchar(max), varchar(max), varbinary(max), and xml.
  • DBCC SHOWCONTIG 不支援空間索引。Spatial indexes are not supported by DBCC SHOWCONTIG.

sys.dm_db_index_physical_stats (Transact-SQL) 動態管理檢視可完全支援所有新功能。All new features are fully supported by the sys.dm_db_index_physical_stats (Transact-SQL) dynamic management view.

資料表片段Table Fragmentation

DBCC SHOWCONTIG 會判斷資料表是否嚴重片段化。DBCC SHOWCONTIG determines whether the table is heavily fragmented. 資料表的片段化是在資料表的資料修改 (INSERT、UPDATE 和 DELETE 陳述式) 過程中發生的。Table fragmentation occurs through the process of data modifications (INSERT, UPDATE, and DELETE statements) made against the table. 由於這些修改通常不會平均散發在資料表的各個資料列上,因此,各頁面的飽和度可能會隨著時間而不同。Because these modifications are not ordinarily distributed equally among the rows of the table, the fullness of each page can vary over time. 對於掃描部分或完整資料表的查詢而言,這類資料表片段化可能會造成額外的頁面讀取。For queries that scan part or all of a table, such table fragmentation can cause additional page reads. 這會防礙資料的平行掃描。This hinders parallel scanning of data.

當索引片段化很嚴重時,您可以利用下列選項來減少片段化:When an index is heavily fragmented, the following choices are available for reducing fragmentation:

  • 卸除和重建叢集索引。Drop and re-create a clustered index.
    重建叢集索引會重新組織資料,造成飽和的資料頁面。Re-creating a clustered index reorganizes the data, and causes full data pages. 您可以在 CREATE INDEX 中使用 FILLFACTOR 選項來設定飽和度的層級。The level of fullness can be configured by using the FILLFACTOR option in CREATE INDEX. 這個方法的缺點是在卸除或重建周期內索引是離線的,作業不可部分完成。The drawbacks of this method are that the index is offline during the drop or re-create cycle, and that the operation is atomic. 如果中斷了索引建立,就不會重建索引。If the index creation is interrupted, the index is not re-created.
  • 依照邏輯順序來重新排序索引的分葉層級頁面。Reorder the leaf-level pages of the index in a logical order.
    請利用 ALTER INDEX…REORGANIZE,依照邏輯順序來重新排序索引的分葉層級頁面。Use ALTER INDEX...REORGANIZE to reorder the leaf-level pages of the index in a logical order. 由於這項作業是一個線上作業,因此,當執行陳述式時,可以使用索引。Because this operation is an online operation, the index is available when the statement is running. 這項作業可能在未失去已完成工作的情況下中斷。The operation is also interruptible without loss of completed work. 這個方法的缺點是它的資料重新組織作業不如叢集索引卸除或重建作業的資料重新組織作業。The drawback of this method is that the method does not do as good a job of reorganizing the data as a clustered index drop or re-create operation.
  • 重建索引。Rebuild the index.
    請利用 ALTER INDEX 和 REBUILD 重建索引。Use ALTER INDEX with REBUILD to rebuild the index. 如需詳細資訊,請參閱 ALTER INDEX (Transact-SQL)For more information, see ALTER INDEX (Transact-SQL).

每頁平均可用位元組平均頁面密度 (全滿) 統計資料能指出索引頁面的飽和度。The Avg. Bytes free per page and Avg. Page density (full) statistic in the result set indicate the fullness of index pages. 每頁平均可用位元組的數目應該很小,而平均頁面密度 (全滿) 的數目數應該很大,這樣索引才不會有許多隨機的插入。The Avg. Bytes free per page number should be low and the Avg. Page density (full) number should be high for an index that will not have many random inserts. 指定 FILLFACTOR 選項來卸除和重建索引,可以改進統計資料。Dropping and re-creating an index with the FILLFACTOR option specified can improve the statistics. 另外,設定 REORGANIZE 的 ALTER INDEX 也會壓縮索引,將它的 FILLFACTOR 考量在內,可以改進統計資料。Also, ALTER INDEX with REORGANIZE will compact an index, taking into account its FILLFACTOR, and will improve the statistics.

注意

有許多隨機插入且非常飽和的頁面之索引,頁面分割數會增加。An index that has many random inserts and very full pages will have an increased number of page splits. 這會造成更多的片段。This causes more fragmentation.

您可以利用下列方式來判斷索引的片段化層級:The fragmentation level of an index can be determined in the following ways:

  • 比較範圍切換掃描範圍的值。By comparing the values of Extent Switches and Extents Scanned.
    範圍切換值應該盡可能接近掃描範圍值。The value of Extent Switches should be as close as possible to that of Extents Scanned. 這個比例會計算成掃描密度值。This ratio is calculated as the Scan Density value. 這個值應該盡可能高,您可以縮減索引的片段化來改進它。This value should be as high as possible, and can be improved by reducing index fragmentation.

    注意

    如果索引跨越許多檔案時,這個方法便無法運作。This method does not work if the index spans multiple files.

  • 了解邏輯掃描片段化範圍掃描片段化值。By understanding Logical Scan Fragmentation and Extent Scan Fragmentation values.
    邏輯掃描片段化和 (某種程度上的) 範圍掃描片段化值,是資料表片段化程度的最佳指標。Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values are the best indicators of the fragmentation level of a table. 這兩個值都應該盡可能接近零,不過,百分比 0 至 10 的值可能比較合適。Both these values should be as close to zero as possible, although a value from 0 through 10 percent may be acceptable.

    注意

    如果索引跨越多個檔案,範圍掃描片段化值會比較高。The Extent Scan Fragmentation value will be high if the index spans multiple files. 若要縮減這些值,您必須減少索引片段化。To reduce these values, you must reduce the index fragmentation.

權限Permissions

使用者必須擁有資料表,或是系統管理員 (sysadmin) 固定伺服器角色、db_owner 固定資料庫角色,或 db_ddladmin 固定資料庫角色的成員。User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

範例Examples

A.A. 顯示資料表的片段資訊Displaying fragmentation information for a table

下列範例會顯示 Employee 資料表的片段資訊。The following example displays fragmentation information for the Employee table.

USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG ('HumanResources.Employee');  
GO  

B.B. 利用 OBJECT_ID 取得資料表識別碼,利用 sys.indexes 取得索引識別碼Using OBJECT_ID to obtain the table ID and sys.indexes to obtain the index ID

下列範例會利用 OBJECT_IDsys.indexes 目錄檢視來取得 AdventureWorks2012AdventureWorks2012 資料庫中 Production.Product 資料表之 AK_Product_Name 索引的資料表識別碼和索引識別碼。The following example uses OBJECT_ID and the sys.indexes catalog view to obtain the table ID and index ID for the AK_Product_Name index of the Production.Product table in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DECLARE @id int, @indid int  
SET @id = OBJECT_ID('Production.Product')  
SELECT @indid = index_id   
FROM sys.indexes  
WHERE object_id = @id   
   AND name = 'AK_Product_Name'  
DBCC SHOWCONTIG (@id, @indid);  
GO  

C.C. 顯示資料表的縮寫結果集Displaying an abbreviated result set for a table

下列範例會傳回 AdventureWorks2012AdventureWorks2012 資料庫中 Product 資料表的縮寫結果集。The following example returns an abbreviated result set for the Product table in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG ('Production.Product', 1) WITH FAST;  
GO  

D.D. 顯示資料庫中每個資料表上之每個索引的完整結果集Displaying the full result set for every index on every table in a database

下列範例會傳回 AdventureWorks2012AdventureWorks2012 資料庫中,每個資料表上各個索引的完整資料表結果集。The following example returns a full table result set for every index on every table in the AdventureWorks2012AdventureWorks2012 database.

USE AdventureWorks2012;  
GO  
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;  
GO  

E.E. 使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 來重組資料庫中的索引Using DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database

下列範例會顯示一種簡單的重組方法,將在宣告的臨界值上片段化的資料庫中重組所有索引。The following example shows a simple way to defragment all indexes in a database that is fragmented above a declared threshold.

/*Perform a 'USE <database name>' to select the database in which to run the script.*/  
-- Declare variables  
SET 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 = 0  
BEGIN  
-- 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 = 0  
BEGIN  
   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  

另請參閱See Also

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)
DBCC (Transact-SQL)DBCC (Transact-SQL)
DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL)
sys.dm_db_index_physical_stats (Transact-SQL)sys.dm_db_index_physical_stats (Transact-SQL)
OBJECT_ID (Transact-SQL)OBJECT_ID (Transact-SQL)
sys.indexes (Transact-SQL)sys.indexes (Transact-SQL)