What version of the server are you running?

Does anything show up in the error log when this happens?

What database options are set?

Can you issue a checkpoint, or does that hang...

It would greatly help to have some additional information:

Query execution plans for the listed SQL

DDL for the tables involved and their indexes

The number of rows in each table

When you say "a...

The other thing that may cause this is that the proc may have been compiled and stored with bad query execution plans.  The SQL you execute dynamically will get the...

Constraints depend on the integrity rules and business rules you are trying to enforce.  Just looking at the table structure may provide some information you can use to infer these...