共用方式為

DBCC SHOWCONTIG (Transact-SQL)

  • 發行項

更新： 2007 年 9 月 15 日

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

ms175008.note(zh-tw,SQL.90).gif重要事項：
未來的 Microsoft SQL Server 發行版本將不再提供此功能。請避免在新的開發工作中使用此功能，並計劃修改目前使用此功能的應用程式。 請改用 sys.dm_db_index_physical_stats。

主題連結圖示Transact-SQL 語法慣例

語法

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 的參考用訊息。

結果集

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

統計資料

描述

掃描頁數

資料表或索引中的頁數。

掃描範圍

資料表或索引中的範圍數目。

範圍切換

當 DBCC 陳述式往返資料表或索引頁面時，在各範圍之間的移動次數。

每個範圍的平均頁數

在頁面鏈結中，每個範圍的頁數。

掃描密度 [Best Count:Actual Count]

這是一個百分比。它是最佳次數與實際次數的比例。如果每個項目都是連續的，這個值就是 100；如果這個值小於 100，就會有某些片段存在。

最佳次數是每個項目都連續連結時，理想的範圍變更數目。實際次數是實際的範圍變更數目。

邏輯掃描片段

掃描索引分葉頁時所傳回失序頁面的百分比。這個數字與堆積無關。失序頁面是指配置給索引之下一個實體頁面的頁面，並不是目前分葉頁中下一頁**指標所指向的頁面。

範圍掃描片段

掃描索引分葉頁時之失序範圍的百分比。這個數字與堆積無關。失序範圍是索引目前頁面所在之範圍，實際上不是索引上一頁所在範圍之下一範圍的範圍。

ms175008.note(zh-tw,SQL.90).gif附註：

當索引跨越許多檔案時，這個數目沒有意義。

每個頁面的平均可用位元組數

掃描頁面的平均可用位元組數。數目愈大，頁面的飽和度愈低。如果索引沒有許多隨機的插入，數目低會比較好。這個數目也受到資料列大小的影響；資料列愈大，這個數目也愈大。

平均頁面密度 (全滿)

平均頁面密度，這是一個百分比。這個值將資料列大小考慮在內。因此，這個值是更精確的頁面飽和度指示。百分比愈大，愈好。

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

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

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

統計資料

描述

Object Name

所處理之資料表或檢視表的名稱。

ObjectId

物件名稱的識別碼。

IndexName

所處理之索引的名稱。堆積的這個值是 NULL。

IndexId

索引的識別碼。堆積的這個值是 0。

Level

索引的層級。層級 0 是索引的分葉層級或資料層級。

堆積的層級是 0。

Pages

組成索引或整個堆積的層級之頁數。

Rows

索引層級的資料或索引記錄數目。堆積的這個值是整個堆積中的資料記錄數目。

就堆積而言，這個函數傳回的記錄數目，可能與針對堆積執行 SELECT COUNT(*) 所傳回的資料列數目不符。這是因為一個資料列可能包含多筆記錄。例如，在某些更新情況下，單一堆積資料列可能會在更新作業之後產生要轉送的記錄和已轉送的記錄。此外，多數的大型 LOB 資料列還會在 LOB_DATA 儲存體內分割成多筆記錄。

MinimumRecordSize

索引或整個堆積的層級之最小記錄大小。

MaximumRecordSize

索引或整個堆積的層級之最大記錄大小。

AverageRecordSize

索引或整個堆積的層級之平均記錄大小。

ForwardedRecords

索引或整個堆積的層級之轉送記錄數目。

Extents

索引或整個堆積的層級之範圍數目。

ExtentSwitches

當 DBCC 陳述式往返資料表或索引頁面時，在各範圍之間的移動次數。

AverageFreeBytes

掃描頁面的平均可用位元組數。數目愈大，頁面的飽和度愈低。如果索引沒有許多隨機的插入，數目低會比較好。這個數目也受到資料列大小的影響；資料列愈大，這個數目也愈大。

AveragePageDensity

平均頁面密度，這是一個百分比。這個值將資料列大小考慮在內。因此，這個值是更精確的頁面飽和度指示。百分比愈大，愈好。

ScanDensity

這是一個百分比。它是 BestCount 與 ActualCount 的比例。如果每個項目都是連續的，這個值就是 100；如果這個值小於 100，就會有某些片段存在。

BestCount

這是每個項目都連續連結時，理想的範圍變更數目。

ActualCount

這是實際的範圍變更數目。

LogicalFragmentation

掃描索引分葉頁時所傳回失序頁面的百分比。這個數字與堆積無關。失序頁面是指配置給索引之下一個實體頁面的頁面，並不是目前分葉頁中下一頁**指標所指向的頁面。

