Optimize MSSQL for Development or Test Environments
If you experience problems related to MSSQL in development or cruise environments, use this article to explore options that might optimize your system and resolve the issues. If you are not experiencing any issues, there is no need to perform any of these steps.
These tips are provided for DBAs to make use of. If you aren't familiar with an option listed, do not use it.
Required or Recommended
Start with these steps, which are required or recommended:
- Make sure that XA transactions are enabled to correctly process JMS work. This is required.
- We also recommend using a separate MSSQL instance for Agiloft. This prevents inappropriate locks by other products within the same MSSQL instance, and it allows you to reserve the sysadmin role for aluser.
Consider trying any or all of these methods to optimize your system or resolve issues:
- At Database Properties > Options, set the recovery model to Simple. If for some reason you must use a full recovery model, configure a periodic backup to avoid constant transaction log growth.
- For versions below MSSQL2016: At Database Properties > Files, set Autogrowth to 200Mb+ for data and 64Mb+ to log. Set log autogrowth for tempdb to 64Mb+.
- At Server Properties > Processors, set Maximum Worker Threads to 2048 and set Boost SQL Server Priority to On. Note that this might cause some instability.
- For versions below MSSQL2014: At Server Properties > Memory, set the maximum server memory to half of server memory.
Finally, it is highly discouraged to use a separated architecture, with JBoss at one server and MSSQL at another. However, if this is required for some reason, consider these tips for managing it:
- Use two virtual machines at one host, or use a host and guest as two machines, with the appropriate network interface. For example, use host-only networking for VMWare.
- Agiloft uses a packet size provided by the MSSQL server, so it's possible to tune network performance by tuning the MSSQL server default packet size at SSMS > Server > Properties > Advanced > Network > Packet Size.
If none of the other options resolve MSSQL-related issues, you could choose to try these options as well. These options are not recommended for production environments and might affect performance.
These methods are not safe for production and should be used only after exhausting all other options and taking appropriate precautions.
- Stop all MSSQL services from automatic execution except SQL Server (sqlservr.exe).
- Alter the "sw2_std" database to set auto_shrink on. It is also worth shrinking the tempdb data file (the transaction log cleaned automatically on every MSSQL restart) from time to time.