DBCC SHOWCONTIG (Transact-SQL)

適用於：SQL ServerAzure SQL 受控執行個體

顯示指定資料表或檢視之資料與索引的片段資訊。

適用於：SQL Server 2008 (10.0.x) 和更新版本

Transact-SQL 語法慣例

Syntax

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

引數

table_name | table_id | view_name | view_id

檢查片段資訊的資料表或檢視。 若未指定，就會檢查目前資料庫中的所有資料表和索引檢視表。 若要取得資料表或檢視識別碼，請使用 OBJECT_ID 函式。

index_name | index_id

檢查片段資訊的索引。 若未指定，陳述式會處理指定之資料表或檢視表的基本索引。 若要取得索引識別碼，請使用 sys.indexes 目錄檢視。

WITH

指定 DBCC 陳述式所傳回之資訊類型的選項。

FAST

指定是否執行索引和輸出最小資訊的快速掃描。 快速掃描不會讀取索引的分葉或資料層級頁面。

ALL_INDEXES

顯示指定資料表和檢視之所有索引的結果，即使指定了特定索引也是如此。

TABLERESULTS

將結果顯示成含其他資訊的資料列集。

ALL_LEVELS

維護這個項目的目的，只是為了與舊版相容。 即使指定了 ALL_LEVELS，也只會處理索引分葉層級或資料表資料層級。

NO_INFOMSGS

抑制所有嚴重性層級在 0 到 10 的參考用訊息。

結果集

下表描述結果集中的資訊。

指定 table_id 和 FAST 時，DBCC SHOWCONTIG 會傳回只含下列資料行的結果集：

• 掃描頁數
• 範圍切換
• 掃描密度 [Best Count:Actual Count]
• 範圍掃描片段
• 邏輯掃描片段

當指定 TABLERESULTS 時，DBCC SHOWCONTIG 會傳回下列資料行及上一資料表所描述的 9 個資料行。

當指定 WITH TABLERESULTS 和 FAST 時，結果集與指定 WITH TABLERESULTS 時相同；不過，下列資料行含有 Null 值：

備註

當指定 index_id 時，DBCC SHOWCONTIG 陳述式會周遊於指定索引其分葉層級的頁面鏈結。 如果只指定 table_id，或是 index_id 為 0，便會掃描指定資料表的資料頁面。 這個作業僅需要意圖共用 (IS) 資料表鎖定。 這個方式可以執行所有更新和插入，但需要獨佔 (X) 資料表鎖定者除外。 這可讓您在執行速度和充分並行傳回的統計資料數目之間進行取捨。 不過，如果此命令只用來量測片段，則我們建議您使用 WITH FAST 選項以獲得最佳效能。 快速掃描不會讀取索引的分葉或資料層級頁面。 WITH FAST 選項不會套用至堆積。

Restrictions

DBCC SHOWCONTIG 不會顯示 ntext、text 和 image 資料類型的資料。 這是因為儲存文字和影像資料的文字索引已不存在。

同時，DBCC SHOWCONTIG 不支援部分新功能。 例如：

• 如果指定的資料表或索引進行資料分割，則 DBCC SHOWCONTIG 只會顯示指定資料表或索引的第一個分割區。
• DBCC SHOWCONTIG 不會顯示資料列溢位儲存資訊及其他新的非資料列資料類型，例如 nvarchar(max)、varchar(max)、varbinary(max) 和 xml。
• DBCC SHOWCONTIG 不支援空間索引。

sys.dm_db_index_physical_stats (Transact-SQL) 動態管理檢視可完全支援所有新功能。

資料表片段

DBCC SHOWCONTIG 會判斷資料表是否嚴重片段化。 資料表的片段化是在資料表的資料修改 (INSERT、UPDATE 和 DELETE 陳述式) 過程中發生的。 因為這些修改通常不會平均散發在資料表的各個資料列上，所以各頁面的飽和度可能會隨著時間而不同。 對於掃描部分或完整資料表的查詢而言，這類資料表片段化可能會造成額外的頁面讀取。 這會防礙資料的平行掃描。

當索引片段化很嚴重時，您可以利用下列選項來減少片段化：

• 卸除和重建叢集索引。

  重建叢集索引會重新組織資料，造成飽和的資料頁面。 您可以在 CREATE INDEX 中使用 FILLFACTOR 選項來設定飽和度的層級。 這個方法的缺點是在卸除或重建周期內索引是離線的，作業不可部分完成。 如果中斷了索引建立，就不會重建索引。

• 依照邏輯順序來重新排序索引的分葉層級頁面。

  使用 ALTER INDEX...REORGANIZE 即可依照邏輯順序重新排序索引的分葉層級頁面。 由於這項作業是一個線上作業，因此，當執行陳述式時，可以使用索引。 這項作業可能在未失去已完成工作的情況下中斷。 這個方法的缺點是，該作業不如叢集索引卸除或重建作業的資料重新組織作業。

• 重建索引。

  搭配 REBUILD 使用 ALTER INDEX，以重新建立索引。 如需詳細資訊，請參閱 ALTER INDEX (Transact-SQL)。

每頁平均可用位元組和平均頁面密度 (全滿) 統計資料能指出索引頁面的飽和度。 每個頁面的平均可用位元組數應該很小，而平均頁面密度 (全滿) 數應該很大，如此索引才不會有許多隨機的插入。 指定 FILLFACTOR 選項來卸除和重建索引，可以改善統計資料。 另外，搭配 REORGANIZE 的 ALTER INDEX 也會壓縮索引，將它的 FILLFACTOR 考量在內，並可以改善統計資料。

您可以利用下列方式來判斷索引的片段化層級：

• 比較範圍切換和掃描範圍的值。

  範圍切換值應該盡可能接近掃描範圍值。 這個比例會計算成掃描密度值。 這個值應該盡可能高，您可以縮減索引的片段化來改進它。

  注意

  如果索引跨越許多檔案時，這個方法便無法運作。

• 了解邏輯掃描片段化和範圍掃描片段化值。

  邏輯掃描片段化和 (某種程度上的) 範圍掃描片段化值，是資料表片段化程度的最佳指標。 這兩個值都應該盡可能接近零，不過，百分比 0 至 10 的值可能比較合適。

  注意

  如果索引跨越多個檔案，範圍掃描片段化值會比較高。 若要縮減這些值，您必須減少索引片段化。

權限

使用者必須擁有資料表，或是系統管理員 (sysadmin) 固定伺服器角色、db_owner 固定資料庫角色，或 db_ddladmin 固定資料庫角色的成員。

範例

A. 顯示資料表的片段資訊

下列範例會顯示 Employee 資料表的片段資訊。

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

B. 使用 OBJECT_ID 和 sys.indexes 分別取得索引識別碼及資料表識別碼

下列範例會利用 OBJECT_ID 和 sys.indexes 目錄檢視來取得 AdventureWorks2022 資料庫中 Production.Product 資料表之 AK_Product_Name 索引的資料表識別碼和索引識別碼。

USE AdventureWorks2022;
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. 顯示資料表的縮寫結果集

下列範例會傳回 AdventureWorks2022 資料庫中 Product 資料表的縮寫結果集。

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

D. 顯示資料庫中每個資料表上各個索引的完整結果集

下列範例會傳回 AdventureWorks2022 資料庫中，每個資料表上各個索引的完整資料表結果集。

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

E. 使用 DBCC SHOWCONTIG 和 DBCC INDEXDEFRAG 重組資料庫中的索引

下列範例會顯示一種簡單的重組方法，將在宣告的臨界值上片段化的資料庫中重組所有索引。

/*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

另請參閱

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