TABLE OF CONTENTS
Problems / Challenges
MSSQL is not running as fast as it should... Where should I look?
How do I improve the performance of MSSQL?
Solution
A. Check your memory usage on the MSSQL Database server.
Please ensure that the critical applications running on the MSSQL database machine are not starved for RAM. Reserve memory for applications like AGRIS if it such applications are installed on the same machine.
MSSQL Server is very aggressive about memory allocation and will consume as much as it is allowed to very quickly. The MSSQL RAM limit should be set to never let the total RAM consumption exceed 80% of the available physical RAM (or there will be significant negative impact on performance).
If you see something like this,
then you need to strongly consider setting limits for your MSSQL server RAM utilization or provide more memory for MSSQL to use here. Using the following assumptions, consider this formula for setting "Maximum server memory (in MB)" (in the Microsoft SQL Server Management Studio):
Total RAM
Less AGRIS RAM of ~2,048 (2,000 * 1,024/1000)
Less operating system RAM of ~ 4,096 (4,000 * 1,024/1000)
Equals amount of RAM for SQL Server
So assuming 12GB of RAM on your server, consider setting "Maximum server memory (in MB)" at 6,168 or less (i.e., 12,288 total RAM less 2,024 for AGRIS less 4,096 for the operating system).
If you have less than 12GB of RAM on your server, limit the "Maximum server memory (in MB)" to 50% or less of your total RAM.
B. Check your Database properties in Microsoft SQL Server Management Studio.
Size = current size that the MSSQL database is holding.
Space available = Should be set to consider a standard "reserved" area to not require MSSQL to continually increase the database size.
Always remember to either restart SQL or Restart the server after making this change to ensure a timely update of the config setting
