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.