Why does changing an account name field change the execution plan so much?

CIS 2013-07-26 21:21:42

I have a field called T1.acct. This can be changed to reflect many different accounts in the table. When using one account name in particuliar it takes about 5 minutes to run, all other account names take 1 – 2 seconds. The rows returned are about the same for all accounts.

The query plan changes quite a bit with this one account name which is the attached plan. When using all other account names the plan changes to use a hash match and doesn't seem to do the RID lookups.

I'm not exactly sure how this plan can vary so much? The plan doesn't suggest any missing indexes, though it could probably stand a clustered index. Any ideas?

Jonathan Kehayias 2013-07-26 21:26:43
Look at the Actual Rows vs Estimated Rows on the RID Lookup here, it is 3947 actual vs 7 estimated. You have a cardinality issue or skewed statistics affecting this plan. If you go to the Plan Tree tab in PE, you will also see that the estimated and actual data amounts are significantly skewed as well.
CIS 2013-07-26 21:34:40
So perhaps updating the stats may help? Thank you.
Jonathan Kehayias 2013-07-26 21:39:13
Not in every case, and that's not a permanent solution unless you don't already have maintenance jobs in place to take care of stale statistics. Before updating the statistics I'd look at the information with DBCC SHOW_STATISTICS to look at the sample rate, density, and histogram information around this account name. It could be that you just have a skewed data set that causes problems for the step that the account name exists in due to the statistics step reduction during calculations.
swasheck 2013-08-13 16:39:38
I'm still working on learning how to read these anonymized plans but based on what I think I see, you don't have a clustered index on [Table1] or [Table2]. It looks like you're performing quite a few reads to do a RID lookup. Consider creating a clustered index on [Table1] and covering [Column1] and [Column2] in an index.

I'd also consider doing this to remove the RID lookup on [Table2] columns.