Best Practices to Configure SQL Server on New Server

A few best practices are given below to configure SQL Server on a newly built server:

1. Choose correct hardware:- Assuming the correct hardware specification has been selected as per user requirement & estimated load on the server.

2. Sufficient Memory:- We should allocate sufficient memory to the server as well as SQL Server to provide better performance.

As per best practice, allocate :

10-20% of total physical memory as min. memory to SQL Server,

75-80% of total physical memory as min. memory to SQL Server.

Left at least 20% for OS & other processes.

3. Select Correct Version & Edition:- Select the appropriate SQL Server Version & Edition as per the user requirements.

4. Install Latest Updates:- Install the latest SQL Server updates on the server with proper downtime & DB/VM backups.

Always keep in mind, that updates should be N-1 fashion. It means, support currently we have Service Pack 10 for SQL Server. Apply Service Pack 9 on the server.

5. Configure the firewall properly:- To allow SQL Server traffic, Configure the firewall properly.

6. Service Account:- Always use a service account for SQL Services to make the system more secure.

7. TempDB optimization:- Optimise the tempdb database by configuring the appropriate no. of files, sizes, and separate drives for data & log files. Suppose we have 8 TempDB data files, try to maintain 4 files on one LUN & log files on another LUN to get better performance. 

8. Enable IFI:- Always enable Instant File Initialisation for SQL Server.

9. Enable Lock Pages in Memory:- Lock Pages in Memory is also an important parameter that we need to configure. Just include the SQL Server service account in Lock Pages in Memory.

10. Database Auto-Growth Settings:- Configure database Auto-Growth settings properly to prevent performance issues. Avoid specifying growth rate in %. Good to specify the number that is divisible by 8.

Suppose the database size is 2 TB, then the growth rate should be 5-10% of the DB size.

11. Backup Compression: The backup compression option should be enabled state of the SQL Server. It'll help to reduce the time of DB backup, the size of backup files as well and the load on the network if we store backup files at a centralized location. 

Check the below articles also: 

DBCC Freeproccache in SQL Server: A powerful command

Understand Deadlocks in SQL Server

Unleash Database Insights with Extended Events in SQL Server

Best Practices to Configure SQL Server on New Server