曲演杂坛--重建索引后，还使用混合分区么？（Are mixed pages removed by an index rebuild?）...-优快云博客

本文详细探讨了在 SQL Server 中，表和索引在创建、重建以及操作过程中的分区策略，特别是混合区与统一区的分配机制。通过实验演示，展示了如何使用 sp_AllocationMetadata 和 DBCCEXTENTINFO 等工具来观察对象在不同操作后所处的分区类型。实验结果表明，无论是在联机还是脱机重建索引，或是在创建索引时使用特定选项，新表和索引始终倾向于在混合区上分配页，除非开启了特定的跟踪选项。文章还讨论了混合区数据页可能减少的原因及背后的原理。

原文来自：http://www.sqlskills.com/blogs/paul/mixed-pages-removed-index-rebuild/

在SQL SERVER 中，区是管理空间的基本单位，连续的8个页为一分区，分区可分为混合区和统一区（也叫独占区），混合区内存放一个或多个对象的数据，统一区只存放一个对象的数据。为提高空间的利用率，对于新表或索引，会先从混合区上分配页，当表或索引增长到 8 页时，将变成使用统一区进行后续分配。

让我们来测试下

首先，创建测试数据

--====================================
--创建测试表
CREATE TABLE [MixedTest]
    (
      [c1] BIGINT IDENTITY ,
      [c2] CHAR(8000) DEFAULT 'a'
    );
--=======================================
--创建聚集索引
CREATE CLUSTERED INDEX [MixedTest_CL] 
ON [MixedTest] ([c1]);

SET NOCOUNT ON;
GO
--====================================
--插入1000条数据
INSERT  INTO [MixedTest]
        DEFAULT VALUES;
GO 1000

Paul 使用sp_AllocationMetadata来查看对象的IAM页，然后再使用DBCC PAGE 查看IAM页的数据，从而判断数据页所在分区时混合还是统一区。

sp_AllocationMetadata的CODE:

