Hardware configuration for SQL Server application procedural Optimization

Select hardware

Choosing the best hardware for your SQL Server application involves many factors, such as the size of the database, the number of users, and the way the database is used (OLTP or OLAP. Although there is no success formula to estimate the server hardware requirements, the best way is to start testing your application in advance during the development phase. Although many experienced DBAs can give reasonable estimates of the best hardware you need, only by passing actual tests can you be sure what hardware meets your application's needs.

When examining server hardware, consider the following hardware options:

CPU: You need to purchase a server that can expand the number of CPUs. For example, if you believe that a single CPU server is sufficient, you should purchase a server with at least two CPU installation spaces, even if the location of another CPU slot is empty. Reserve space for future upgrade and expansion.

Memory: it may be the hardware that has the greatest impact on SQL Server performance. Ideally, your entire database should be able to fit in to memory. Unfortunately, this is generally impossible. The minimum requirement is that the memory size should be able to accommodate the largest tables in your database. If it is economically acceptable, the server should be equipped with the memory that can support the size. In other words, there is no harm if there is more memory.

I/O subsystem: it affects SQL Server performance only after memory and is also very important. The minimum requirement is to use a hardware RAID system to run your database. Generally, you should purchase multiple small hard disks instead of one large one. The larger the number of hard disks in the array, the faster I/O can be obtained.

Network Connection: On your database server, there should be at least one MB Nic, and it should be connected to a switch. Ideally, the server should have two NICs that are connected to the switch in full duplex mode.

Tuning Server

Without proper configuration and optimization, the most expensive server hardware may not have the best performance. I have encountered many hardware-related performance problems, most of which are caused by incorrect driver installation. Many of these hardware performance problems are often difficult to track and solve. In general, an experienced technical expert should be given to ensure that the hardware is correctly installed and configured. Then, test your application under certain conditions before the server is used in the production environment to discover potential performance problems. In addition, your operating system must be correctly configured, which involves many aspects and cannot be described here.

To achieve the best performance on one Server, SQL Server should have a dedicated Server instead of other management tools. Do not install your IIS or MTS Server and SQL Server on the same Server to save a little money. This not only affects the performance of SQL Server, but also makes it difficult to optimize performance and troubleshoot.

Optimize SQL Server configurations

Another common misunderstanding of optimizing SQL Server is that you must customize and optimize multiple configurations to achieve optimal performance. For some earlier versions of SQL Server, this approach may be reasonable, but for the latest version of SQL Server, configuration is usually no longer a problem, of course, it may be another situation for ultra-large and ultra-busy servers.

In most cases, SQL Server can optimize itself. That is to say, SQL Server can check its own running tasks and then automatically make internal adjustments to achieve the highest possible performance for specified tasks.

When you test the performance of SQL Server, remember that it takes some time for SQL Server to optimize itself. In other words, the performance you obtain immediately after starting the SQL Server service is different from that of the SQL Server that runs for several hours under load conditions. Therefore, before testing, SQL Server should have time to adapt to your load.

You can modify 36 SQL Server configuration options by using Enterprise Manager or sp_configure. If you have no experience in optimizing SQL Server, I do not recommend that you modify any settings of SQL Server. If you are a newbie, your modifications will often be counterproductive and reduce the performance of SQL Server. Once you modify the settings of SQL Server, it will lose its self-tuning capability.

If, after careful consideration, you still think that modifying one or more SQL Server configurations can improve the performance in a specific environment, you should be careful to modify them. Before modifying the settings, you should first use tools such as performance monitor to understand the performance of the current SQL Server and use it as a benchmark. Make only one modification at a time. Do not make multiple modifications at a time, because you cannot determine the performance changes caused by each setting.

After one modification, the performance of SQL Server is measured again under the same load to see if it has actually improved. If no, restore to the default settings. If it does improve, continue to check whether the performance will also improve under other loads. Through subsequent tests, you may find that your modifications can improve the performance under some loads, but will reduce the performance under other loads. This is one of the reasons why I do not recommend you modify most settings.

In general, if your SQL Server application encounters performance problems, it is very unlikely to solve these problems by modifying SQL Server settings.