Development Q13: How can I optimize the query plans for my stored procedures accepting parameters, thus achieving optimal performance?
The problem is the query-plans, the pre-compilation of stored procedures, that SQL Server does for you. As you know, one of the biggest reasons to use stored procedures instead of ad-hoc queries is the performance gained by using them. The problem that arises with the above tip is that SQL Server will only generate a query-plan for the path taken through your stored procedure when you first call it, not all possible paths. Let me illustrate this with an example. Consider the following procedure (pre-compilation doesn’t really have a huge effect on the queries used here, but these are just for illustration purposes): CREATE PROCEDURE dbo.spTest (@query bit) AS IF @query = 0 SELECT * FROM authors ELSE SELECT * FROM publishers GO Suppose I make my first call to this procedure with the @query parameter set to 0. The query-plan that SQL Server will generate will be optimized for the first query (“SELECT * FROM authors”), because the path followed on the first call will result in that query
