After some hangs on our public web application, it was recommended by a friend that I use Profiler in order to view db activity from each user (including sprocs and duration). Is there a more widely used database server profiler that I could use in order to better pinpoint issues like this?
I've never had the need to use anything other than SQL Server Profiler. It's included specifically for purposes such as you mention. You can see everything you need to see with Profiler. Have you run a trace against your server yet? There are quite a few options that you'll need to configure for your trace in order to narrow down the problem. It won't necessarily happen on the first trace, either. It's likely you'll have to do some tweaking of your trace config and rerun a few times in order to see the information you need.