SQL Sam and the Evil Twin - Solution!
SQL Sam knew that the query optimizer generates a query plan for a stored procedure the first time it
is executed- and then sticks with it.
Thus, if the parameters passed in are not typical of most uses of the procedure, the query plan chosen may not
be optimal. For example, with one set of parameters, the optimizer may decide that a table scan is the way to go.
In another case, a clustered index might be a better choice.
In Fred's case, it's even worse. Fred essentially combined two unrelated queries into one procedure. The optimizer
generates the query plan based on the first execution - the one Fred did with the product type as 'BUSINESS'. The
optimizer made a query plan for each SELECT statement, based on the value of ARG1 and the index distribution statistics.
The optimizer couldn't make use of an index on color for the SELECT statement that handles @TYPE='HOUSE' because the
value of ARG1 was a price the first time the procedure was called. So, it opted to use a table scan for any time it had to do a HOUSE
product lookup. All subsequent users of the procedure paid the price for Fred's cleverness.
Sam suggested that the procedure be split into three separate procedures, with the main one calling either a HOUSE product
lookup procedure or a BUSINESS lookup procedure. Sam also mentioned that Fred should look into the WITH RECOMPILE option.
If the values that Fred uses in stored procedures are widely varying, and could result in different query plans, the
WITH RECOMPILE option could save time by insuring that the optimizer picks a plan that is most appropriate for the current
data.
Go read more exciting SQL Sam Cases!
See the story behind the story in Behind the SQL: The Making of SQL Sam!
» 
Comment and Contribute