Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 AlwaysOn ReadOnly Statistics Issue

Author  Topic 

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2014-01-16 : 15:51:37
I'll provide more background for SQLTeam soon, but here's the histogram data for the Twitter question I asked:

Read-write replica:

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
10036025     0             2317.11       0                    1
10161736     0             27704.57      0                    1
10685820     0             3.787215E+07  0                    1
17425392     0             46644.43      0                    1
20650966     0             1834647       0                    1
37481118     372126.8      2968520       5                    74425.37
38999786     0             6398649       0                    1
41263345     0             1102441       0                    1
45407977     752472.3      664909.8      1                    752472.3
45682424     0             8160.256      0                    1
52298624     0             637507.4      0                    1
67956212     644485.8      492839.2      4                    161121.5
70014912     0             1046326       0                    1
78290075     552312.4      1501890       3                    184104.1
91908681     337587.8      5838009       4                    84396.95
102933497    561467.3      285004.5      7                    80209.62
114647353    603496.7      614638.6      3                    201165.6
117280928    776816.1      2045303       2                    388408
123747310    244686.1      841715.3      4                    61171.54
133166405    623471.1      470071.1      2                    311735.6
143605335    604120.9      3469016       2                    302060.5
146292774    0             4832887       0                    1
149776493    442557.3      2420372       4                    110639.3
156886357    83226.58      1570698       3                    27742.19
188578945    371814.7      306865.9      9                    41312.75
208460044    555537.4      3922061       5                    111107.5
213385470    765996.6      575751.4      2                    382998.3
213601755    0             13701.17      0                    1
217867075    0             1425426       0                    1
228674264    288588.2      3894558       4                    72147.04
241657619    683394.3      440452.3      6                    113899
245682371    669765.9      786910.6      1                    669765.9
302952241    710859        229494.6      12                   59238.25
308457016    314804.5      292358.8      4                    78701.13
317994472    422582.9      1476402       4                    105645.7
340526340    764956.3      9650259       10                   76495.63
341444485    0             1368606       0                    1
347781020    27048.64      429169        3                    9016.213
363381350    160003.1      584616.9      5                    32000.62
364058765    0             2971240       0                    1
369425321    395950.4      699263.4      1                    395950.4
392573476    511011.2      445691        3                    170337.1
423991888    773695.1      2885205       10                   77369.51
424249165    0             16018.28      0                    1
426172468    0             736538.7      0                    1
439949065    481049.6      126030.6      4                    120262.4
442051581    638764        803634.1      1                    638764
457486429    848390.9      410833.7      9                    94265.66
476638390    537851.8      948604.6      6                    89641.96
479572926    0             882415.9      0                    1
492057913    667061        644257.3      2                    333530.5
494883772    60755.4       572225.4      2                    30377.7
511076843    596526.5      284500.8      5                    119305.3
516638811    450567.9      660275.6      4                    112642
519159343    98415.43      1285089       3                    32805.14
520900373    0             2416645       0                    1
549251757    154801.4      444784.3      6                    25800.24
554141028    194646.2      597209.9      1                    194646.2
560742119    372647        1486879       3                    124215.7
577790987    520790.3      849875.6      6                    86798.38
581212684    0             6990318       0                    1
590076007    424871.7      1613414       3                    141623.9
609867385    634290.6      242893.5      9                    70476.73
611971346    0             1352185       0                    1
618523396    482402        2436189       3                    160800.7
638857097    506745.8      345551.6      6                    84457.63
642440800    0             968652.6      0                    1
668536791    388252        739661.8      4                    97062.99
696307241    704721.1      1175984       9                    78302.34
696405093    0             613530.4      0                    1
720243718    379409.2      1329417       5                    75881.83
764035545    671534.4      300519.1      9                    74614.94
764436647    0             2236918       0                    1
779902503    557097.9      729285.1      5                    111419.6
782651012    0             5021681       0                    1
787311739    51080.31      7401655       2                    25540.16
787658666    0             1174271       0                    1
794444287    485106.9      497775.6      1                    485106.9
812796309    133266.6      3350944       2                    66633.28
831139716    353817        2680997       6                    58969.5
842885441    431737.9      2.158519E+07  3                    143912.6
843180353    0             188592.6      0                    1
848364141    0             1.564916E+07  0                    1
848718601    0             1460081       0                    1
859045913    131081.9      1137701       3                    43693.95
862818092    505081.3      742684.1      4                    126270.3
863874155    0             631160.6      0                    1
881984062    292437.4      1804021       6                    48739.56
884903988    53993.24      443575.4      1                    53993.24
905853184    279017.1      2579850       5                    55803.42
924871165    719285.7      6134700       5                    143857.1
942015899    364428.4      589855.6      3                    121476.1
955245837    433298.4      616754.2      3                    144432.8
970027013    805113.1      2105749       2                    402556.6
978741593    646878.6      576658.1      3                    215626.2
984236267    787739.6      14003.4       2                    393869.8
999078776    490412.6      663902.3      3                    163470.9
1015434386   562923.8      3186731       3                    187641.3
1030757273   836843.3      5440776       4                    209210.8
1042022791   470022.1      23171.1       3                    156674
1049235058   762771.6      1097101       2                    381385.8
1049523918   0             55610.64      0                    1
1050752972   0             884934.4      0                    1
1068406376   495614.3      4429407       5                    99122.85
1069377408   0             1799185       0                    1
1103721264   688595.9      1011872       12                   57382.99
1129397940   619101.7      842722.8      10                   61910.17
1144061227   824463.3      308679.3      8                    103057.9
1165783616   612651.6      291049.1      7                    87521.66
1178006382   774527.3      166227.4      2                    387263.7
1187202130   303985.1      1379386       4                    75996.27
1208361907   554601.1      692513.6      9                    61622.34
1218232928   0             566785.2      0                    1
1219205959   0             1396008       0                    1
1238422657   607970.1      417986.5      6                    101328.4
1271205882   657802.1      488809.4      13                   50600.16
1280533217   506537.8      578572.3      5                    101307.5
1287262272   0             1664390       0                    1
1289980085   0             579781.2      0                    1
1313939622   552208.3      646272.1      4                    138052.1
1318507436   295870.5      1115739       2                    147935.2
1323750208   0             6837288       0                    1
1331056255   0             190909.7      0                    1
1332355030   0             1310175       0                    1
1334783440   91445.2       574038.8      2                    45722.6
1337942947   0             945985.3      0                    1
1345460130   200992.2      390181.1      2                    100496.1
1363229371   648231        418187.9      6                    108038.5
1367620647   0             1167823       0                    1
1369802692   0             530819.6      0                    1
1382530698   716892.9      2951998       4                    179223.2
1383198627   0             1611.902      0                    1
1385566106   0             1895597       0                    1
1401376801   315116.6      2230168       2                    157558.3
1402778320   0             2120055       0                    1
1426242199   585082.8      1696326       6                    97513.8
1445656093   373063.1      429068.3      7                    53294.73
1454568256   171966.9      1426030       3                    57322.3
1457039076   0             705106.6      0                    1
1494135363   700871.8      3956112       6                    116812
1505304046   259354.8      580788.6      3                    86451.61
1516390936   598295.1      1.890197E+07  4                    149573.8
1525045548   204633.3      5118899       2                    102316.7
1557484794   339252.3      690095.8      8                    42406.54
1571488783   464404.3      436221.1      4                    116101.1
1595881690   99247.7       862065.6      4                    24811.92
1634936481   715332.4      754068.1      8                    89416.55
1639182201   740924.6      509864.9      2                    370462.3
1646527249   0             1007.439      0                    1
1646941088   0             5893116       0                    1
1654392278   102264.7      666622.4      4                    25566.16
1674409579   344974.2      712561.6      3                    114991.4
1699069300   785034.7      1720907       4                    196258.7
1713610617   763811.9      5329856       2                    381906
1734125760   829664.9      273821.9      6                    138277.5
1751506201   61899.77      1651193       3                    20633.26
1783691823   498423.2      1541785       8                    62302.89
1794206692   137844        602549.3      1                    137844
1817730501   246350.7      494753.3      3                    82116.89
1836163789   439228.3      649697.4      4                    109807.1
1846100685   61483.63      574945.4      3                    20494.54
1848764870   548879.3      788421.8      2                    274439.6
1854703085   644173.7      741273.6      1                    644173.7
1855905898   0             2665482       0                    1
1875586598   759130.4      127441        9                    84347.82
1905732551   451400.2      1877060       7                    64485.73
1923969059   533898.5      830029        6                    88983.08
1930982747   706801.7      736740.2      3                    235600.6
1934732852   0             2469838       0                    1
1936729638   16437.25      518226.7      1                    16437.25
1945385615   169366.1      7735419       4                    42341.52
1959406231   515484.6      451433.4      3                    171828.2
1959879121   0             13499.68      0                    1
1962780748   0             6853104       0                    1
1963247487   0             742583.3      0                    1
1963705883   0             32741.77      0                    1
1966383379   0             6.732514E+07  0                    1
1974982145   148871.5      2296961       4                    37217.89
1981987968   0             2039661       0                    1
1982512875   0             258408.1      0                    1
1983391065   0             893799.9      0                    1
1985731883   0             57927.75      0                    1
1994016146   840796.5      116862.9      2                    420398.3
2018650402   667789.3      4752795       6                    111298.2
2022299131   0             1657842       0                    1
2023991904   0             703796.9      0                    1
2029286825   0             69412.55      0                    1
2029813312   0             752859.2      0                    1
2043516036   722406.7      647481.1      4                    180601.7
2045434923   672054.6      362678.1      3                    224018.2
2049319608   0             65886.52      0                    1
2050084894   0             798294.7      0                    1
2061383394   842148.9      171264.6      4                    210537.2
2071927591   0             1.371588E+07  0                    1
2077489557   335611.2      1197543       4                    83902.79
2089634735   211499.5      945683        2                    105749.8
2093981143   663003.7      226371.5      2                    331501.8
2121380450   523911.3      623705.5      4                    130977.8
2144751374   317717.5      48558.56      9                    35301.94
2145555613   0             120288.2      0                    1


