I don't know anyone that likes to wait for an application to respond. This is especially a concern with web applications; you start to wonder if the web infrastructure has a problem. Should you hit by "Order" button again?

Because waiting is subjective it's nice to be able to know, when a user calls, how long they have actually been waiting. Or at least how long the database has been waiting. SQL Server 2005 will tell you, at least when locking is involved.

There is a configuration parameter "blocked process threshold" that controls if and when to send notifications whenever a process is blocked for N seconds. You set this through sp_configure; its an advanced option so show advanced options must be on too. It's set to 0 (never notify) by default. You can catch these notifications through WMI/SQLAgent alerts or the new event notifications. Very cool.

So now when a user reports "I've been waiting for this database query for 10 minutes" but the query response is immediate when you check it (the "works for me" answer that users hate), you can see (after the fact) if its lock contention that caused the waiting. “Well, you weren't waiting for locks” might be a better answer. Maybe.