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

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

 RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
 ------------ ------------- ------------- -------------------- --------------
 2000         0             1.547612E+08  0                    1
 2003         0             3.036737E+07  0                    1
 2004         0             1.382094E+07  0                    1
 2005         0             8783906       0                    1
 2006         0             1.923503E+09  0                    1
 2007         0             9.997391E+08  0                    1
 2008         0             1.004515E+09  0                    1
 2009         0             4.943285E+08  0                    1
 2010         0             4.881142E+08  0                    1
 2011         0             129017        0                    1
 2012         0             72572.05      0                    1
 2013         0             40317.8       0                    1
 2014         0             193525.5      0                    1
 2016         0             1.189913E+07  0                    1
 2017         0             9840233       0                    1
 2018         0             1.92027E+09   0                    1
 2019         0             7.596681E+07  0                    1
 2022         0             4.245465E+07  0                    1
 2026         0             8138821       0                    1
 2029         0             8.306543E+07  0                    1
 2030         0             4781692       0                    1
 2035         0             5.50209E+08   0                    1
 2037         0             4531722       0                    1
 2038         0             5800389       0                    1
 2041         0             3066841       0                    1
 2042         0             1.928116E+09  0                    1
 2044         0             2.004276E+09  0                    1
 2046         0             8.009266E+07  0                    1
 2047         0             2.710969E+07  0                    1
 2048         0             3.883142E+07  0                    1
 2049         0             24190.68      0                    1
 2050         0             34942.1       0                    1
 2051         0             24190.68      0                    1
 2052         0             4738686       0                    1
 2053         0             1161153       0                    1
 2054         0             258034        0                    1
 2055         0             1.82233E+09   0                    1
 2056         0             8.831347E+08  0                    1
 2057         0             1.255658E+08  0                    1
 2058         0             5.897689E+07  0                    1
 2059         0             1.137312E+08  0                    1
 2060         0             7.283009E+07  0                    1
 2068         0             510692.2      0                    1
 2077         0             7.079806E+07  0                    1
 2078         0             4.231757E+07  0                    1
 2079         0             3.385083E+07  0                    1
 2080         0             7.803108E+07  0                    1
 2081         0             3.801432E+07  0                    1
 2082         0             5.24669E+07   0                    1
 2084         0             5.630946E+08  0                    1
 2094         0             1.878165E+08  0                    1
 2096         0             1290170       0                    1
 2098         0             1.227731E+08  0                    1
 2099         0             451559.4      0                    1
 2100         0             5.476502E+07  0                    1
 2101         0             7.800958E+07  0                    1
 2102         0             7.763328E+07  0                    1
 2103         0             4.323413E+07  0                    1
 2104         0             4.16698E+07   0                    1
 2106         0             5993914       0                    1
 2107         0             3.04722E+07   0                    1
 2108         0             6461601       0                    1
 2109         0             3.292621E+07  0                    1
 2110         0             7.659308E+07  0                    1
 2111         0             6526109       0                    1
 2112         0             7.956316E+07  0                    1
 2113         0             8711334       0                    1
 2125         0             408553.8      0                    1
 2126         0             292976.1      0                    1
 2128         7.711276E+07  7621.021      1                    7.711276E+07
 2129         0             1166529       0                    1
 2130         0             1142338       0                    1
 2142         0             6.172925E+07  0                    1
 2151         0             1.871499E+09  0                    1
 2152         0             9.0335E+08    0                    1
 2153         0             1290170       0                    1
 2154         0             884303.9      0                    1
 2155         0             2.096257E+07  0                    1
 2156         0             4.986775E+07  0                    1
 2179         0             244594.7      0                    1
 2181         0             161271.2      0                    1
 2184         0             1.246089E+07  0                    1
 2185         0             9006998       0                    1
 2190         0             107514.1      0                    1
 2193         0             4279.893      0                    1
 2194         0             163959.1      0                    1
 2195         0             26878.54      0                    1
 2201         0             131704.8      0                    1
 2203         0             99450.59      0                    1

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.)

avatar image By sam.bishop 54 asked Jul 12 at 07:27 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x24

asked: Jul 12 at 07:27 PM

Seen: 22 times

Last Updated: Jul 17 at 06:35 PM