Return to Question

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

UPDATE: I guess it must have been the parameters after all- when I ran the ad-hoc code with the variable hardcoded, I can get the "bad" plan with the right value (it is a date, values that are a year old seem to generate the "good" plan). Now on to trying to force the "good" plan on the proc by using a query hint.

SOLUTION: I ended up getting the plan I wanted by using the OPTIMIZE FOR UNKNOWN hint.

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

UPDATE: I guess it must have been the parameters after all- when I ran the ad-hoc code with the variable hardcoded, I can get the "bad" plan with the right value (it is a date, values that are a year old seem to generate the "good" plan). Now on to trying to force the "good" plan on the proc by using a query hint.

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

UPDATE: I guess it must have been the parameters after all- when I ran the ad-hoc code with the variable hardcoded, I can get the "bad" plan with the right value (it is a date, values that are a year old seem to generate the "good" plan). Now on to trying to force the "good" plan on the proc by using a query hint.

SOLUTION: I ended up getting the plan I wanted by using the OPTIMIZE FOR UNKNOWN hint.

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

UPDATE: I guess it must have been the parameters after all- when I ran the ad-hoc code with the variable hardcoded, I can get the "bad" plan with the right value (it is a date, values that are a year old seem to generate the "good" plan). Now on to trying to force the "good" plan on the proc by using a query hint.