DBCC大全集之（适用版本MS SQLServer 2008 R2）----DBCC SHOWCON

发布时间：2020-12-12 13:36:45 所属栏目：MsSql教程来源：网络整理

导读：显示指定的表或视图的数据和索引的碎片信息。重要提示后续版本的 Microsoft SQL Server 将删除该功能。请不要在新的开发工作中使用该功能，并尽快修改当前还在使用该功能的应用程序。请改用sys.dm_db_index_physical_stats。 ?Transact-SQL 语法约定语法

后续版本的 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

要检查碎片信息的表或视图。如果未指定，则检查当前数据库中的所有表和索引视图。若要获得表或视图 ID，请使用?OBJECT_ID?函数。

index_name?| index_id

要检查碎片信息的索引。如果未指定，则该语句将处理指定表或视图的基本索引。若要获取索引 ID，请使用?sys.indexes?目录视图。

WITH

指定有关 DBCC 语句返回的信息类型的选项。

FAST

指定是否要对索引执行快速扫描和输出最少信息。快速扫描不读取索引的叶级或数据级页。

ALL_INDEXES

显示指定表和视图的所有索引的结果，即使指定了特定索引也是如此。

TABLERESULTS

将结果显示为含附加信息的行集。

ALL_LEVELS

仅为保持向后兼容性而保留。即使指定了 ALL_LEVELS，也只对索引叶级或表数据级进行处理。

NO_INFOMSGS

取消严重级别从 0 到 10 的所有信息性消息。

结果集

下表对结果集中的信息进行了说明。

扫描页数

表或索引中的页数。

扫描区数

表或索引中的区数。

区切换次数

遍历表或索引的页时，DBCC 语句从一个区移动到另一个区的次数。

每个区的平均页数

页链中每个区的页数。

扫描密度 [最佳计数:实际计数]

百分比。这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。

“最佳计数”是指在一切都连续链接的情况下，区更改的理想数目。“实际计数”是指区更改的实际次数。

逻辑扫描碎片

扫描索引的叶级页时返回的出错页的百分比。此数与堆无关。对于出错页，分配给索引的下一个物理页不是由当前叶级页中的“下一页”指针所指向的页。

区扫描碎片

扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。

注意如果索引跨越多个文件，则此数字无意义。

每页的平均可用字节数

扫描的页上平均可用字节数。此数字越大，则页的填充程度越低。如果索引不会有很多随机插入，则数字越小越好。此数字还受行大小影响：行越大，此数字就越大。

平均页密度(满)

页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。

如果指定了?table_id?和 FAST，则 DBCC SHOWCONTIG 将返回只包含以下列的结果集。

扫描页数
区切换次数
扫描密度 [最佳计数:实际计数]
区扫描碎片
逻辑扫描碎片

如果指定了 TABLERESULTS，则 DBCC SHOWCONTIG 将返回以下列以及上一个表中说明的九个列。

Object Name

处理的表或视图的名称。

ObjectId

对象名的 ID。

IndexName

处理的索引的名称。堆的 IndexName 为 NULL。

IndexId

索引的 ID。堆的 IndexId 为 0。

Level

索引的级别。级别 0 是索引的叶（或数据）级。

堆的级别为 0。

Pages

组成某个索引级别或整个堆的页数。

Rows

某个索引级别上的数据或索引记录数。对于堆，此值是整个堆中的数据记录数。

对于堆，此函数返回的记录数可能与通过对堆运行 SELECT COUNT(*) 返回的行数不匹配。这是因为一行可能包含多个记录。例如，在某些更新情况下，单个堆行可能由于更新操作而包含一条前推记录和一条被前推记录。此外，多数大型 LOB 行在 LOB_DATA 存储中拆分为多个记录。

MinimumRecordSize

某个索引级别或整个堆中的最小记录大小。

MaximumRecordSize

某个索引级别或整个堆中的最大记录大小。

AverageRecordSize

某个索引级别或整个堆中的平均记录大小。

ForwardedRecords

该索引级别或整个堆中的被前推记录数。

Extents

某个索引级别或整个堆中的区数。

ExtentSwitches

AverageFreeBytes

AveragePageDensity

页的平均密度，以百分比表示。该值会考虑行大小。因此，该值可以更准确地指示页的填充程度。百分比越大越好。

ScanDensity

百分比。这是“最佳计数”与“实际计数”的比率。如果所有内容都是连续的，则该值为 100；如果该值小于 100，则存在一些碎片。

BestCount

所有内容连续链接时的区更改理想数量。

ActualCount

区更改实际数量。

LogicalFragmentation

ExtentFragmentation

扫描索引的叶级页时出错区所占的百分比。此数与堆无关。对于出错区，包含当前索引页的区在物理上不是包含上一个索引页的区的下一个区。

注意如果索引跨越多个文件，则此数字无意义。

