I have a stored procedure that executes one of two queries based on a value passed into it. Will I get better performance if I break the procedure into separate procedures? As I understand it, SQL Server determines a query execution plan based on the values specified the first time the query is run, so splitting up the stored procedure might help performance. But I'm not sure whether this behavior pertains to IF statements. Listing 1 shows an example of the current procedure, and Listing 2 shows an example of breaking the code into three separate procedures.

If SQL Server selects different query plans based on the different branches in the stored procedure, you're better off splitting the queries into separate stored procedures. Because SQL Server allows only one execution plan per stored procedure, leaving the queries in the same procedure will lead to stored procedure recompilation, which can reduce concurrency and increase CPU utilization.

For example, if you have multiple concurrent sessions executing the same stored procedure, your goal is to use the same execution plan. However, if the procedure's different branches cause SQL Server to recompile the execution plan, the system will have to synchronize between the sessions trying to get access to the compiled plan. Repeated recompilation of a stored procedure's execution plan is CPU intensive, so you'll also see increased CPU utilization.

The downside of breaking the queries into separate stored procedures is that you'll require more memory for the procedure cache because you'll need to hold extra plans in memory—in this case, three instead of one. To figure out how much memory the extra query plans will need, you can query the syscacheobjects system table in the master database. The result's sql column contains the query, and the sqlbytes column contains the size of the plan entry. The usecounts column shows whether or not you have plan reuse.

—Gert Drapers
Software Architect,
SQL Server Development Team