THE SQL Server Blog Spot on the Web

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

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

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

Leave a Comment

(required) 
(required) 
Submit

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.

This Blog

Syndication

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