分区表上的离线索引重建

我花了一些时间看这个，因为我已经写了一些演示脚本，所以很容易检查其余部分。让我们进行设置，然后查看结果。这将创建必要的分区表和索引。

CREATE PARTITION FUNCTION YourMom ( INT )
    AS RANGE LEFT FOR VALUES ( 1000000, 2000000, 3000000, 4000000, 5000000 );

CREATE PARTITION SCHEME YourDad
    AS PARTITION YourMom
    ALL TO ( [PRIMARY] );

CREATE TABLE dbo.YourAuntDebbie
(
    Id INT,
    StopUsingDeprecatedDataTypes NTEXT
) ON YourDad (Id);


DECLARE @counter INT = 1;
WHILE @counter < 6
    BEGIN

        RAISERROR('Run number: %d', 0, 1, @counter) WITH NOWAIT;

        INSERT dbo.YourAuntDebbie WITH ( TABLOCK ) ( Id, StopUsingDeprecatedDataTypes )
        SELECT TOP 1000000 x.n + CASE WHEN @counter = 1 THEN 0
                                      WHEN @counter = 2 THEN 1000000 
                                      WHEN @counter = 3 THEN 2000000 
                                      WHEN @counter = 4 THEN 3000000 
                                      WHEN @counter = 5 THEN 4000000 
                                      ELSE 0 
                                      END, 
                           REPLICATE(N'A', x.n % 10000)
        FROM   (   SELECT ROW_NUMBER() OVER ( ORDER BY @@ROWCOUNT ) AS n
                   FROM   sys.messages AS m
                   CROSS JOIN sys.messages AS m2 ) AS x;

        SET @counter += 1;

    END;

CREATE CLUSTERED INDEX ix_whatever
    ON dbo.YourAuntDebbie ( Id ) ON YourDad(Id);

这给了我 5 个具有 100 万行的分区和一个空分区。

SELECT OBJECT_NAME(p.object_id) AS table_name, p.partition_number, p.rows
FROM   sys.partitions AS p
WHERE  p.object_id = OBJECT_ID('dbo.YourAuntDebbie');

花式表：

+----------------+------------------+---------+
|   table_name   | partition_number |  rows   |
+----------------+------------------+---------+
| YourAuntDebbie |                1 | 1000000 |
| YourAuntDebbie |                2 | 1000000 |
| YourAuntDebbie |                3 | 1000000 |
| YourAuntDebbie |                4 | 1000000 |
| YourAuntDebbie |                5 | 1000000 |
| YourAuntDebbie |                6 |       0 |
+----------------+------------------+---------+

这是我用来查看索引重建需要哪些锁的 XE 会话：

CREATE EVENT SESSION Locks
    ON SERVER
    ADD EVENT sqlserver.lock_acquired
    ( SET collect_resource_description = ( 1 )
     ACTION ( sqlserver.sql_text )
     WHERE (   sqlserver.equal_i_sql_unicode_string(sqlserver.database_name, N'Crap')
               AND package0.equal_uint64(sqlserver.session_id, ( 61 )))),
    ADD EVENT sqlserver.lock_released
    ( SET collect_resource_description = ( 1 )
     ACTION ( sqlserver.sql_text )
     WHERE (   sqlserver.database_name = N'Crap'
               AND sqlserver.session_id = ( 61 )))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\Locks' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 30 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = ON,
           STARTUP_STATE = OFF );
GO

有了它，我可以重建我的分区，然后深入研究 XE。

ALTER EVENT SESSION Locks ON SERVER STATE = START;

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 1 WITH (ONLINE = OFF);
GO 

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 2 WITH (ONLINE = OFF);
GO 

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 3 WITH (ONLINE = OFF);
GO 

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 4 WITH (ONLINE = OFF);
GO 

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 5 WITH (ONLINE = OFF); 
GO 

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 6 WITH (ONLINE = OFF);
GO 

ALTER EVENT SESSION Locks ON SERVER STATE = STOP;

现在，我要把 XE 事件粉碎的东西放在最后，因为它非常丑陋，没有理由让每个人都坐下来看结果。我将使用第一个分区的结果作为示例，但它们在所有 6 个分区中几乎相同，即使是空分区也是如此。

