Multiple instances of the same query with OPTION (RECOMPILE) results in execution plans with wildly different row estimates

sam.bishop 2017-07-12 19:27:47

EDIT: I haven't solved this issue, but I have a way around it by using a different join type. This question can be considered closed.

I have a stored procedure that builds a dynamic SQL query and executes it. That SQL always includes OPTION (RECOMPILE). The resulting execution plan includes a Hash Match that feeds into a Sort. Sometimes SQL Server estimates that one row will be returned by the Hash Match, and sometimes it estimates something reasonable. (Which is a few orders of magnitude more than one!) Of course, when it estimates one row, the Sort is highly likely to spill.

I don't understand why this happens or how to prevent it. The attached Plan Explorer session has two examples of the good and bad scenarios. The inputs to the stored procedure are identical each time. I believe that the plan shapes are also identical. I have noticed that I seem to get a good plan after running DBCC FREEPROCCACHE. But it doesn't seem like that should matter given the OPTION (RECOMPILE).

My suspicion has been that it has something to do with automatically generated statistics on the balanceId column, and that SQL Server is using those statistics in one case and not in the other. Here's what I get from DBCC SHOW_STATISTICS:

Name                                                                                                                             Updated              Rows                 Rows Sampled         Steps  Density       Average key length String Index Filter Expression                                                                                                                                                                                                                                                Unfiltered Rows
-------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- ------ ------------- ------------------ ------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------
_WA_Sys_00000008_5D0CA0AC                                                                                                        Jul 11 2017  4:30PM  19745725474          7317594              89     0.3333333     4                  NO           NULL                                                                                                                                                                                                                                                             19745725474
All density   Average Length Columns
------------- -------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0.01111111    4              balanceId
------------ ------------- ------------- -------------------- --------------

Note that the "Updated" timestamp is almost 24 hours old, so the statistics haven't changed during my testing. (I'm working on a relatively idle development server.)