General Implementation, Maintenance and Optimization Recommendations

Implementation

Memory

Among the many uses that SQL Server makes of the memory allocated to it to optimize its responsiveness, we highlight the cache of frequently requested data and the creation of execution plans for application requests.

The execution capacity of these features is influenced by the amount of available memory and the size of existing databases since, with their growth, the need for additional memory usage also increases.

As such, Quidgest recommends constant monitoring of memory usage to ensure server responsiveness.

Given that the need for memory depends on various factors, such as the size of the databases, the number of users, and the type of operations performed, we provide our recommendations, which should be adapted to each environment, always with the allocation of 4 GB for the server operating system.

Memory Allocation

  • Small environments: from 16 GB to 32 GB;
  • Medium environments: from 32 GB to 64 GB;
  • Large environments: from 64 GB to 128 GB;
  • Very large environments: from 128 GB or higher;

Storage

To ensure storage, management, and optimization of information, SQL Server uses various types of files with different usage patterns. Among them, we give special emphasis to the following, which, to ensure their efficiency, should be implemented on separate disks due to different usage speeds and concurrent accesses.

Storage characterization

  • Operating System - Where the software installation and system databases reside
  • Data - Where the data files, with the stored information, reside
  • Log - Where the records of ongoing and completed transactions reside
  • TempDB - Where the TempDB system database resides
  • Filestream - Where the files stored in the SQL Server filestream reside (if applicable)

Server, Instances, and Database Settings

  • Server and/or instance collation : SQL_Latin1_General_CP1_CI_AI;
  • Database collation : SQL_Latin1_General_CP1_CI_AI;
  • Auto Close : Inactive;
  • Multi User : Active;

Maintenance

  • Periodic integrity check of databases;
  • Periodic reorganization or rebuilding of database indexes;
  • Periodic database backup, according to the established information security policy;
  • Periodic transaction log backup, if the database Recovery Model is set to Full, according to the established information security policy;

Optimization

  • If the database is not part of a replication process, it must be ensured that the Recovery Model is set to Simple, to reduce both the space occupied by the transaction log and the response time of write operations in the database;
  • Except for some duly identified situations, it must be ensured that the database has the Compatibility Level corresponding to the SQL server version where it is hosted;
  • At the Server, Instances, and Databases level, the following settings must be ensured:
    • Optimize for ad hoc workloads: Active;
    • Auto Shrink: Inactive;
    • MDF file growth settings: 1024 MB;
    • LDF file growth settings: 1024 MB;