Multiple instances of the same query with OPTION (RECOMPILE) results in execution plans with wildly different row estimates
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.)