我将更新结果限制为仅对象级锁。这些是我们唯一关心的。

   +---------------+-------------------------+----------------+--------------+-------+------------------+--------+
|   EventName   |        EventDate        |   ObjectName   | ResourceType | Mode  | PARTITIONREBUILT | Events |
+---------------+-------------------------+----------------+--------------+-------+------------------+--------+
| lock_acquired | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_M | PARTITION = 1    |      1 |
| lock_acquired | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |      1 |
| lock_released | 2017-10-03 13:21:14.554 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |      1 |
| lock_acquired | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | S     | PARTITION = 1    |      6 |
| lock_acquired | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |     30 |
| lock_released | 2017-10-03 13:21:14.603 | YourAuntDebbie | OBJECT       | SCH_S | PARTITION = 1    |     24 |
| lock_released | 2017-10-03 13:21:14.867 | YourAuntDebbie | OBJECT       | SCH_M | PARTITION = 1    |      1 |
+---------------+-------------------------+----------------+--------------+-------+------------------+--------+

据我所知，对于每个分区SCH-M，在索引重建开始时取出一个锁，并在THE TABLE结束时释放。

例如，如果我在事务中运行单个分区重建：

BEGIN TRAN

ALTER INDEX ix_whatever ON dbo.YourAuntDebbie REBUILD PARTITION = 1 WITH (ONLINE = OFF);

--ROLLBACK

然后在另一个 SSMS 窗口中：

SELECT *
FROM dbo.YourAuntDebbie AS yad
WHERE yad.Id = 6000000
AND 1 = (SELECT 1)

选择被阻止，直到我终止重建。完成后，查询计划显示正在消除分区，因此只有正在重建的分区 1 似乎会影响整个表。

希望这可以帮助！

现在这是可怕的 XE 会话粉碎代码：

    CREATE TABLE #Locks
       (
         ID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
         WaitsXML XML 
       );

INSERT  #Locks
        ( WaitsXML )
SELECT    CONVERT(XML, event_data) AS TargetData
FROM      sys.fn_xe_file_target_read_file( 'c:\temp\Locks*.xel', NULL, NULL, NULL);

WITH locks
AS ( SELECT l.WaitsXML.value('(/event/@name)[1]', 'VARCHAR(128)') AS EventName,
            l.WaitsXML.value('(/event/@timestamp)[1]', 'DATETIME2(3)') AS EventDate,
            l.WaitsXML.value('(event/data[@name="object_id"]/value)[1]', 'NUMERIC') AS ObjectId,
            l.WaitsXML.value('(event/data[@name="resource_type"]/text)[1]', 'VARCHAR(128)') AS ResourceType,
            l.WaitsXML.value('(event/data[@name="mode"]/text)[1]', 'VARCHAR(128)') AS Mode,
            l.WaitsXML.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(128)') AS SQLText,
            l.WaitsXML
     FROM   #Locks AS l )
SELECT   locks.EventName,
         locks.EventDate,
         ISNULL(OBJECT_NAME(locks.ObjectId), 'Unknown') AS ObjectName,
         locks.ResourceType,
         locks.Mode,
         SUBSTRING(
             locks.SQLText,
             CHARINDEX('PARTITION', locks.SQLText),
             CHARINDEX('WITH', locks.SQLText, CHARINDEX('PARTITION', locks.SQLText))
             - CHARINDEX('PARTITION', locks.SQLText)) AS PARTITIONREBUILT,
         COUNT(*) AS Events
FROM     locks
WHERE    OBJECT_NAME(locks.ObjectId) = 'YourAuntDebbie'
         --OR OBJECT_NAME(locks.ObjectId) IS NULL
GROUP BY ISNULL(OBJECT_NAME(locks.ObjectId), 'Unknown'),
         SUBSTRING(
             locks.SQLText,
             CHARINDEX('PARTITION', locks.SQLText),
             CHARINDEX('WITH', locks.SQLText, CHARINDEX('PARTITION', locks.SQLText))
             - CHARINDEX('PARTITION', locks.SQLText)),
         locks.EventName,
         locks.EventDate,
         locks.ResourceType,
         locks.Mode
ORDER BY locks.EventDate;