/*============================================================================
  File:     sp_AllocationMetadata.sql

  Summary:  This script cracks the system tables to provide top-level
            metadata about a table or index

  SQL Server Versions: 2005 onwards
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com

  (c) 2014, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE [master];
GO

IF OBJECT_ID (N'sp_AllocationMetadata') IS NOT NULL
    DROP PROCEDURE [sp_AllocationMetadata];
GO

CREATE PROCEDURE [sp_AllocationMetadata]
(
    @object SYSNAME = NULL
)
AS
SELECT
    OBJECT_NAME ([sp].[object_id]) AS [Object Name],
    [sp].[index_id] AS [Index ID],
    [sa].[allocation_unit_id] AS [Alloc Unit ID],
    [sa].[type_desc] AS [Alloc Unit Type],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 6, 1) +
            SUBSTRING ([sa].[first_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_page], 4, 1) +
            SUBSTRING ([sa].[first_page], 3, 1) +
            SUBSTRING ([sa].[first_page], 2, 1) +
            SUBSTRING ([sa].[first_page], 1, 1))) +
    ')' AS [First Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 6, 1) +
            SUBSTRING ([sa].[root_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[root_page], 4, 1) +
            SUBSTRING ([sa].[root_page], 3, 1) +
            SUBSTRING ([sa].[root_page], 2, 1) +
            SUBSTRING ([sa].[root_page], 1, 1))) +
    ')' AS [Root Page],
    '(' + CONVERT (VARCHAR (6),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 6, 1) +
            SUBSTRING ([sa].[first_iam_page], 5, 1))) +
    ':' + CONVERT (VARCHAR (20),
        CONVERT (INT,
            SUBSTRING ([sa].[first_iam_page], 4, 1) +
            SUBSTRING ([sa].[first_iam_page], 3, 1) +
            SUBSTRING ([sa].[first_iam_page], 2, 1) +
            SUBSTRING ([sa].[first_iam_page], 1, 1))) +
    ')' AS [First IAM Page]    
FROM
    sys.system_internals_allocation_units AS [sa],
    sys.partitions AS [sp]
WHERE
    [sa].[container_id] = [sp].[partition_id]
AND [sp].[object_id] =
    (CASE WHEN (@object IS NULL)
        THEN [sp].[object_id]
        ELSE OBJECT_ID (@object)
    END);
GO

EXEC sys.sp_MS_marksystemobject [sp_AllocationMetadata];
GO

--USE [AdventureWorks];
--GO
--EXEC [sp_AllocationMetadata] N'HumanResources.Employee';
--GO

View Code

个人更喜欢使用DBCC EXENTINFO来查看

--================================================================================

--===========================
--插入数据后查看
DBCC EXTENTINFO('TestDB','MixedTest')

PS:注意上面8个数的Ext_Size为1，代表使用混合区

--================================================================================

--===================================
--在线重建索引后查看
ALTER INDEX [MixedTest_CL] 
ON [MixedTest] REBUILD
WITH(ONLINE=ON)

DBCC EXTENTINFO('TestDB','MixedTest')

对比第一次的数据页ID，会发现重建后的索引，使用新的数据页，但是仍会为表分配混合区。

--================================================================================

--===================================
--脱机重建索引后查看
ALTER INDEX [MixedTest_CL] 
ON [MixedTest] REBUILD
WITH(ONLINE=OFF)

DBCC EXTENTINFO('TestDB','MixedTest')

在脱机重建索引情况下，仍会为表分配混合区。

--=========================================================================

使用DROP_EXISTING=ON选项创建索引

--===========================
--使用DROP_EXISTING=ON选项创建索引
CREATE CLUSTERED INDEX [MixedTest_CL] 
ON [MixedTest] ([c1])
WITH(DROP_EXISTING=ON );

DBCC EXTENTINFO('TestDB','MixedTest')

使用DROP_EXISTING=ON选项创建索引，仍会为表分配混合区。

--================================================================================

--===========================
--删除再创建索引
DROP INDEX [MixedTest_CL] 
ON [MixedTest]
GO
CREATE CLUSTERED INDEX [MixedTest_CL] 
ON [MixedTest] ([c1])
GO
DBCC EXTENTINFO('TestDB','MixedTest')

删除再创建索引，仍会为表分配混合区。

--================================================================================

--===================================
--启用TF1118后,脱机重建索引后查看
DBCC TRACEON(1118,-1)

ALTER INDEX [MixedTest_CL] 
ON [MixedTest] REBUILD
WITH(ONLINE=OFF)

DBCC EXTENTINFO('TestDB','MixedTest')

在开启1118后，不再为对象分配混合区

--===============================================================================

部分同学会疑惑，为什么不一定总是8个页位于混合区中，个人理解是最开始存到到混合区的8个页，在后面的索引操作中，发生了数据页合并和移动等情况，因此导致原来在混合区的数据页被回收或移动到其他统一区上，因此导致存在混合区的数据页小于8个

Paul的原文如下：

there are only 7 mixed pages in the singe-page slot array above. What happened? The answer is that the offline index rebuild ran in parallel, with each thread building a partial index, and then these are stitched together. The ‘stitching together’ operation will cause some of the non-leaf index pages to be deallocated as their contents are merged together. This explains the deallocated page that was originally tracked by entry 3 in the slot array.

--===============================================================================

总结：在不开启1118条件下，无论是脱机还是联机，还是删除重建，无论是多线程还是单线程，实现各部相同，但都会为新表和索引分配混合区上的页。

--===============================================================================

问题1 ：在MSDN上有“如果对现有表创建索引，并且该表包含的行足以在索引中生成 8 页，则对该索引的所有分配都使用统一区进行。”

但是经过测试，发现仍会分配混合区。

MSDN连接：http://msdn.microsoft.com/zh-cn/library/ms190969(v=sql.105).aspx

--==============================================================================

各位大师，猩猩，妹子来啦，此次该有掌声