Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hiuhiu
Starting Member
18 Posts |
 Posted - 2004-07-27 : 06:11:52
|
| Hi,I have an application written in Visual Basic 6 and connect to SQL Server 2000. It works fine until I have about 100 concurrent users accessing the same SQL Server Database. The application raised Timeout from time to time because it waited for too long for the resources that it needed.I tried to reduce some unnecessary locks by using WITH NOLOCK in many SELECT statements.When I tried to monitor the SQL Server Lock Events (Lock:Cancel, Lock:Deadlock, Lock:Deadlock Chain, and Lock:Timeout) using SQL Profiler, sometimes I got more than 100 rows appeared on the trace window with EventClass = Lock:Timeout. All 100+ rows refered to the same user with StartTime almost the same. But when I asked that user if he experienced any slowdown or timeout in the application, he said "NO".My problem is, exactly how to interpret this Lock EventClass?Thanks in advance.HIU |
|
|
hiuhiu
Starting Member
18 Posts |
Posted - 2004-07-27 : 20:40:29
|
| Anyone has any idea or I should post my problem in another section of the forum?Thanks.HIU |
 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-28 : 08:38:56
|
| You need to run Profiler at the same time to capture SP:Completed and RPC:Completed (if you have dynamic SQL, you'll need to run statement completed which is a pain). Analyze the SPIDs that caused the deadlocks and redesign the queries. SQL Server can support way over 100 concurrent users needless to say. Remember that the longer any one piece takes to run, the more the deadlock possibilities increase. They increase exponentially. So, if you can identify a couple long running procs that cause this, you can fix the issue.You need to focus on the object id indicated in your deadlock chain.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Caz
Starting Member
3 Posts |
Posted - 2004-07-29 : 11:29:37
|
What type of locks are they? Page or Row locks. If they are page locks what is the fill factor on your indexes? SQL Server should default to taking row locks unless your database design prevents it. Are your row sizes going to be bigger than the 8k page size? This can happen if you use large varchar fields, large text fields etc... which can cause a page lock rather than a row lock to be taken thus causing more lock contention. RegardsCazRegards |
|
|
|
|
|
|
|
|
Problem with Locks