Database hanged due to concurrence

5 pts.
Tags:
Database performance and tuning
SQL Server 2000
SQL Server database
Windows Server 2003
Hi to all. We have an application running under Windows 2003 Server, database SQL Server 2000, client/server, 60 users updating or consulting data. We did a concurrence test. We asked several users to search for same string at same time and the database got hanged. Will that be a configuration issue? What should we check to solve this situation? Thanks, José.
ASKED: August 8, 2008  5:40 PM
UPDATED: August 11, 2008  4:08 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following