Yesterday was a very interesting day, trying to tune a procedure that uses a schema binding view, I try to add a needed unique clustered index into the view, so I can utilize the noexpand hint to used the actual indexes from the table, the result a total cluster ckfu. First there is not online support to build indexes on a view so the table got lock when adding the unique clustered index block the table, which cause everyone to jump in panic, while I was unaware of the situation as a let the script execute in a reduce script while working on another task. So lesson learns, monitor the execution of the script, if blocking way until a later time (maintenance window), the indexes will improved the execution to a 45% faster response time, but, careful planning should be in place, sometimes we get wrap into fixing and not realizing that everyone won't remember what you did good, but that one day when everything went to hell.
cheers.
