Sql-server – Rebuilding Unique Index with uniqueidentifier in SQL Azure never succeeds

azure-sql-databaseindexsql server

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.

Best Answer

My suggestion would be:

(a) talk to Azure support. This is not how it should be working AFAIK.

(b) when building your list of indexes to rebuild/reorganize, add a NOT EXISTS clause to the criteria to eliminate any indexes with GUIDs as the leading key column:

SELECT name, etc.
 FROM sys.indexes AS i
 INNER JOIN sys.dm_db_index_physical_stats AS s
 ON ...
 WHERE ... criteria ...
 AND NOT EXISTS 
 (
   SELECT 1 FROM sys.index_columns AS ic
     INNER JOIN sys.columns AS c
     ON ic.object_id = c.object_id
     AND ic.column_id = c.column_id
     AND ic.key_ordinal = 1 -- only leading column
     AND c.system_type_id = 36 -- uniqueidentifer
     WHERE ic.index_id = i.index_id
 );

(c) before deciding to manually deal with these columns, wait until they actually cause you a performance problem that you can demonstrate and undeniably identify the fragmentation of this index as the problem.

(d) or, just use the inverse query in (b) to perform manual drop/create all the time instead of dealing with the problem over and over.

Related Question