We have a problem on a live server where web users experience occasional unpredictable "stalls". There are a few links in the chain where the problem could be, but based on some clues in logs, I suspect that it's the last link at the bottom of the chain ... SQL Server that's the source of the problem.

But I need evidence. Is there some feature of SQL Server or perhaps some tool that can detect queries that are suspiciously long running? This is SQL Server full standard edition. I haven't had to poke deeply into SQL Server's machinery before, so I'm in unfamiliar territory.

Cheers,