Database: The TRY... CATCH Penalty

Is there a performance penalty for using TRY... CATCH? The short answer is "not really."

The detailed answer involves a quick comparison test. I used set @variable = @variable + 1 within a TRY... CATCH and again without a TRY... CATCH. I did something trivial like this so I would only be measuring the impact of the TRY... CATCH block.

Each pass looped 1,000,000 times so, we need to divide our resultant times by one million to see the performance effect on each individual query. I did 5 runs of 1,000,000 with the TRY... CATCH and 5 without, so we'll do some averging. Here are the raw times in milliseconds:

TRY NON-TRY

8093 2483

7733 2470

8220 2376

7626 2483

7936 2530

7931.6 2468.4

So, for the TRY... CATCH, we averaged 7.932 seconds for one million executions. For the NON-TRY... CATCH version, we averaged 2.468 seconds for one million executions. If we divide both numbers by one million then take the difference, we get the performance impact of using TRY... CATCH. In this case, it works out to about a 0.000005464 second penalty per query (about 5 one-millionths of a second).

Those are the SQL Server 2005 numbers. The penalty and corresponding execution times are even smaller in SQL Server 2008.

Here are the test scripts I used:

------ WITH THE TRY-CATCH BLOCK

declare @i int

set @i = 0

declare @startTime datetime, @endTime datetime

set @starttime = getDate()

while @i < 1000000

begin

begin try

set @i = @i + 1

end try

begin catch

-- nothing

end catch

end

set @endTime = getdate()

print cast(datediff(ms, @starttime, @endtime) as varchar) + ' ms'

go 5

------ WITHOUT THE TRY-CATCH BLOCK