1. Handling the logs
Logging strategies are one of the most ignored aspects of an SQL Server. Enterprise applications have a large amount of logs being generated on a daily basis, and they tend to become one of the bottlenecks in the application quickly. Some of the strategies that you can implement are as follows.- Transaction Logs – These logs require a significant amount of free space. Failing to do so leads to these files running the grow file operation. The grow file operation is an expensive, time-consuming operation that can block the DML commands of the database until the operation has been completed.
- Virtual Log Files – Most of the applications have a higher number of VLF files that necessary in a transaction log. It is essential to set the right size so that the number of these files that are created doesn’t go out of hand and cause performance hits.
- Separation of Concern- while conducting performance tuning in SQL server, it important you make sure that the server is not overloaded. To achieve performance stability make sure that the data files are separate from the log files. By doing so, you are making sure that the random access of files does not interfere with the sequential access.