Yes, but will either need a cursor or a hard-coded limit on how deep the hierarchy will go.

Which is your preference?

Got connection pooling in place? That would account for the remaining connections. Reusing an open connection is faster than opening a new.

As for the slow down, there could be hundreds...

A connection will stay open until it's explicitly closed. SQL doesn't close them automatically. If they're awaiting command, then they are idle.

There's a column in sysprocesses waittime and another lastwaittype....

Are you using SQL 2000 or 2005? It's very easy on 2005 but on 2000 it can be quite tricky.

How deep can the hierarchy go?

Could you post your table structure and some sample data please? It's a little difficult to write a working query without them.

What's the wait time on those writelog waits?

Is the log on a dedicated drive?

A million rows is very small. Nonclustered indexes should complete in under 5 min.

While the index is being built, the table will be read-only, but there's no need to...

The disappearing essages is related to session timeout. I've had similar issues on other forums too.  I normally just Ctrl-A, Ctrl-C before hitting post.

That will probably be fine then. Just wanted to be sure that Field2 wasn't a char(500) or something like that.

What datatypes are Field1 and Field2? If 2 is a wide type then it's better if it isn't in the cluster. The wider a clustered index key, the wider all...

Run DBCC CheckDB to see if you have any data corruption.

Since you say the users connect as SA, are you sure someone didn't accidentally drop the tables? That's the most...

Thanks. Forgot about that one.

I know there was a discussion panel at PASS last year regarding new certifications. I got an invite but didn't attend.

Sounds interesting.

If you're planning to do cross database queries, make sure you're not using application roles.

Also watch for broken ownership chains

Wow. This looks really good.

Vry good timing too, seeing as I'm writing 441 on Friday.