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 |
|
dineshrajan_it
Posting Yak Master
217 Posts |
 Posted - 2008-04-23 : 01:40:49
|
| Hicould anyone pls tell me how to avoid deadlocks during as iam stuck up with this problem during large no of hits to server.Iam a slow walker but i never walk back |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2008-04-23 : 12:50:41
|
| There's a number of things you can do to reduce the frequency of, or completely eliminate deadlocks.Optimal code is the most obvious. Make sure the queries are written in the best way possibleOptimal indexes - make sure your indexes are appropriate for the queries you are runningOrder of access - Access objects in the same order. If in one proc you access table1 first and table2 second, use the same order in other procs.I know that's vague, but without seeing the specifics of your problem it's hard to be specific. If you would like specific help, post the code that's involved in the deadlocks. Also of great use is the deadlock graph (turn traceflag 1204 or 1222 on and SQL will write info on the deadlock into the error log). You can use the deadlock graph to get a precise view of exactly what's deadlocking where.Hope that helps a bit.--Gail Shaw |
 |
|
|
WyldOne
Starting Member
4 Posts |
Posted - 2008-04-23 : 23:44:33
|
| Couple of hints.Try to keep sets of data small using the where clause. The smaller and more precise the dataset the less likely a lock will escalate to a page and or worse a full table lock. It helps to have data you can put unique keys on for indexes.Remember that sub-selects executes for every row specified in each where clause.Make use of the 'show execution plan' in the query tool to find out if your queries are optimal, and if your indexes are being used. Note that a full table scan can be very bad. Sometimes a index you create will seem correct, but the optimizer ignores it.Make small temp table of similar data and index them. yes you can put an index on a temp table.Watch for 'or' conditions in your where clause. Sometimes it is better to separate two types of data and merge them later using a temp table.Break up unions. If you have to have one result set, grab each section separately and stuff in a temp table. Then select from temp table for final results, and sort if needed.Use the 'dirty read' optimizer hints for 'non blocking' reads where it does not matter if you are looking at the most current data, and have no need to update it.In some cases a temp talbe with an index are much faster than a large static 'in' clause (eg where X in ('b', 'c', 'sdfas', etc...))Try different ways to do the same thing. Sometimes a change in the method results in a huge change in the performance.Now deadlocks are a part of life, but you can reduce the number and scope of the ones you do get. |
|
|
|
|
|
|
|
|
Dead locks