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.

I've tried a bit with SELECT * from sys.dm_os_tasks, but to no avail. I had hoped that I could see how much of the table that SQL had read, and how much had been written out as "sorted, ok", and maybe also how much of the index that had been written (just to the nearest 100GB).
I would like to be able to tell my customers that the index will be finished in xx hours, and I'm no good at keeping track of how long it took the last time I did a similar index.

Your best guideline will indeed be how long a similar job took the past, so if this is a common question for you, then you would probably be well served by logging that information somewhere you can refer to it easily. With that said, even that is just a very rough estimate because there are so many factors. There could be higher server usage now, locking processes that weren't there last time, the table could be slightly different, etc, etc, etc.