An Approach to Database Optimization

Optimization of any system obeys the Pareto Principle, which states that only a vital few factors are responsible for producing most of the problems. This is also known as the 80/20 Rule, meaning that most of the problems in a system result from just a few causes. Specifically, the biggest optimizations will be effected at the business process end of a system, then the application code, the database schema, and physical data storage, and the server configuration.

In a business environment that does not yet have a unified approach to solving business problems by combining business process with technology, the system administrators would tend to apply optimization to the parts of the system that were within reach. In fact, prior to the architectural enhancements made in SQL Server version 7.0 and SQL Server 2000, this was the standard approach. The logical approach seemed to be building on the server as a foundation, then the database configuration parameters, and then working upward to the parts that were less easy to reach because they involve a larger group of people for the changes required. This approach was heavily weighted toward the server, as shown here:

However, applying the Pareto Principle reveals that this approach is not going to produce the best resolution to the problem, in part because SQL Server has become a more advanced product. As SQL Server has changed over time, the central focus of optimization has shifted toward application tuning. The database administrator (DBA) can focus on areas that will have more impact (database and application design), without needing to spend so much time on areas that have less effect long term (server hardware and configuration).

The following diagram illustrates the data tier elements, and an approach in optimization moving from left to right:

Ideally, all four columns must be addressed, or the application will not be tuned optimally. In cases where tuning must be approached sequentially, then the order of effort becomes extremely significant. The elements on the left are the most difficult, but they also give the largest rewards in terms of performance. For example, while some gains in performance may be achieved by tuning the environmental settings (sp_configure), this approach will not reach the same level of performance that could be reached by tuning the code elements. Only large systems, in terms of concurrency or transaction volume, need to worry about elements three and four, the storage components and server components. The new approach is more focused on optimization as a cycle.