TABLE OF CONTENTS
MSSQL is not running as fast as it should... Where should I look?
How do I improve the performance of MSSQL?
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 a 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.
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 |
More information on server configuration can be found here.
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15
Related articles appear here based on the labels you select. Click to edit the macro and add or change labels.
|