DBCC SHOWCONTIG在SQL Server 2005与SQL Server 2000里的比较和改进
--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址DBCC SHOWCONTIG用来显示指定表的数据和索引的碎片信息。
在SQLServer2000里，DBCC SHOWCONTIG在下面的情形下使用时会在表上产生一个S锁，因而会影响DML操作（INSERT、UPDATE、DELETE）。
- 当表是一个堆表时
- 当表有聚集索引，但是仅指定table_name参数调用DBCC SHOWCONTIG（无论使用WITH FAST选项与否）。
- 当表有聚集索引，并且指定了table_name参数和index_name或index_id参数，但是没有使用WITH FAST选项。
对于大表，DBCC SHOWCONTIG会花费大量的时间。在此期间对DML操作的影响会波及应用程序。如果表有聚集索引，你能在SQLServer2000里解决这个问题。
这个问题在SQLServer2005里已经得到解决。在SQLServer2005里，所有使用DBCC SHOWCONTIG在表上要求IS锁，因而允许同时进行DML操作。
此外，在SQLServer2005里，建议使用表值动态管理函数sys.dm_db_index_physical_stats来代替DBCC SHOWCONTIG命令。
本文描述在SQLServer2000里的问题和解决方法，在SQLServer2005里的解决方法和建议。
生成测试环境执行下面步骤模拟环境：
1.创建表：
USE [TESTDB]
GO
CREATE TABLE [dbo].[test_contig](
[SK_Store_ID] [smallint] NOT NULL,
[SK_Item_ID] [int] NOT NULL,
[SK_Date_ID] [int] NOT NULL,
[SK_Buyer_ID] [int] NULL,
[SK_Model_Strategy_ID] [smallint] NULL,
[Model_QTY] [int] NULL,
[On_Hand_Qty] [int] NULL,
[On_Order_Qty] [int] NULL,
[Return_Qty] [int] NULL,
[Retail_Amt] [numeric](9, 2) NULL,
[Days_In_Stock] [tinyint] NULL,
[ETL_Load_ID] [int] NULL,
[Last_Modified_Date] [datetime] NULL,
[Modified_by_User] [varchar](30) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX ix_test_contig ON test_contig(sk_store_id, sk_item_id, sk_date_id)
GO
2.载入大量数据到表里。在表里载入3千万行。这会确保一个会话有足够的时间运行DBCC SHOWCONTIG，同时切换到另一个会话去查询目录表看看DBCC SHOWCONTIG运行期间用的什么锁。
3.连接到SQLServer2000执行下面的命令：
DBCC SHOWCONTIG (test_contig)
SQL Server 2000里的局限性
正如前面测试所显示，DBCC SHOWCONTIG在表上产生了一个S锁。为了查看它，当DBCC SHOWCONTIG运行时用另一个会话来运行下面的查询：
SELECT o.name, CASE l.rsc_type
WHEN 1 THEN 'NULL'
WHEN 2 THEN 'Database'
WHEN 3 THEN 'File'
WHEN 4 THEN 'Index'
WHEN 5 THEN 'Table'
WHEN 6 THEN 'Page'
WHEN 7 THEN 'Key'
WHEN 8 THEN 'Extent'
WHEN 9 THEN 'RID'
WHEN 10 THEN 'Application'
END AS Resource_Type,
CASE l.req_mode
WHEN 0 THEN 'NULL'
WHEN 1 THEN 'Sch-S'
WHEN 2 THEN 'Sch-M'
WHEN 3 THEN 'S'
WHEN 4 THEN 'U'
WHEN 5 THEN 'X'
WHEN 6 THEN 'IS'
WHEN 7 THEN 'IU'
WHEN 8 THEN 'IX'
WHEN 9 THEN 'SIU'
WHEN 10 THEN 'SIX'
WHEN 11 THEN 'UIX'
WHEN 12 THEN 'BU'
WHEN 13 THEN 'RangeS_S'
WHEN 14 THEN 'RangeS_U'
WHEN 15 THEN 'RangeI_N'
WHEN 16 THEN 'RangeI_S'
WHEN 17 THEN 'RangeI_U'
WHEN 18 THEN 'RangeI_X'
WHEN 19 THEN 'RangeX_S'
WHEN 20 THEN 'RangeX_U'
WHEN 21 THEN 'RangeX_X'
END AS Lock_Type
FROM testdb.dbo.sysobjects o, master.dbo.syslockinfo l
WHERE l.rsc_objid = o.id
GO
输出结果如下：
--
Locks held in SQL Server 2000 | Name | Resource_Type | Lock_Type |
| test_contig | Table | S |
在SQLServer2000里，由于DBCC SHOWCONTIG在表上有S锁，所以其他的事务如试图插入、更新或删除表里的数据都将被阻塞。这就是DBCC SHOWCONTIG的局限性。对于大表，它会花费更长的时间。对于测试环境的例子，DBCC SHOWCONTIG（test_config）花费了将近2分钟；但对于大表，可能会更长。因此，在日常事务处理的时间不要去执行DBCC SHOWCONTIG。
在下面的执行语句里，在DBCC SHOWCONTIG期间也会对表产生一个S锁：
DBCC SHOWCONTIG (test_contig) WITH FAST注意我们仍然在使用SQLServer2000
使用WITH FAST选项意味着DBCC SHOWCONTIG将对索引执行快速的扫描并输出最小的信息。快速扫描不会读索引的叶级或数据页。即使快速扫描花费更少的时间，它仍然在表上产生S锁。
SQL Server 2000里的解决方法在SQLServer2000里，可以改变DBCC SHOWCONTIG命令如下来解决：
DBCC SHOWCONTIG (test_contig, 1) WITH FAST GO
通过在DBCC SHOWCONTIG调用里指定index_id=1，你要求它检查聚集索引的碎片信息。通过指定WITH FAST选项，你要求执行快速扫描且输出最小的信息。快速扫描不会读索引的叶级或数据页。
这个执行在表上产生IS锁。因而它不会阻塞同时对表进行的插入、更新或删操作。如果你在执行DBCC SHOWCONTIG期间通过使用前面的那个脚本来查询目录表的话，你会得到下面的结果：
--Locks held in SQL Server 2000 with index_id=1
| Name | Resource_Type | Lock_Type |
| test_contig | Table | IS |
注意这个解决方法只适用于有聚集索引的表。
SQL Server 2005里的解决方法在SQLServer2000里的阻塞问题在SQLServer2005里已经得到了解决。对于SQLServer2005，所有DBCC SHOWCONTIG的执行都在表上产生IS锁。因而不会阻塞同时对表进行的插入、更新或删除操作。当DBCC SHOWCONTIG运行时，用另一个会话执行下面的查询以便列出锁信息：
SELECT o.name, l.resource_type, l.request_mode
FROM sys.objects o, sys.dm_tran_locks l
WHERE l.resource_associated_entity_id = o.object_id
GO
输出结果如下：
--Locks held in SQL Server 2005
Name Resource_Type Lock_Type
test_contig Table IS
SQL Server 2005里的建议DBCC命令在将来的SQLServer版本里将不再被支持。因而，建议你使用函数sys.dm_db_index_physical_stats。为了检查表的碎片，执行下面的查询：
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('test_contig'), NULL, NULL , 'DETAILED')
GO
当该查询运行时，如果你使用前面的脚本来检查锁的话，将会输出下面的结果：
--Locks held in SQL Server 2005
| Name | Resource_Type | Lock_Type |
| test_contig | Table | IS |
综述在SQLServer2000里，DBCC SHOWCONTIG的一些使用会在表上产生S锁，因而会影响DML（INSERT、UPDATE、DELETE）操作。在大表上，DBCC SHOWCONTIG会花费更长的时间。在SQLServer2000里如果表有聚集索引，那么可以解决这个问题。
这个问题在SQLServer2005里已经得到了解决。在SQLServer2005里，所有DBCC SHOWCONTIG的使用都在表上产生的是IS锁，因而允许同时进行DML操作。此外，在SQLServer2005里，建议使用表值动态管理函数sys.dm_db_index_physical_stats来代替DBCC SHOWCONTIG命令。
