Home > Workload Solutions > SQL Server > Best Practices > AMD-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Introduction to Best Practices for SQL Server > SQL Server Best Practices for AMD-based PowerEdge and PowerMax systems
The development of the Microsoft SQL Server best practices program had one primary goal: improve customer outcomes using Dell EMC infrastructure. In today’s fast changing technology landscape, keeping track of what best practices to use can be a challenge. Having to read multiple manuals to find how SQL Server should be optimally configured for different database designs takes a lot of 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 starts 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 to review the findings with PowerEdge experts.
This approach means we actively worked with experts and engineers responsible for each part of the SQL Server infrastructure. As we worked with groups of engineers and other experts, these best practices will appeal to database administrators. For example, each best practice will include an overview and implementation steps for the database administrators to use with their data systems. The following 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 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 Hammer DB workload configuration is shown in the following table:
Table 1: Hammer DB 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 |
5 |
Minutes of test duration |
60 |
Use all warehouses |
Yes |
User delay (ms) |
100 |
Repeat delay (ms) |
100 |
Iterations |
10 |
With this HammerDB configuration each best practice was validated in a 65-minute workload test: 5 minutes of ramp up time plus 60 minutes of test duration. This approach of running a workload for just over an hour to validate each best practice was used 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. With each test we restored the data in the database back to its baseline state to ensure each workload test used the exact same data.
One of the HammerDB parameters used, “Use all warehouses”, ensures the tool generates the maximum of 5,000 simulated inventory warehouses in the data schema, data generation, and testing. The result of forcing the use of all warehouses means the workload will generate more I/O 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 six configurations:
The first test within the storage configuration best practices category was optimizing the PowerMax storage groups. The goal was to show how optimizing PowerMax storage groups can improve performance of the SQL Server database. With each additional test the working theory, was we would observe a gain in performance. Thus, with the last test in changing the PowerMax front end I/O modules we would achieve the overall optimal storage design. This building upon prior best practices has the benefit of showing 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 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:
With this way of ranking best practices, the hope is customers can very 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 EMC infrastructure but should not be applied blindly. It’s 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.