If you have many try/catch blocks in your .NET code and your code execution actually passes through them, you should expect a performance hit. That’s intuitive and no surprise.
What is surprising is the extent to which you may experience severe performance degradation when multiple users are executing a piece of SQLCLR code (e.g. calling a SQLCLR table valued function) that contains many try/catch blocks. In other words, there may significant and adverse concurrency implication if you use too many try/catch blocks in your SQLCLR code.
Here is an example to prove it!
I have implemented two SQLCLR table-valued functions: tvf_GetDataWithTryCatch() and tvf_GetDataWithoutTryCatch(). These two functions return exactly the same resultset: 2000 rows with 100 columns of integers. The functions simulate the case where the integer values are out of bound (i.e. greater than the maximum value allowed for the integer datatype, which is SqlInt32.MaxValue in the attached C# code), and when that happens, the functions return NULL instead.
There are many ways to check whether a value is equal to or greater than SqlInt32.MaxValue. As the name implies, tvf_GetDataWithTryCatch() implements the check in a try block and returns NULL in the corresponding catch block. The tvf_GetDataWithoutTryCatch() function, on the other hand, implements the check with a C# conditional operator (?:).
When there is no background concurrent load
The tvf_GetDataWith TryCatch() function is expected to be slower than the tvf_GetDataWithoutTryCatch() function because of the inherent overhead with a try/catch block, and tests show that is indeed the case. On my rather antiquated PC with 2GB of physical memory, the following query
select * from dbo.[tvf_GetDataWithTryCatch]()
returns all the 2000 rows in about 8 seconds, whereas this query
select * from [dbo].[tvf_GetDataWithoutTryCatch]()
returns in about 1 second. Consistently, the function doing 200,000 try/catch’es is about eight times slower than the function doing 200,000 conditional operations. So far so good! No surprises!
When there is background concurrent load
When I have 10 users running tvf_GetDataWithoutTryCatch() nonstop against the same SQL Server 2008 instance in the background, the tvf_GetDataWithoutTryCatch() returns in about 2 seconds consistently. So running additional load of tvf_GetDataWithoutTryCatch() does slow it down a bit, but not a lot.
Now, when I have 10 users running tvf_GetDataWithTryCatch() nonstop in the background against the same SQL Server 2008 instance, the performance of tvf_GetDataWithTryCatch() degrades dramatically. This time it takes about 76 seconds to return all the 2000 rows. That is about 10 times slower than it is when there is no concurrent load. And it is about 38 times slower than tvf_GetDataWithoutTryCatch() under the same 10-user background concurrent load. Note that tvf_GetDataWithoutTryCatch() returns in about 2 seconds regardless whether the 10-user background load is calling tvf_GetDataWithTryCatch() or calling tvf_GetDataWithoutTryCatch().
If you think 38 times slower is bad, in real production environments I have seen far worse concrrency impact as a result of too many try/catch blocks.
With the attached C# code, you can run the tests yourself to see the behavior. Your results may differ in some details, but you should see a similar pattern. If not, post back a comment and let us know.