Michael De Voe, a Senior Premier Field Engineer at Microsoft, has compiled a set of recommendations for SQL Server configuration to improve performance when running Microsoft Dynamics NAV 5.0 and later versions with one of the following versions of SQL Server:
- Microsoft SQL Server 2005 SP3 x64
- Microsoft SQL Server 2008 SP1 x64
- Microsoft SQL Server 2008 R2 x64
The attached document contains Michael's recommendations, including the following options and parameters:
- Max Server Memory
- Auto-Create Statistics
- Auto-Update Statistics
- Database Compatibility Level
- Trace Flag 4136
- Trace Flag 4119
- Data files for TempDB
- Disk Alignment
- Read Committed Snapshot Isolation (RCSI)
- Max Degree of Parallelism
- Dynamics NAV Default Isolation Level
- Dynamics NAV "Lock Timeout"
- Dynamics NAV "Always Rowlock"
- Maintenance Jobs
- Instant File Initialization
- Optimize for Ad Hoc Workloads
- Page Verify
- Lock Pages in Memory
These postings are provided "AS IS" with no warranties and confers no rights. You assume all risk for your use.
Updated on February 20, 2017:
The documentation on Installation Considerations for Microsoft SQL Server and Microsoft Dynamics NAV has been updated with information on recommended settings for SQL Server 2012, 2014, and 2016.