Read-only replica:

RANGE_HI_KEY RANGE_ROWS    EQ_ROWS       DISTINCT_RANGE_ROWS  AVG_RANGE_ROWS
------------ ------------- ------------- -------------------- --------------
10036025     0             12001         0                    1
10685820     0             3.648772E+07  0                    1
17425392     0             19305.95      0                    1
20650966     0             2374110       0                    1
37481118     333301.8      2872934       3                    111100.6
38999786     0             6024500       0                    1
42797355     992905.3      1352460       1                    992905.3
52298624     948752.3      1094178       1                    948752.3
63078129     0             511346.8      0                    1
67956212     414608        521782.4      2                    207304
70014912     0             797283.5      0                    1
78290075     574528.2      1024781       3                    191509.4
91908681     367762.7      3845015       2                    183881.3
92882092     0             453428.9      0                    1
102933497    508298.6      619355.7      4                    127074.7
114647353    493760.4      633965.6      3                    164586.8
117280928    767832.4      2117915       2                    383916.2
118481695    0             258282.3      0                    1
126455934    694064.5      474822        1                    694064.5
130018333    0             143490.2      0                    1
133166405    0             827025.1      0                    1
139388826    0             410121        0                    1
143605335    0             3138521       0                    1
146292774    0             5027895       0                    1
149776493    438299.9      2248882       3                    146100
155544533    0             6261.389      0                    1
156886357    0             1467774       0                    1
188578945    287533.3      362117        4                    71883.33
208460044    985367        3645172       3                    328455.7
210976629    0             128358.5      0                    1
213385470    0             680926.1      0                    1
217867075    0             1167749       0                    1
219937520    0             113748.6      0                    1
228674264    0             3763617       0                    1
241657619    530913.6      537957.7      3                    176971.2
244957612    0             758149.8      0                    1
245682371    0             1253321       0                    1
280221533    538990.4      2054.453      2                    269495.2
308457016    683833.9      547349.8      3                    227944.6
317994472    642911.6      1132790       3                    214303.9
340526340    787216.7      1.12298E+07   5                    157443.3
341444485    0             1900853       0                    1
347781020    0             536914.1      0                    1
364058765    705372        3709873       4                    176343
369425321    584220.3      751888.4      1                    584220.3
392573476    293456.3      567177.5      1                    293456.3
423991888    691372.3      2445594       6                    115228.7
426172468    0             564568.6      0                    1
442051581    829754.4      499867.5      2                    414877.2
457486429    466299.4      289067.4      4                    116574.8
471764642    382300.9      123662.4      3                    127433.6
479572926    863676.9      632922.1      1                    863676.9
480168645    0             114792.1      0                    1
492057913    807139.4      626138.9      1                    807139.4
493010287    0             7826.736      0                    1
494883772    0             631878.5      0                    1
516570317    969751.9      393945.7      4                    242438
516638811    0             1039912       0                    1
519159343    0             2067824       0                    1
520900373    0             2341238       0                    1
548848751    79690.88      43829.72      1                    79690.88
554141028    899753.2      1099396       2                    449876.6
560742119    298302.4      1999470       2                    149151.2
577790987    846446.4      577091.3      3                    282148.8
581212684    0             6505061       0                    1
589707547    33922.47      454994.3      2                    16961.24
590076007    0             2063128       0                    1
611971346    726371.6      1686923       6                    121061.9
617639536    81844.7       473256.7      1                    81844.7
618523396    0             1985904       0                    1
627749265    385531.6      1             2                    192765.8
642440800    652065.3      649097.3      2                    326032.6
668536791    561605.4      553611.1      3                    187201.8
696307241    582066.5      1786583       6                    97011.09
696405093    0             1148965       0                    1
720243718    384454.7      1476122       2                    192227.3
764436647    744140.6      2998684       3                    248046.8
782651012    561066.9      4831705       3                    187022.3
787311739    221842.2      7398875       2                    110921.1
787658666    0             1635266       0                    1
812796309    724756.3      2919373       3                    241585.4
831139716    0             3079038       0                    1
842885441    192765.8      2.050344E+07  2                    96382.89
848364141    0             1.443876E+07  0                    1
848718601    0             1041478       0                    1
859045913    232072.8      1147400       2                    116036.4
862818092    900291.6      569264.6      3                    300097.2
863874155    0             689274.6      0                    1
881984062    351070.7      1623265       3                    117023.5
905853184    649911.4      3068081       3                    216637.2
919815804    297763.9      616225        1                    297763.9
924871165    178766        5161472       2                    89383.02
935631522    146458.9      22958.43      1                    146458.9
952302491    633219.4      532739.8      1                    633219.4
960683500    1036520       495171.5      2                    518260
970027013    0             2206618       0                    1
978741593    892753.3      769629.1      1                    892753.3
982623966    438838.3      758149.8      1                    438838.3
999078776    188996.6      913641        2                    94498.31
1015434386   673064.9      2942853       2                    336532.4
1026302963   368301.1      265065.5      1                    368301.1
1029102660   712910.3      636574.6      1                    712910.3
1030757273   0             3826752       0                    1
1044542348   759217.2      402816        3                    253072.4
1049235058   0             1321675       0                    1
1050752972   0             690839.9      0                    1
1068406376   514221.6      5565853       4                    128555.4
1069377408   0             1467774       0                    1
1103721264   380685.5      1022172       3                    126895.2
1129397940   782909.1      657445.8      8                    97863.63
1150456784   932598.8      72005.97      4                    233149.7
1173719470   515836.9      647010.2      1                    515836.9
1187202130   849677.1      2063649       4                    212419.3
1192811402   0             141924.8      0                    1
1207337091   982674.8      126793.1      5                    196535
1218232928   857215.4      494127.9      1                    857215.4
1219205959   0             1826760       0                    1
1238422657   576682        623008.2      4                    144170.5
1271205882   740371.4      649619.1      10                   74037.14
1280533217   303148.4      618312.1      2                    151574.2
1289980085   1100057       822329.1      1                    1100057
1318507436   1032212       1075915       6                    172035.4
1323750208   0             7337826       0                    1
1331056255   0             220714        0                    1
1334783440   863676.9      446645.8      1                    863676.9
1337942947   0             1015389       0                    1
1363229371   1046212       328722.9      6                    174368.7
1367620647   0             911553.9      0                    1
1378288320   676834.1      506650.7      3                    225611.4
1382530698   0             3138521       0                    1
1385566106   0             2379328       0                    1
1398549638   0             159665.4      0                    1
1401376801   0             2453943       0                    1
1402778320   0             2709094       0                    1
1426242199   296148.6      2136699       2                    148074.3
1454568256   890599.5      1829369       6                    148433.3
1457039076   0             700232        0                    1
1494135363   394685.3      4088687       3                    131561.8
1505304046   0             541088.4      0                    1
1516390936   650988.4      1.834952E+07  3                    216996.1
1525045548   266533.7      5413493       2                    133266.8
1557484794   505606.3      440384.3      3                    168535.5
1595881690   873907.4      983559.8      5                    174781.5
1618342287   883061.1      72527.76      4                    220765.3
1634936481   249303.2      636052.8      2                    124651.6
1639182201   833523.6      311504.1      2                    416761.8
1646941088   0             6600026       0                    1
1651215692   0             224366.4      0                    1
1654392278   0             768063.7      0                    1
1674409579   174996.9      812415.2      2                    87498.44
1682100971   370993.4      540566.6      2                    185496.7
1686514783   0             406468.5      0                    1
1699069300   0             1756320       0                    1
1713610617   760294.1      5463062       2                    380147.1
1718160907   0             579700.3      0                    1
1751506201   256303.1      1462556       4                    64075.78
1783691823   474914.6      1248625       2                    237457.3
1817730501   730679.3      463864.6      3                    243559.8
1836163789   375839.4      656402.3      3                    125279.8
1846100685   200304.1      447689.3      3                    66768.04
1848764870   254149.3      1162531       2                    127074.7
1854703085   998828.3      1140095       1                    998828.3
1855905898   0             2242099       0                    1
1905732551   858830.8      2006775       6                    143138.5
1917149359   0             154969.4      0                    1
1923969059   747909.7      433601.2      1                    747909.7
1926173252   0             98616.88      0                    1
1930982747   930983.4      857810.3      2                    465491.7
1934732852   0             1944161       0                    1
1936729638   0             767020.1      0                    1
1945385615   299379.3      6017195       3                    99793.09
1953738960   379070.2      65744.59      1                    379070.2
1959406231   572374.4      642314.1      1                    572374.4
1962780748   0             7348784       0                    1
1963247487   0             771716.2      0                    1
1966383379   0             6.738506E+07  0                    1
1972778496   0             54265.37      0                    1
1974982145   0             2510295       0                    1
1981987968   0             2011471       0                    1
1989496326   1016597       478474.5      2                    508298.6
1991190857   0             556741.8      0                    1
2018650402   932060.3      3974417       5                    186412
2022299131   0             1836674       0                    1
2023991904   0             567699.3      0                    1
2029286825   0             69397.06      0                    1
2043516036   1075289       808240.9      3                    358429.5
2045434923   771063.1      348550.7      2                    385531.6
2050084894   0             1210013       0                    1
2051361851   0             123140.6      0                    1
2051961023   0             986690.5      0                    1
2061383394   0             240019.9      0                    1
2071927591   0             1.344894E+07  0                    1
2077416739   0             103312.9      0                    1
2077489557   0             1216797       0                    1
2078012124   0             121053.5      0                    1
2089634735   0             594310.2      0                    1
2089901075   0             1085829       0                    1
2121380450   950367.6      708580.5      3                    316789.2
2125403860   0             142968.4      0                    1
2145555613   0             43307.94      0                    1


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2014-01-16 : 16:07:26
Here's the background.

