If several users are simply running <a href=”http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-the-select-statement/”>SELECT</a> statements against the same table and getting locked up then you may need to look at your indexes. They probably aren’t setup correctly.
In addition you may want to change your SELECT statements to use the WITH (NOLOCK) hint. This will prevent the SELECT statements from taking locks on the table. The downside to using the WITH (NOLOCK) hint is that you will be making dirty reads of the table, and may not be getting the most up to date information. However if you are being blocked because another user is running an <a href=”http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-the-update-statement/”>UPDATE </a>or <a href=”http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-the-delete-statement/”>DELETE </a>statement the WITH (NOLOCK) will prevent this.
Start with checking your indexes, and make sure that you aren’t being blocked by someone running data modification statements against the table.
Before using hints which may cause undesirable results, can you explain what you mean by the database is hung? Is the entire server non-responsive and <b>never</b> gets back to a working state again? Or perhaps it is just taking a much longer time to run because of contention? Are you perhaps running out of memory?
You’ll need to provide a lot more details if you want a useful response. At this point, your problem could be caused by a bunch of different things. We can’t really help you troubleshoot effectively without more details unless you just want to toss a bunch of things against the wall and see what sticks.