We have a number of tables (~1M records) that have a column on them defined as:
[GlobalID] [uniqueidentifier] NOT NULL that gets auto-populated with
newid(). We use this ID for synchronizing data across multiple systems, databases, file imports/exports, etc.
For this column, we have the following index created:
CREATE UNIQUE NONCLUSTERED INDEX [IxUx_XXXXXX_GlobalID] ON [dbo].[XXXXXX] ( [GlobalID] ASC )
As expected, this index gets fragmented pretty quickly so we have to be on the ball with keeping it rebuilt. However, this is where we have a problem. My maintenance script that goes through and rebuilds all indexes over x% fragmented with
ONLINE=ON always hangs forever on these indexes. I've tried to let one run for over 3 hours without success when the database was mostly to completely idle. Just for kicks, I even put the DB into single user mode and nothing changed. I also tried with
ONLINE=OFF, again with no change. However, I am able to simply drop/create the index and it completes within ~15 seconds!
Just to make sure there's nothing funny with my maintenance script, I've manually tried rebuilding these indexes with no success. The script I use is:
ALTER INDEX IxUx_XXXXXX_GlobalID ON [dbo].[XXXXXX] REBUILD WITH (ONLINE=ON) and it seems like it will never succeed. Given that I can drop and recreate it in only 15 seconds, 3 hours is WAY more than plenty to see this succeed on an idle database.
For now, I've altered my maintenance script to filter out indexes with "GlobalID" in the name and then have a follow-up script that drops/creates these indexes. This gets us by until we start having naming variations on these types of columns (i.e. we need a uniqueidentifier for some other purpose).
Any idea why rebuilding such indexes would never finish within a reasonable amount of time? I see this happen across ~12 tables, all with essentially the same column/index on them.