Home > Workload Solutions > SQL Server > Best Practices > Intel-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Introduction to Best Practices for SQL Server > SQL Server Instance Best Practices for Intel-based PowerEdge and PowerMax systems
The development of the Microsoft SQL Server best practices program had one primary goal: improve customer outcomes using Dell Technologies infrastructure. In today’s fast changing technology landscape, keeping track of what best practices to use can be a challenge. Reading multiple manuals to find out how SQL Server should be optimally configured for different database designs takes time. The goal of this SQL Server best practice program is to centralize and validate recommendations that will make you successful in running SQL Server instances on our infrastructure.
Our approach to validating best practices started with gathering recommendations from multiple sources and categorizing the guidelines into different layers of the database infrastructure. For example, in the case of the optimal PowerEdge configuration for databases the team pulled best practices from current manuals and held meetings with PowerEdge experts to review the findings. This meant we actively worked with experts and engineers responsible for each part of the SQL Server infrastructure. Below is a list of each infrastructure layer validated for best practices, in the order tested:
Before starting the validation work the Engineering team conducted internal load testing to determine the workload profile. HammerDB was used to generate an Online Transaction Processing Workload (OLTP) that simulates many common enterprise applications. The goal of generating a significant load on the SQL Server infrastructure was to ensure that the system was sufficiently taxed to show how best practices optimized performance. In this case, an overall CPU utilization of 80 percent was the initial target. The HammerDB workload configuration is shown in the following table:
Table 1: HammerDB workload configuration
Setting name |
Value |
Total transactions per user |
1,000,000 |
Number of warehouses |
5,000 |
Number of virtual users |
100 |
Minutes of ramp up time |
10 |
Minutes of test duration |
110 |
Use all warehouses |
Yes |
User delay (millisecond) |
100 |
Repeat delay (millisecond) |
100 |
Iterations |
15 |
With this HammerDB configuration each best practice was validated in a two-hour-long workload test: 10 minutes ramp up time plus 110 minutes for test duration. We chose to run the workload for two hours to ensure the database system reached a consistent state of execution. Reaching a consistent run state has the benefit of validating whether the configuration is stable and if the best practice shows value over time.
One of the HammerDB parameters used, “Use all warehouses,” enables increased I/O to the database area by assigning all the warehouses to the virtual users. The result of forcing the use of all warehouses means the workload will generate more I/Os on the storage subsystem. This determined that an optimal storage configuration was required if the team was going to satisfy the goal of achieving 80 percent CPU utilization. Thus, the first set of best practices compares baseline database performance without an optimal storage configuration to a database configuration with an optimal storage configuration.
Within each set of best practices, the team sequentially tested a best practice or a set of practices. For example, with the storage configuration there were a total of seven configurations:
The first test within the storage configuration best practices category was to optimize the PowerMax storage groups. The goal was to show how optimizing PowerMax storage groups can improve SQL Server database performance. With each additional test, the working theory was that we would observe a gain in performance. Thus, in the last test in changing the PowerMax front-end I/O modules, we would achieve the overall optimal storage design. Building upon prior best practices shows that when best practices are deployed together, overall database performance becomes optimized.
All best practices are not created equal. To enhance the value of best practices, we have identified which configuration changes produced the greatest results. This system should enable anyone reviewing the best practices to easily identify which recommendations will have the most value. Best practices were categorized as follows:
When reviewing the best practices, the day and value of the recommendations will be combined. Here are some examples:
We hope this ranking system helps customers to quickly decide which recommendations will provide the best value. For example, two or three best practices from the section on storage optimization might provide most of the value. Investing time in implementing these best practices provides the greatest return. This approach can be taken for each layer of the database system until all Day 1, highly recommended best practices are completed. We believe this approach differentiates how we present best practices and provides a pathway for customers to have the best return on investment.
Best practices are broad recommendations designed to apply to most SQL environments using Dell Technologies infrastructure, but they should not be applied blindly. It is important to recognize every database application workload and environment is different and thus, the value of these best practices will vary from system to system. As with any configuration or change to a database system the best approach tests and validates the change prior to implementing the best practice on a production system. Thus, we recommend testing all best practices before implementing the changes in production.