5

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.

  • Silly question, but are you sure the index rebuild command has online = on, and that it's being honored? – Aaron Bertrand Aug 14 '14 at 19:43
  • No silly questions...Yes, I'm sure I had WITH (ONLINE=ON). I believe it's being honored because there's a definite time difference between OFF and ON, but is there a particular way to know whether it's being honored? – Henry Lee Aug 14 '14 at 19:46
  • Though, upon further thought, not all queries were blocked. If my scenario was not supported, that would seem to suggest it would become an offline operation and block all queries. Perhaps it's more nuanaced... – Henry Lee Aug 14 '14 at 20:12
  • Dumb question; why drop the PK? Are you recreating it? Can you do this during downtime - it might be a more productive use of your time. – Max Vernon Aug 14 '14 at 21:57
  • Not sure if this helps: technet.microsoft.com/en-us/library/cc966402.aspx – Max Vernon Aug 14 '14 at 21:59
4

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.

  • That makes sense. I dropped the nonclustered indexes prior to attempting to drop the clustered index (so they wouldn't get rebuilt twice) - which forced all queries to scan the entire table. So regarding system resources, this caused disk speeds to come to a crawl (as not all the data fits in memory). This then had a cascading effect as the actual rebuild then crept along as well. – Henry Lee Aug 15 '14 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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