9

If I partition a table with ntext, text or image datatypes and rebuild an index on a single partition with online = off, does that lock the whole table or just the partition in question?

0

1 Answer 1

13
+50

I had some time to look at this, and since I already had some of the demo scripts written, it was pretty easy to check on the rest. Let's do the setup, then look at the results. This will create the necessary partitioned table and index.

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);

That gives me 5 partitions with 1 million rows, and one empty partition.

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');

Fancy table:

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

Here's the XE session I'm using to see which locks the index rebuilds need:

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

With that in place, I can rebuild my partitions, and then dig into 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;

Now, I'm gonna put the XE event shredding stuff at the end, because it's pretty ugly, and there's no reason to make everyone sit through that to see the results. I'm going to use the first partition's results as an example, but they're just about identical across all 6 partitions, even the empty one.

I'm limiting the results of the update to only the object level locks. These are the only ones we care about.

   +---------------+-------------------------+----------------+--------------+-------+------------------+--------+
|   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 |
+---------------+-------------------------+----------------+--------------+-------+------------------+--------+

From what I can tell, for each partition, a SCH-M lock is taken out at the beginning of the index rebuild, and released at the end ON THE TABLE.

For example, if I run a single partition rebuild in a transaction:

BEGIN TRAN

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

--ROLLBACK

And then in another SSMS window:

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

The select is blocked until I kill the rebuild. When it finishes, the query plan shows partition elimination occurring, so only partition 1 being rebuilt appears to effect the entire table.

NUTS

Hope this helps!

Now here's the awful XE session shredding code:

    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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.