Yes,  SQL Profiler is the right tool to use next.

The easiest way to use is start with the default options and add bits and pieces to it until you get the effect that you want.  Then set run the trace for a while.  If you get a lot of data or you want to keep it, you cans save it yo a table for later analysis.

Start a new trace,

Connect to the server

Go to Events Tab

The default events as a good starting point:

  Audit Logon

  Audit Logoff

  ExistingConnections

  RPC:Complete

  TSQL:BatchComplete

Run the trace and look for transactions from your app so you can filter it.

Once you figure out how to identify the transactions from your app, probably by user ID or application name then you can stop the trace,  and use File-> properties to edit the values.  Use the filter tab to set user id or whatever to teh apprpopriate value and restart the trace.

Next,  look at the details of the trnasaction in the lower window.  From your description,  you should now be able to see the stetements in full.  If your app is using stored procedures (it doesn't sound like it is),  you will only see the EXEC statements which are useful but not very informative.  You can get more details by adding a couple more events so stop the trace again and go to the events tab. then add:

  SP:StmtCompleted

  TSQL:StmtCompleted

  Transaction:SQLTransaction

will probably be usefull too.

-------------

I wonder if this is the 'classic' insert contention problem.  If several users are all trying to insert to the same table, and if the table has a clustered index, and if the index values of the new records are similar as is the case with an incrementing identity column,  then you can get blocking which would fit your description.

It is caused because SQL must lock the page and indexes which are being update with the new record for the duration of the transaction and this blocks the next user.

The solution  is to remove the clustered index from the identity column and replace it with an non-clustered index.  This should not be undertaken lightly though as the performance could plummit.

It could be any number of other problems too.  We will know more once you get the trace from profiler.