Open Forum

Expand all | Collapse all

A few SQL settings you might wan to think about

  • 1.  A few SQL settings you might wan to think about

    TOP CONTRIBUTOR
    Posted 17 days ago

    Recently we had a company audit one of our SQL Servers (warehouse mgt system) and they did such a good job we had them do the same thing for our NAV SQL Server.  We have NAV 2018 and our database size is around 500 gb.  Remember every database is a little different with different hardware so take the following suggestions with a grain of salt.  Also this is a list of things that might be a little less known but certainly is not all inclusive, so add things if you would like.

    • You should set up Alerts for Severity 17 to 25 which helps predict hardware failure and fatal errors. Here is a screen showing where to do this under SQL Server Agent: 
    • Make sure your backups have "Verify backup integrity" = true. Takes longer (maybe double) but important to check the integrity.  Some people will dispute this saying it is not worth the additional time
    • To speed up your backups (and restores) make sure the "Set backup compression" is set to Compress Backup. Your backup will be much smaller in size as well.
    • The above two bullet points apply for Transaction Log and Differential backups as well.
    • If you have NAV 2009 and above in Database Properties, Options "Auto Create Statistics" should be true .
    • In Database Properties, Options "Auto Update Statistics" should be false. This assumes of course that you have a job that updates statistics (probably nightly).
    • In Server Properties, Advanced, "Max Degree of Parallelism" (MAX DOP for short) should be set to half of your logical processors. We have 16 so we set it to 8.  Easy to increase/decrease to optimize performance.  Logical processors can be seen by going to Task Manager, Performance, CPU as seen here:
    • Along with MAX DOP you should set "Cost Threshold for Parallelism". Don't understand this one yet but it was suggested to set to 25 and then we later raised to 40 which is what it is now.  These last 2 settings can be changed with users on the system.
    • Another property under Server Properties is called "Optimize for Ad hoc Workloads". We do not understand this yet so it remains false until we learn more.
    • Your SQL Install should not be located on your C Drive. Running out of space on C Drive would be bad (applies to the next 2 bullet points as well).
    • Your system databases should not be located on your C Drive.
    • Your TempDB should not be located on your C Drive.
    • Your TempDB should contain one physical file per Processor. Our SQL partner said "This will alleviate SGAM contention."  Don't know what that means but supposedly SQL will automatically assign one process to one file to improve performance.  Though we developers typically use Temp Tables pretty heavily in NAV, that is done in memory on the client machine and has nothing to do with the SQL TempDB database.  I do not think NAV uses TempDB too often so I wouldn't expect this to change the world for you, but if you know otherwise please tell me.
    • Make sure all of your jobs have a Notification (email and or text) attached to them.
    • Make sure SQL Server Agent has "Auto restart SQL Server Agent if it stops unexpectedly" = true and "Enable fail-safe operator = true (need to set up an Operator and Notify method as well).
    • It was recommended to set our auto-growth options on our Database and Log files to 512 mg instead of a percent. For large databases if you use a percent it could take a little while to expand and cause some issues while expanding.
    • After all of this stuff you can really improve performance by creating indexes where needed.  This is something that takes time and effort.  Apply a few indexes at a time to see if it helps, if no then remove them.  Remember, creating an index can massively improve the performance of something but at the same time there is a cost to maintain that index so be cautious.  Do you know that SQL keeps track of all of your indexes and how often they are used and updated?  You can run a query to find indexes that are never used (or barely used) and compare it to how often it is maintained which helps you decide if you need it or not.  This index data is cleared each time the SQL Service is restarted so make sure you have a weeks worth of uptime before running this.  Remember that if you are creating/removing an index always do it through the the NAV developer environment not SQL Server.

    Please add to this list if you deem it worthy.

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


  • 2.  RE: A few SQL settings you might wan to think about

    TOP CONTRIBUTOR
    Posted 16 days ago
    Hi Jason

    Thanks for sharing Jason

    A few comments/additions:

    • In Server Properties, Advanced, "Max Degree of Parallelism" (MAX DOP for short) should be set to half of your logical processors. We have 16 so we set it to 8.  Easy to increase/decrease to optimize performance.  Logical processors can be seen by going to Task Manager, Performance, CPU as seen here:
    • Along with MAX DOP you should set "Cost Threshold for Parallelism". Don't understand this one yet but it was suggested to set to 25 and then we later raised to 40 which is what it is now.  These last 2 settings can be changed with users on the system.

    Be very careful editing these. While they can make some things quicker, I've seen them make wait stats go through the roof (reducing overall performance)

    • Your SQL Install should not be located on your C Drive. Running out of space on C Drive would be bad (applies to the next 2 bullet points as well).
    I normally leave system DB's on drive C, but data files, log files and tempDB files all get their own dedicated drives. Jury's out on if this is still relevant on large RAIDs and SAN's as all "drives" are just partitions on the same physical hardware.

    • Your TempDB should contain one physical file per Processor. Our SQL partner said "This will alleviate SGAM contention."  Don't know what that means but supposedly SQL will automatically assign one process to one file to improve performance.  Though we developers typically use Temp Tables pretty heavily in NAV, that is done in memory on the client machine and has nothing to do with the SQL TempDB database.  I do not think NAV uses TempDB too often so I wouldn't expect this to change the world for you, but if you know otherwise please tell me.
    SQL server uses Tempdb a ton, regardless of the app.

    While SQL developers can utilize TempDB directly (e.g. by creating temp tables), but besides that, TempDB is used for a lot queries, especially expensive operations like sorts. I typically use 8 equally sized TempDB files - haven't seen much benefit in using more, but 1 per processor is not a bad rule of thumb. (FYI: SGAM is the Shared Global Allocation Map is responsible for tracking page allocation)

    • After all of this stuff you can really improve performance by creating indexes where needed.  This is something that takes time and effort.  Apply a few indexes at a time to see if it helps, if no then remove them.  Remember, creating an index can massively improve the performance of something but at the same time there is a cost to maintain that index so be cautious.  Do you know that SQL keeps track of all of your indexes and how often they are used and updated?  You can run a query to find indexes that are never used (or barely used) and compare it to how often it is maintained which helps you decide if you need it or not.  This index data is cleared each time the SQL Service is restarted so make sure you have a weeks worth of uptime before running this.  Remember that if you are creating/removing an index always do it through the the NAV developer environment not SQL Server.

    Brent Ozar has some good tools for indexing: https://www.brentozar.com/blitzindex/

    Would you mind PM'ing me the name of the company you used?

    Thanks

    Mark


    ------------------------------
    Mark Anderson
    Director of ERP Systems
    Clesen Wholesale
    Evanston, IL
    ------------------------------

    Conference-BCNAVUG_200x200


  • 3.  RE: A few SQL settings you might wan to think about

    TOP CONTRIBUTOR
    Posted 16 days ago
    Great job, Jason.

    Thanks for sharing.

    One thing that I like to do is to make the database large enough that it won't have to grow for at least 2 or 3 years. Small database grows will result in some disk fragmentation. Fragmentation results in slower data access because of excess movement of the heads on the drive. I haven't done recent tests on this, but the results can be significant.  The new defragmentation tools should handle this and reduce fragmentation.


    Sent from my T-Mobile 4G LTE Device




    Conference-BCNAVUG_200x200


  • 4.  RE: A few SQL settings you might wan to think about

    Posted 16 days ago
    Some good information here. I have immersed myself in it recently after experiencing some serious performance problems.  A couple of notes.

    Microsoft has different recommendations on MaxDOP.  They say to keep it at 1 for normal day to day processing due to the MARS nature of record retrieval.
    https://docs.microsoft.com/en-us/dynamics-nav/installation-considerations-for-microsoft-sql-server

    On the TempDB configuration, they are used very extensively by NAV.  Setting Trace Flags 1117 and 1118 is recommended.  One of those flags will equally apportion the usage across all temp files equally.  Start with 8 files initially, increase as necessary.

    The Brent Ozar resources are GOLD.  Helped us to find out a lot of things.  Keep in mind that his recommendations are for SQL in general, not for NAV on SQL, which is a slightly different beast.

    I agree with the recommendations on the log and database file growth, and setting them with enough free space to start.  Particularly the log files, which will prevent fragmentation.  Run DBCC LOGINFO.  If you find more than 200 records are returned, it makes sense to do a shrink and expand to remove the fragmentation.

    Set maximum AND minimum memory for SQL, leaving some overhead for Windows.  Pay attention to the limits that your SQL server version/edition has.

    ------------------------------
    Torolf Haug
    Mchutchison
    Wayne NJ
    ------------------------------

    Conference-BCNAVUG_200x200


  • 5.  RE: A few SQL settings you might wan to think about

    TOP CONTRIBUTOR
    Posted 15 days ago
    Thanks for the responses.  Based on these responses I have changed the MAXDop setting from 8 to 1 (per Microsoft's suggestion) and it has made a positive difference.

    I am holding off on setting "Auto Update Statistics" to True even though Microsoft suggests this.  It is because during our busiest days we will be invoicing for about 8 hours straight and I do not want this to slow posting down.  I will experiment at some point to see if this makes a difference.


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

    Conference-BCNAVUG_200x200


  • 6.  RE: A few SQL settings you might wan to think about

    Posted 15 days ago
    Another recommendation I've heard with MaxDOP is change it to say 8 while overnight maintenance is being performed, then change it back to 1 for normal production processing.  I personally don't do this now, but I will do some benchmarking to see if it makes a big enough difference in maintenance tasks.

    I'd be curious to hear if you find a difference with Auto Update Statistics.  It's a double edged sword.  True, you are no longer updating the statistics, which will save some writes, but it will also result in "dirty" queries being made that are not optimal, which may be worse.  Or better.

    Something else I've been using has been the IndexOptimize tools by Ola Hallengren. You can set them up to only optimize statistics or indexes on tables/indexes that actually need it.  That has resulted in generally smaller maintenance windows, but it will be variable if it decides it needs to do more.
    https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    ------------------------------
    Torolf Haug
    Mchutchison
    Wayne NJ
    ------------------------------

    Conference-BCNAVUG_200x200


  • 7.  RE: A few SQL settings you might wan to think about

    SILVER CONTRIBUTOR
    Posted 4 days ago
    I do a lot of query work and I've been working some with general SQL Server setup issues.
    This thread motivated me to create a post explaining MAXDOP and Cost Threshold in the NAV context.
    I hope it clarifies what these things do.

    https://reportsyouneed.com/sql-server-and-dynamics-nav-maxdop-and-cost-threshold-for-parallelism/



    ------------------------------
    Adam Jacobson
    President
    Red Three Consulting, Inc.
    Bronx NY
    ------------------------------

    Conference-BCNAVUG_200x200


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