June 4, 2015 at 6:25 pm
How we can avoid blocking and deadlock?
June 5, 2015 at 12:56 am
Here are some basic guidelines
1)Use clustered indexes on high-usage tables.
2)Avoid high row count SQL statements that can cause a table lock. For example, instead of inserting all rows from one table to another all at once, put a single INSERT statement in a loop and insert one row at a time.
3)Break long transactions up into many shorter transactions.
4)Make sure that UPDATE and DELETE statements use an existing index. Add missing indexes.
5)If you use nested transactions, be sure there are no commit or rollback conflicts.
6)You can use NOLOCK or READPAST, if possible
June 5, 2015 at 9:48 am
You can't avoid blocking, and you don't want to. It is an integral part of SQL's engine to maintain data integrity.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 5, 2015 at 9:53 am
Write queries efficiently, make sure that there are supporting indexes. That's mostly what it comes down to.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply