Missing Optimality Based Recompile on cached temporary table with clustered PK? - by Martin Smith

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 725697 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 2/20/2012 3:35:39 AM
Access Restriction Public


Repro shows a situation where a stored procedure with a #temp table first gets a plan compiled for the case where the  #temp table has 10 rows. It is then invoked a second time and the #temp table cardinality is 990,000.

Despite the massive increase in number of rows it gets no optimality based recompile and re-uses the nested loops plan from the first invocation rather than the merge join it would use if recompiled. If the primary key is removed from the table definition I do see statistics based recompiles then. 

Reversing the order of the procedure calls so it is first ran with cardinality of 990,000 and then with cardinality 10 does lead to a statistics based recompile for both the heap and the clustered case.
Sign in to post a comment.