Investigating high SQL waits, especically LCK_M_U

Locks tend to form chains and you are always interested mostly in what the process at the head of the chain is doing. Simply looking at lock wait times can be misleading because many processes can wait long times (thus increasing the wait times stats) but all be blocked by a single dog-slow process. What you likely see is a coalesce point: because locks granted have to be compatible with all current grantees and all pending waiters whenever a high level lock (X, or SCH_M) gets into the wait queue for a resource all subsequent requests queue up behind this.

To give an example: say you have a query running a report on a table, say it takes 5 mins. It hold an IS lock ont he table. This IS is compatible will all normal operations (reads and writes). A request comes in that wants SCH_M. Is incompatible with the IS so he's put in the queue. Now all of the sudden all other activity on the table goes into the queue, because every requests, reads or wites, are incompatible with this waiter. So all of the sudden all lock times spike. After 5 min the slow query finishes (it 'drains' in the DB jargon), the SCH_M is granted, it does its job in 5 ms and everyone else is free to proceed. This is just an (extreme) example, it doesn't have to be SCH_M. The idea is that just wait times don't tell the full story.

Luckily SQL Server itself can report blocking chains to you via an almost unknown feature called blocked process threshold. Mix in Blocked Process Report and DDL event notifications and you get a fully automated solution.

For an example see my answer at MS SQL Server third party transaction blocking monitor tools.

Another observation: You have 22.30% wait times on IX. This implies a object level lock occurred. Intent locks (IS, IX) are compatible so the fact that IX was blocked suggests that someone acquired an incompatible object level lock. A scan acquiring an S, or a big update escalating to X.

Collecting Data from sp_WhoIsActive in a Table is a good technique for tracking down blocking and locking issues.

The @get_locks parameter can be used if you want to see the finer detail of the locks involved. Alternatively, @get_task_info and @get_additional_info will typically capture enough to identify the cause.

If the output gathered isn't clear enough to understand the problem, feel free to append to your question.