question

+1 - having thought about this I agree, I have never seen an index take longer than 10 minutes unless there have been blocking issues either. I generally schedule index work to occur out of hours to avoid such issues.

I promise you, I have seen indexes that takes a lot longer then 10 minutes without blocking, but the tables have been 400 Gb. I really understand your concern Henrik! :)

How about using TOAD to connect to Oracle which has whatever the equivalent of Sybase's "Lock All Pages" thingy. Any DML statement implicitly begins transaction, so if one dev issues something like **update the_table set col1 = col1 where key_col = 'some_value'** and then goes home forgetting to explicitly type and execute **rollback**, all others are hosed until the next day!!! Issuing create index under this seemingly innocent scenario would take a bit of time :( When I was trying to answer this question: http://ask.sqlservercentral.com/questions/7079/advise-on-deleting-huge-amount-of-rows Henrik gave me an excellent suggestion which proved to be very helpful when trying to sniff into some complex queries to figure the time taken by different steps:

declare @d varchar(19);
set @d = convert(varchar(19), getDate(), 120);
raiserror(@d, 10, 1) with nowait;

waitfor delay '00:00:10';

set @d = convert(varchar(19), getDate(), 120);
raiserror(@d, 10, 1) with nowait;
waitfor delay '00:00:10';
select 'All Done'
go

The above prints activity in **real time**. Substitute raiserror with select or print and one is doomed to wait entire 20 seconds and then get all prints at once :(

@TimothyAWiseman; well, our 7 biggest tables are between 1 and 70 billion rows, taking up a TB each. A covering index can easily take 300 GB. This takes a bit more than 10 minutes to complete. I was thinking more along the line of something like this: SELECT * FROM sys.dm_os_tasks where session_id=68

@Henrik, yes as Hakan also pointed out, with very large databases it is quite possible to have a backup legitimately take more than 10 minutes, especially if you are deliberately creating a covering index which will have several columns. But, if someone relatively new to SQL were asking me how long it takes to make an index, I think it would be safe to say: "It depends on a lot of factors, but for a very general idea they will normally only take a couple of minutes and if it goes over 10 minutes you probably will want to look for blocking processes." It gives them an order of scale so they know it will rarely be instant but also should not take hours (for tables of average size.) Of course, I also would never recommend someone relatively new to SQL add indexes to a table that was several hundred GB large.