如果指定了 WITH TABLERESULTS 和 FAST，则结果集将与指定 WITH TABLERESULTS 时一样，但以下列的值将为 null：

Extents

AverageFreeBytes

AveragePageDensity

ExtentFragmentation

注释

如果指定了?index_id，则 DBCC SHOWCONTIG 语句将遍历指定索引的叶级上的页链。如果只指定?table_id，或者?index_id?为 0，则将扫描指定表的数据页。此操作只需要一个意向共享 (IS) 表锁。通过这种方式，除了需要排他 (X) 表锁的更新和插入以外，可执行所有更新和插入。这就可以根据返回的统计信息数量，实现执行速度与不减少并发之间进行权衡。但是，如果使用此命令只是为了测量碎片，则建议您使用 WITH FAST 选项以优化性能。快速扫描不读取索引的叶级或数据级页。WITH FAST 选项不适用于堆。

SQL Server 2008 和更高版本中的碎片计算算法比 SQL Server 2000 中的更精确。因此，碎片值显得更高。例如，在 SQL Server 2000 中，如果一个表的第 11 页和第 13 页在同一区中，而第 12 页不在该区中，该表不被视为含有碎片。但是访问这些页需要两次物理 I/O 操作，因此，在 SQL Server 2008 中，这将算作碎片。

限制

DBCC SHOWCONTIG 不显示数据类型为?ntext、text?和?image?的数据。这是因为不再有存储文本和图像数据的文本索引（SQL Server 2000 中的索引 ID 为 255）。有关索引 ID 255 的详细信息，请参阅?sys.sysindexes (Transact-SQL)。

此外，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 选项进行配置。这种方法的缺点是索引在删除/重新创建周期内为脱机状态，并且该操作是一个整体，不可中断。如果中断索引创建，则不能重新创建索引。
对索引的叶级页按逻辑顺序重新排序。

使用 INDEX…REORGANIZE，对索引的页级页按逻辑顺序重新排序。由于此操作是联机操作，因此语句运行时索引可用。此外，中断该操作不会丢失已完成的工作。这种方法的缺点是在重新组织数据方面没有聚集索引的删除/重新创建操作有效。
重新生成索引。

使用 REBUILD 和 ALTER INDEX 重新生成索引。有关详细信息，请参阅?ALTER INDEX (Transact-SQL)。

“每页的平均可用字节数”和“平均页密度(满)”统计信息指示了索引页的填满程度。对于没有许多随机插入的索引，“每页的平均可用字节数”数应该较小，“平均页密度(满)”数应该较大。使用指定的 FILLFACTOR 选项删除并重建索引可改善统计信息。另外，REORGANIZE 和 ALTER INDEX 可根据其 FILLFACTOR 选项压缩索引，从而改善统计信息。

如果索引有很多随机插入和很满的页，则其页拆分数将增加。这将导致更多的碎片。

索引的碎片级别可通过以下方式确定：

比较“区切换次数”和“扫描区数”的值。

“区切换次数”的值应尽可能接近于“扫描区数”的值。此比率将作为“扫描密度”值计算。此值应尽可能的大，可通过减少索引碎片得到改善。

如果索引涉及多个文件，则此方法无效。

了解“逻辑扫描碎片”和“区扫描碎片”的值。

“逻辑扫描碎片”和“区扫描碎片”（对于较小的区）的值是表的碎片级别的最好指标。这两个值应尽可能接近零，但 0% 到 10% 之间的值都是可接受的。

如果索引涉及多个文件，则“区扫描碎片”的值将较高。若要减小这些值，必须减少索引碎片。

权限

用户必须是表所有者，或者是?sysadmin?固定服务器角色、db_owner?固定数据库角色或?db_ddladmin?固定数据库角色的成员。

示例

A. 显示表的碎片信息

以下示例将显示?Employee?表的碎片信息。

Transact-SQL

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

B. 使用 OBJECT_ID 获得表 ID，使用 sys.indexes 获得索引 ID

以下示例使用?OBJECT_ID?和?sys.indexes?目录视图，以获取 AdventureWorks2008R2 数据库中?Production.Product?表的?AK_Product_Name?索引的表 ID 和索引 ID。

USE AdventureWorks2008R2;
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. 显示表的简略结果集

以下示例将返回 AdventureWorks2008R2 数据库中?Product?表的简略结果集。

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

D. 显示数据库中每个表的每个索引的完整结果集

以下示例将显示 AdventureWorks2008R2?数据库中每个表的每个索引的完整表结果集。

USE AdventureWorks2008R2;
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,@frag;
END;

-- Close and deallocate the cursor.
CLOSE indexes;
DEALLOCATE indexes;

-- Delete the temporary table.
DROP TABLE #fraglist;
GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

重要提示
显示指定的表或视图的数据和索引的碎片信息。
统计信息	说明
注意
注意