Open Forum

Expand all | Collapse all

Auto Create Statistics On or Off?

  • 1.  Auto Create Statistics On or Off?

    TOP CONTRIBUTOR
    Posted 21 days ago
    I have a big post in regards to some SQL settings/ best practices you should at least think about.  Before posting that I wanted to get some feedback on the option called "Auto Create Statistics".  In SQL Server Management Studio if you go to your NAV database, properties, options you should see a property called "Auto Create Statistics".  It has been suggested by a well respected SQL DBA that we have this set to True.  I believe the only reason ours is set to false is because of NAV on older versions (like the 5.0 or before) it was recommended to turn this off but can't really remember.

    So my question to everybody is for a NAV 2018 database (or similar)  what should "Auto Create Statistics" be set to?

    ------------------------------
    Jason Wilder
    Senior Application Developer
    Stonewall Kitchen
    York ME
    ------------------------------
    Conference-BCNAVUG_200x200


  • 2.  RE: Auto Create Statistics On or Off?

    TOP CONTRIBUTOR
    Posted 20 days ago
    Always a good practice to have auto statistics on.



    Sent from my T-Mobile 4G LTE Device




    Conference-BCNAVUG_200x200


  • 3.  RE: Auto Create Statistics On or Off?

    Posted 20 days ago
    We are running NAV2009r2 on SQL2008SP2 and have Auto-Statistics set to 'False'. We do have an SQL Maintenance Plan that runs nightly to update the stats and it runs for approximately 40 minutes on an 85GB database.
    The rationale for this setting has been lost to time but I believe it was to evade the performance hit of Auto Statistics.

    ------------------------------
    Stephen Marsh
    Enterprise Services Manager
    Axxess Logistics
    Vaughan
    Canada
    ------------------------------

    Conference-BCNAVUG_200x200


  • 4.  RE: Auto Create Statistics On or Off?

    TOP CONTRIBUTOR
    Posted 19 days ago
    Thanks for the reply.  I spoke to a few other people on this and for my version of NAV 2018 it is recommended to have "Auto Create Statistics" = True.

    ------------------------------
    Jason Wilder
    Senior Application Developer
    Stonewall Kitchen
    York ME
    ------------------------------

    Conference-BCNAVUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role