THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search

Adam Machanic

Adam Machanic, Boston-based independent database consultant, writer, and speaker, shares his experiences with programming, performance tuning, and optimizing SQL Server 2000, 2005, and 2008, in conjunction with related technologies such as .NET.

Demos - TechEd DAT305 - Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server

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).

Published Wednesday, May 13, 2009 8:46 PM by Adam Machanic

Attachment(s): TechEd09_DAT305_SQL_Exceptions_Depmos.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Vadivel said:

Adam,

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*/ ?

Regards

Vadivel

May 14, 2009 3:24 AM
 

Vadivel said:

pls ignore my typos .... I guess its too late and i need to hit the bed right away :)

May 14, 2009 3:38 AM
 

Adam Machanic said:

Hi Vadivel,

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
 

DBA & Developer Notes « 36 Chambers – The Legendary Journeys: Execution to the max! said:

May 20, 2009 2:14 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based independent database consultant, writer, and speaker. He has been involved in dozens of SQL Server implementations for both high-availability OLTP and large-scale data warehouse applications, and has optimized data access layer performance for several data-intensive applications. 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 "Expert SQL Server 2005 Development" (Apress, 2007) and "Inside SQL Server 2005: Query Tuning and Optimization" (Microsoft Press, 2007). Adam regularly speaks at user groups, community events, and conferences on a variety of SQL Server and .NET-related topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and a member of the INETA North American Speakers Bureau.

This Blog

Syndication

News

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement