Try Catch - Resource intensity?

Question

I've read that using Try Catch blocks in SQL Server stored procedures can chow a lot of server resources. My question is just how much resources does it use?

I currently make use of Try Catch blocks and Transactions whenever my sproc is making a lot of data changes, which is great for preventing incorrect data from entering the db and also for logging the errors, but I'd like to make use of this method of programming in all my sprocs.

How much of a difference does it make?

Answer 1

Zero. Code using TRY/CATCH consumes exactly the same resources as code not using TRY/CATCH, only difference is that the former is usually more correct than the later. In fact TRY/CATCH code is more efficient in presence of errors as the code flow jumps straight to the catch block and avoids running the rest of the statements in the request/stored procedure only to roll back at the end.

Just for the record, I don't buy for a single second that writing code that checks for @@ERROR after each statement is even remotely a viable alternative.

I've read that in a book back when I was in university

For sure the reading did not refer to T-SQL code with TRY/CATCH, but instead was referring C++ code with or without exception (of JVM, or IL). Back in the dark ages there was a dispute about whether adding the exception handling code has performance impact (yes, it has) and whether we should consider this a factor (no, we shouldn't, code with exception handling has long since won that battle due to correctness). But this discussion is completely useless for your point of view: the back end engine that runs your T-SQL is compiled with exception handling and there is nothing you can do about it. Again, this has 0 impact on your T-SQL code.