Adjust Windows operating system parameters to improve SQL server performance

The database SQLServer is born with the same parent in the Windows operating system, and they have technical connectivity. This is reflected in many aspects. For example, in our daily work, we can adjust some parameters of the Windows operating system to improve the performance of the SQL Server database server. I. restrict the size of User File Cache in system memory

Database SQL Server is born with the same parent in the Windows operating system. They are technically common. This is reflected in many aspects. For example, in our daily work, we can adjust some parameters of the Windows operating system to improve the performance of the SQL Server database server. I. restrict the size of User File Cache in system memory

SQL Server and Windows are born from the same parent. They are technically common. This is reflected in many aspects. For example, in daily work, we can adjust some parameters of the Windows operating system to improve the performance of SQLServer.

1. restrict the size of the system memory User File Cache.

Undoubtedly, the physical memory of the database server has a crucial impact on the database performance. However, sometimes we may find a strange phenomenon, that is, it increases the physical memory of the database server, but we still do not find that the database performance has been greatly improved, or it is worse than the original one. Why? For example, in combination with the SQLServer database, this is mainly because the memory allocation is not very reasonable. For example, most of the memory is used to store the File Cache. At this time, if the added memory is used to store the File Cache, simply increasing the memory does not improve the database performance. Therefore, sometimes our database administrator needs to optimize the memory usage of the SQL Server, such as limiting the memory size of User File Cache.

So how should we limit it? This is not done on the SQL Server database server, but through the configuration of the Windows operating system. To limit the file system cache, make sure that the "maximize file sharing data throughput" option is not selected (originally prepared for file sharing ), then select the "minimize memory usage" or "balance" option to specify the minimum file system cache.

Specifically, you can adjust this option as follows to improve the server performance. First, open the control panel, select network connection, and click local connection. Open the General tab and select Properties. In the local Connection Properties dialog box, select the General tab, select file and printer sharing for Microsoft Network, and click Properties. In the displayed dialog box, remove the "maximize file sharing data throughput" option and select "maximize network application data throughput ". Then press OK. In this way, the operating system automatically adjusts the memory allocation and does not reserve much space for the File Cache, thus improving the internal processing performance of the database. This is often useful for transactional application systems and can greatly improve database performance.

However, after adjusting this configuration, if a file server is deployed on the platform server, the performance of the file server will be greatly affected. For file server applications, a large amount of File Cache is required. To some extent, database servers and file servers conflict with each other. Database administrators need to weigh each other and then determine an optimal solution. However, we do not recommend that you deploy the database server, file server, and print server on the same server. Because of these three different applications, resource competition between them is still very powerful. Therefore, the database performance will be greatly affected.