ExtentFragmentation

掃描索引分葉頁時之失序範圍的百分比。這個數字與堆積無關。失序範圍是索引目前頁面所在之範圍，實際上不是索引上一頁所在範圍之下一範圍的範圍。

ms175008.note(zh-tw,SQL.90).gif附註：

當索引跨越許多檔案時，這個數目沒有意義。

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

Rows

Extents

MinimumRecordSize

AverageFreeBytes

MaximumRecordSize

AveragePageDensity

AverageRecordSize

ExtentFragmentation

ForwardedRecords

 

備註

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

SQL Server 2005 中的變更

計算片段的演算法用在 SQL Server 2005 中，比用在 SQL Server 2000 中來得更精確。因此，片段值會顯得比較高。例如，在 SQL Server 2000 中，如果資料表在同一範圍內有分頁 11 及分頁 13，但是沒有分頁 12，即不視為是片段的。不過，要存取這兩個分頁就必須執行兩個實體 I/O 作業，因此這在 SQL Server 2005 中算作是片段。

DBCC SHOWCONTIG 不會顯示 ntext、text 和 image 等資料類型的資料。這是因為在 SQL Server 2005 中，儲存文字和影像資料的文字索引 (SQL Server 2000 中的索引識別碼 255) 已不存在。如需有關索引識別碼 255 的詳細資訊，請參閱＜sys.sysindexes (Transact-SQL)＞。

另外，DBCC SHOWCONTIG 也不支援 SQL Server 2005 的某些新功能。例如：

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

sys.dm_db_index_physical_stats 動態管理檢視能夠完整支援 SQL Server 2005 的所有新功能。

資料表片段

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

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

  • 卸除和重建叢集索引。
    重建叢集索引會重新組織資料，造成飽和的資料頁面。您可以在 CREATE INDEX 中使用 FILLFACTOR 選項來設定飽和度的層級。這個方法的缺點是在卸除或重建周期內索引是離線的，作業不可部分完成。如果建立索引中斷，就不會重建索引。
  • 依照邏輯順序來重新排序索引的分葉層級頁面。
    請利用 ALTER INDEX…REORGANIZE，依照邏輯順序來重新排序索引的分葉層級頁面。由於這項作業是一個線上作業，因此，當執行陳述式時，可以使用索引。這項作業可能在未失去已完成工作的情況下中斷。這個方法的缺點是它的資料重新組織作業不如叢集索引卸除或重建作業的資料重新組織作業。
  • 重建索引。
    請利用 ALTER INDEX 和 REBUILD 重建索引。如需詳細資訊，請參閱＜ALTER INDEX (Transact-SQL)＞。

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

ms175008.note(zh-tw,SQL.90).gif附註：
有許多隨機插入且非常飽和的頁面之索引，頁面分割數會增加。這會造成更多的片段。

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

  • 比較範圍切換和範圍掃描。
    範圍切換值應該盡可能接近範圍掃描值。這個比例會計算成掃描密度值。這個值應該盡可能高，您可以縮減索引的片段化來改進它。
    ms175008.note(zh-tw,SQL.90).gif附註：
    如果索引跨越許多檔案時，這個方法便無法運作。
  • 了解邏輯掃描片段和範圍掃描片段值。
    邏輯掃描片段和範圍掃描片段 (在較小的範圍) 值是資料表片段化層級的最佳指標。這兩個值都應該盡可能接近零，不過，百分比 0 至 10 的值可能比較合適。
    ms175008.note(zh-tw,SQL.90).gif附註：
    如果索引跨越多個檔案，範圍掃描片段值會比較高。若要縮減這些值，您必須減少索引片段化。

權限

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

範例

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

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

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("HumanResources.Employee");
GO

B. 利用 OBJECT_ID 取得資料表識別碼，利用 sys.indexes 取得索引識別碼

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

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

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

USE AdventureWorks;
GO
DBCC SHOWCONTIG ("Production.Product", 1) WITH FAST;
GO

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

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

USE AdventureWorks;
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
OBJECT_ID (Transact-SQL)
sys.indexes (Transact-SQL)

其他資源

空間配置與重複使用

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄

版本 歷程記錄

2007 年 9 月 15 日
變更的內容：
  • 更正「備註」一節中的鎖定資訊。在 SQL Server 2005 中，這個命令僅使用意圖共用 (IS) 資料表鎖定，而非之前所述的共用 (S) 鎖定。
  • 釐清堆積之 Rows 資料行的定義。

2006 年 7 月 17 日
新增內容：
  • 在「SQL Server 2005 中的變更」一節中，新增有關計算片段之演算法的資訊。

2005 年 12 月 5 日
變更的內容：
  • 更正 LogicalFragmentation 的描述。