SQL Server Error Checks – Do They Affect Query Performance?

error handlingexceptionperformancequery-performancesql serversql-server-2016

If I have a stored procedure with a handful of error checks that come before the queries are executed and throw exceptions as necessary, could this affect performance? Is it bad practice to do this?

For example, say I had 10 logical case checks against the parameters of this stored procedure, would that be fine?…What if I had 100 check cases?

(The functional programmer in me wants to not leave any holes, but the relational developer in me feels like this could lead to a bad query plan being generated.)

Example of how I'm doing the error checking:

IF @Parameter1 IS NULL OR @Parameter2 IS NULL OR @Parameter3 IS NULL
BEGIN
    THROW 50000, 'Error Message 1', 1;
END
ELSE IF @Parameter1 = 'Value1' AND @Parameter2 <> @Parameter3
BEGIN
    THROW 50001, 'Error Message 2', 1;
END

Best Answer

I'm hard-pressed to see how an IF statement before the "meat" of the procedure that is just doing simple tests on the parameters would produce a bad plan.

Unless something really odd is going on, I wouldn't expect those checks to have a meaningful impact on performance unless the actual work the procedure is doing is very minimal. Even a few hundred equality/ inequality checks should take way less than a millisecond which is almost certainly too small to be meaningful to you.

If there were 100 checks, that would make me suspect that something was off in the procedure and that it was trying to do too much/ take too many parameters and that it was in desperate need of refactoring into multiple smaller procedures.

The one caveat would be if this was a table-valued function (TVF) rather than a procedure. I would absolutely avoid adding these sorts of checks if that would transform a single-statement TVF into a multi-statement TVF because that's going to prevent the optimizer from merging the query into the rest of the statement that it is being called from. If you have a multi-statement TVF, that logic is potentially getting called many thousands of times in a single SQL statement in which case you might start to see some performance impact. Even there, though, if your checks are only 1% of the overall runtime of the object, you probably don't have too much to optimize for.

Related Question