Online Index Rebuild shows self blocking
1
vote
0
answers
802
views
I've been doing some testing on rebuilding some larger clustered indexes (with online + sort_in_tempdb on) and I'm observing something that I can't immediately explain. I have a hunch that this is some sort of intra-query blocking due to the rebuild going parallel but can't 100% confirm. Running SQL 2016 EE - SP2 + CU8 (13.0.5426)
**The query I'm running:**
USE [UserDB]
GO
ALTER INDEX [PK_Order] ON [dbo].[Order] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)
GO
**Whoisactive says:**
**Digging further into that session and it's ecids, I see the following:**
Now, with sort in tempdb (dbid 2) being on, my concern here is that the


resource_description
column is showing page locks on dbid5 (the DB that I'm rebuilding the index on).
This looks like a page lock on the user database that may cause blocking or disruptions. Can anyone shine some light on what exactly is happening here? Is this just unbalanced parallelism and threads have to sit on their hands until the others finish?
Worth noting that these locks do clear after a while (5-10 mins) and we revert back to just CXCONSUMER waits.
Asked by Kris Gruttemeyer
(3879 rep)
Feb 6, 2020, 01:19 PM
Last activity: Feb 6, 2020, 01:24 PM
Last activity: Feb 6, 2020, 01:24 PM