Open Forum

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

Original Message------

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

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

Original Message:
Sent: Nov 20, 2019 04:07 PM
From: Jason Wilder
Subject: Auto Create Statistics On or Off?

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

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

Original Message:
Sent: Nov 21, 2019 10:23 AM
From: Stephen Marsh
Subject: Auto Create Statistics On or Off?

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

Original Message:
Sent: Nov 20, 2019 04:07 PM
From: Jason Wilder
Subject: Auto Create Statistics On or Off?

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