Sql-server – Why is SQL Server’s online indexing causing blocking

blockingsql serversql-server-2008

My understanding is that online indexing acquires locks at the beginning (Preparation) and end (Final) phases. However, we see a lot of DML queries blocked during the Build phase.

A sampling of the queries being blocked show they have a few things in common – the Wait Type is PAGELATCH_UP, the Wait Resource references a PFS page, and the query uses locking hints (NOLOCK, ROWLOCK, etc). Could locking hints be interfering?

Can anyone shed some light here or point me in the right direction?

Let me add, the actual command was:

ALTER TABLE dbo.Whatever DROP CONSTRAINT [PK_Whatever] WITH (ONLINE=ON);

This was the clustered index and there were no nonclustered indexes.

Best Answer

Despite what the official documentation says, dropping a clustered index using ALTER TABLE DROP CONSTRAINT can be performed online (tested on SQL Server 2005-2014):

USE Sandpit;

ALTER DATABASE Sandpit
SET RECOVERY SIMPLE;

CREATE TABLE dbo.Test 
(
    col1 integer NOT NULL, 
    CONSTRAINT PK PRIMARY KEY CLUSTERED (col1)
);

INSERT dbo.Test 
VALUES (1);

CHECKPOINT;

BEGIN TRANSACTION;

ALTER TABLE dbo.Test 
DROP CONSTRAINT PK WITH (ONLINE = ON);

SELECT 
    [Current LSN], 
    [Operation], 
    [Transaction Name], 
    [Xact ID], 
    [Description]
FROM sys.fn_dblog(NULL,NULL) AS FD
WHERE
    FD.[Transaction Name] IS NOT NULL;

ROLLBACK TRANSACTION;

DROP TABLE Test;

Online plan

Transaction log records

Nevertheless, performing an operation online does not mean that other concurrent queries will not be impacted at all. The online operation does not take long term locks, but it may still consume considerable system resources (e.g. memory, processor, I/O). If these resources become a bottleneck, concurrent queries may experience resource-related waits.

In your specific scenario, the PAGELATCH_UP waits on PFS pages indicate latch contention on allocation resources. Any possible corrective action depends on the resource description associated with the latch waits.

Related Question