It has been said that performance tuning a relational database system is more art than science, but it is, in fact, an art that is based in science. To understand why a database system performs optimally or poorly, it’s best to start with an understanding of how the database system manages data. We will focus this discussion on the Microsoft SQL Server database system.
Note that this article is meant to be a high-level overview of the application rather than a deep dive into the complexities of how SQL Server manages data. While many factors may impact SQL Server performance, the following dives into indexing.
Why Indexing Matters in SQL Server
Due to the way SQL Server manages data, indexing is an important aspect of performance. The index helps the SQL Server Engine identify the location of the data within the database, so that requested records can be returned (thus, performance of queries may suffer if an index becomes out of date).
For example, if someone had a printed telephone directory, and it only contained two entries with the last name that begins with the letter “G,” the index for last names beginning with “G” may begin and end on page five. This makes finding someone with the last name of “Greene” a relatively simple task. However, if suddenly, 20,000 people with the last name beginning with the letter “G” were added to the telephone directory, but the index was not updated, it would be more difficult to find someone with the last name of “Greene” based on the index. In addition, there would be downstream effects for letters “H,” “I,” etc., since those would need to be updated. In the database world, this is known as fragmentation, and it is why database administrators must be diligent in performing database maintenance to help ensure that indexes remain optimized. This is part of the science portion of performance tuning.
In the example above, the printed telephone directory is indexed based on the first letter of the last name, which makes it easy to find someone with the last name “Greene.” But, what if an individual needed to search for someone with the first name of “Keith?” That task would be more difficult and require going over every directory page and marking all first names of “Keith.” Another option would be to include an additional index, based on the first letter of the first name. That would help, but there would still be considerable effort in matching up last name and first name. This is the challenge with proper indexing within a database management system, and it is also part of the art portion of performance tuning.
The Art of Index Selection
It is important to have indexes (at a minimum, a primary key) on a table, though having an index on the proper fields is most critical. The SQL Server database engine determines the most efficient path for returning requested data and this is accomplished, in part, based on the fields requested in the query, in conjunction with the indexes on the given table(s). Efficiently returning the requested data is highly dependent on the query being executed and how that query is written. This will determine whether performance is optimal or poor.
To expound on the telephone directory example, it makes sense to have an index based on the first letter of a person’s last name because that is primarily how the telephone directory is going to be used. Because performance is the goal, adding an index based on first name may not be the best option. The additional index would require another decision to be made to determine whether to search based on first name or last name. If the less efficient option was chosen, the result would be less than optimal for performance. Maintaining a minimum number of decision points is preferable, as there are several types of decisions that the SQL Server Engine must make whenever data is requested. This keeps the process simplified and targeted.
Balancing Art & Science
Through these examples, one can see that a cookie-cutter approach to performance tuning on a database management system is not the best approach for optimal performance. It takes time and effort to help ensure that indexes are appropriate (art) and that maintenance is completed (science) for optimal database performance.
Optimizing database performance truly requires a balance of art and science—thoughtful index selection, ongoing maintenance, and a tailored approach for each use case. If you’re facing challenges or want to take your database performance to the next level, our Analytics team is here to help. We have the experience needed and hands-on support to address your specific needs, helping ensure your systems run efficiently and reliably.
For more information, please reach out to a professional at Forvis Mazars for further guidance or collaboration on your next project.