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 |
|
whatwrong8
Starting Member
1 Post |
 Posted - 2004-11-15 : 20:05:09
|
| Hi, we have an application and recently one application connection using connection pool always blocks another spid (bound connectin to sql server). I have been turning on sql profiler since but could not figure out why. This always happens every day. We also have open connection from application server but each time it is a different connection process so it is a moving target and hard to catch. Could anyone please give some clue how to solve the sql db blocking problem. Thanks in advance.Anli DuAnli Du |
|
|
igbinosun
Starting Member
13 Posts |
Posted - 2004-11-15 : 20:26:01
|
| Reduce maximum workers thread. Default is 255. And ensure your applications concurrency control is optimistic. This depends on what you're doing too. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-11-15 : 20:45:15
|
| Changing the max worker threads will do nothing to alleviate blocking. Neither will setting optimistic concurrency, which is also dependent on which data access library being used and may not be applicable, and can be overridden by both the SQL statement(s) being executed and the query optimizer.It would help if you could post some code or at least a description of what the blocked process(es) is trying to do. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-15 : 20:56:37
|
| Try thishttp://www.nigelrivett.net/sp_nrinfo.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
database blocking