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 |
|
ssrikrish
Yak Posting Veteran
69 Posts |
 Posted - 2006-09-04 : 15:49:43
|
| There have been intermittent timeout errors in our application on a couple of databases. There is a particular long running proc that we suspect that deals with the orders and orderdetails transaction table. The orders table has around 700,000 rows and the orderdetails table has around million rows etc. The Orders and OrderDetails tables have been indexed and it seems INDEX SEEK is being used on the joins etc. Note that the Orders are occuring at the same time when the table is read etc. 3 or 4 deadlocks have also occured in the past 2 weeks. I also looked at the deadlock chain in the SQL server error log. The proc i am referring to is one of them. Any ideas on how to optimize this proc to prevent locking/timeouts and dealocks? |
|
|
Wanderer
Master Smack Fu Yak Hacker
1168 Posts |
Posted - 2006-09-05 : 04:52:04
|
| It will be difficult to suggest anything without the actual SP's themselves.Areas to look at might be whether you are locking correctly - for example, if you select data from table A, do some processing of it, and then update table A, it might be worthwhile investigating whether doing a UPDLOCK on table A, or a ROWLOCK, might help...*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-09-05 : 11:05:48
|
| Add this to the serverhttp://www.nigelrivett.net/SQLAdmin/sp_nrInfo.htmlIt will allow you ro see what is running for a long time and what it is doing and if anything is blocking.When you have decided that use the profiler to see what that sp is doing so you can see what needs optimising.==========================================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. |
|
|
|
|
|
|
Deadlocks/Timeout