Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Does the fragmentation on these indexes actually cause any observable performance problems? How long after the drop/create are they completely fragmented again (e.g. how much does the drop/create really buy you)? I suspect you are performing this maintenance to keep the fragmentation numbers low rather than in response to an actual performance issue. –  Aaron Bertrand Apr 26 '13 at 16:06
    
(To be more specific, unless you are scanning this index very often, which shouldn't be the case, the fragmentation should be dismissible. Fragmentation won't have much effect on seeks at all, which is what a unique index should most often be facilitating IMHO.) –  Aaron Bertrand Apr 26 '13 at 16:12
2  
Well, what kind of query? Does the plan show index scans? Is the I/O (logical reads) way out of whack? Is the data for the index typically in memory anyway? Again, just because fragmentation on a particular index is high does not mean it's causing an actual problem you need to solve. –  Aaron Bertrand Apr 26 '13 at 16:16
1  
Also, I'm not trying to brush this off; this definitely sounds like a support ticket in the making, as this isn't how it should work. But for your particular scenario I'm just suggesting that perhaps you should be ignoring these indexes altogether or just dropping/re-creating them less frequently. If the latter accomplishes the same goal, I'd say go with that until Azure support tells you why the rebuild is not completing. –  Aaron Bertrand Apr 26 '13 at 16:18
1  
Sure thing. Please update us here with anything official you hear back from them. –  Aaron Bertrand Apr 26 '13 at 16:37

1 Answer 1

up vote 4 down vote accepted

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.

share|improve this answer
    
I'm working on a script to combine B) and D) right now (I'd rather have them refreshed just in case they may cause problems for us at some point in the future). I'll also recreate this in a new database for your A) suggestion. Thank you! :-) –  Jaxidian Apr 26 '13 at 16:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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