A huge thank you to everyone who attended today's session on exception handling! I had a great time and got some very good, on-point questions from the audience. As promised, the demos are attached to this blog post. Please let me know in the comments if you have any questions (whether or not you attended--everyone is allowed to join the party).

Comment Notification

Comments

Is there few ways by which we can avoid deadlocking on your SQL Server. Like, ensuring that our DB design is properly normalized, application access server objects in the same order each time etc., etc.,

That being the case do you really you need to promote a code block like /*Put deadlock-prone code here*/ ?

Avoiding deadlocks altogether is probably not possible, but it depends on the nature of your system. For example, data warehouses that do only nightly loads are unlikely to suffer from deadlocking issues, whereas active OLTP systems can be extremely deadlock prone. In SQL Server 2005/2008, the SNAPSHOT isolation levels can help a lot with many deadlock cases. You might also want to look at some of Alex Kuznetsov's posts on the topic.

I'm not sure what you mean about "promoting" the code block. Were you in the session at TechEd? That particular sample shows how to deal with deadlocks as a temporary stopgap while debugging. Some deadlocks can take quite a while to get to the bottom of and it's better to keep the system running if possible. That example is not intended to be a permanent fix or something that you would use without very good reason.

May 18, 2009 12:00 PM

Leave a Comment

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.