The AlwaysOn Availability Group has 5 replicas. 3 are synchronous and 2 are asynchronous for DR purposes. For the sync replicas, we have a read-write replica, a read-only replica and a manual failover replica in case we lose either the RW or RO. A single server can currently handle the load if we lose 2 servers. Hardware is identical on all servers.

On the read-only replica, we are experiencing a severe performance issue after the update stats job runs on the read-write replica. We get a "bad" plan, and it appears to be due to statistics. Both the "good" and "bad" plans are using the same indexes, just a different path. We are trying to come up with a plan guide to workaround this issue, but we are wondering if we can turn off the temporary statistics that SQL Server does for read-only replicas. We want it to use the statistics that were generated on the read-write replica and that were replicated to the read-only replica. The read-write replica is using a 2% sample for the index; the read-only replica shows a 0.20% sample.

We experienced this same issue on SQL Server 2005 when we were using sp_updatestats (both with resample and without). We had to roll our own update stats stored proc and found that 2% sampling works great for this table. Anything below 1% was causing a "bad" plan. This is why we suspect it's a statistics issue since the rows sample is 0.20% on the read-only replica.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2014-01-16 : 16:10:34
Here's the snippet for the update stats code that gets run on the read-write replica:


	SET @RowsSampled = 
		CASE
			WHEN @RowCount < 500000     THEN '100 PERCENT'
			WHEN @RowCount < 1000000    THEN '50 PERCENT'
			WHEN @RowCount < 5000000    THEN '25 PERCENT'
			WHEN @RowCount < 10000000   THEN '10 PERCENT'
			WHEN @RowCount < 50000000   THEN '5 PERCENT'
			WHEN @RowCount < 100000000  THEN '2 PERCENT'
			WHEN @RowCount < 1000000000 THEN '1 PERCENT'
			ELSE '20000000 ROWS'
		END


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site