Advancements in relational database management systems (RDBMS) and availability of better hardware have made sure that response times for SQL queries are going down. However, there is still a lot that a developer can do while designing the database and writing SQL queries that can help optimize database performance. It’s also important to steer clear […]
Advancements in relational database management systems (RDBMS) and availability of better hardware have made sure that response times for SQL queries are going down. However, there is still a lot that a developer can do while designing the database and writing SQL queries that can help optimize database performance. It’s also important to steer clear of the common mistakes that database admins (DBAs) commit. Because most of the databases use the same design concepts, DBAs have every reason to advance their understanding of database performance optimization. In this guide, we’ll talk at length about how the best in the industry do it.
A monitoring system is the biggest asset in the hands of system administrators. A wholesome monitoring solution is effectively half your database performance optimization headaches solved.
The system that you choose must be able to comprehensively showcase details of systems, tools, and applications from your IT ecosystem. Such a system goes a long way in helping database admins:
For any SQL optimizer, one of the most important resources is in the form of database statistics. These stats comprise the following information:
Optimizers use these stats to dynamically decide the least expensive path to serve a query. In many database performance audits, it’s generally found that stats are outdated. The result, queries won’t be served using the least expensive paths. This increases the overall response time of the database and sends performance KPIs for a toss.
Database admins need to always ensure that stats for their databases remain updated. For whichever RDBMS product you’re using, the product manual will have all the necessary information on the commands you can execute to update stats.
Indices are a double-edged sword. Without them, your select queries will take too long to return results. Too many indices, on the other hand, reduce the performance of DML queries (insert, update, and delete).
Indices can have a significant negative impact on DML queries. While creating an index, DBAs can specify a value for the fill factor. This helps reduce the potential negative impact.
When you create an index, the data in the column is stored on disk. Next, when new rows of data are created, or the values of the data elements within the column are changed, then the index needs to be reorganized. This takes a toll on DML queries.
A solution is to specify the expected growth for an index when you know that new rows of data will be regularly added. In some RDBMS solutions, this option is known as fill factor, and in others, it’s called PCTFREE (percent free).
Mostly, the optimizer chooses the appropriate index for particular table based on statistics. However, you can also specify the index name in the select query. Most databases offer this option, using which you can take control of the performance of regularly used select queries. The syntax and method of specifying an index within a select query differ across databases.
The database returns an explanation for every select query created by the optimizer. This explanation can provide tremendous insights to DBAs in their efforts to optimize databases. Using the explanations, they can fine tune SQL queries and deliver significant database performance improvements. Each database produces offers its unique syntax for DBAs to use to get the explanations. Also, we recommend you try out one of the many 3rd party tools to run explanation commands against databases. For instance, WinSQL Professional is one of the renowned tools that also offer database query explanations analysis as one of its features.
You might already know the speed limitations of input-output operations for hard disks. When the size of your databases increases, these limitations become all the more prominent.
Some databases, thankfully, allow databases to be split across multiple hard disks. Some even allow splitting tables across different hard disks. Since more heads work simultaneously to fetch data in such an arrangement, the speed of operations is tremendously improved.
Lesser the amount of data retrieved, faster will the response be. Kind of obvious? Well, only if this were followed, most enterprise database performance measures would be a lot better. Most of the data filtering must be carried out at the server end and only a minimal amount of it kept at the client end. Because of this, limited data is sent on the wire, and the response speeds are correspondingly better.
The database level optimizations are not limited to query design improvements. There’s a lot that database administrators can achieve merely by understanding database performance with the help of a strong monitoring system.
In most enterprises and SMBs, DBAs mostly handle database tuning tasks. However, there are several developers that essentially do DBA like tasks. However, developers and administrators don’t always find it easy to work along with each other. This is caused because of factors such as different key responsive areas (KRAs), different reporting structures, and often, a lack of interpersonal skills.
Developers need to understand the reason why DBAs are not inclined to embrace rapid changes. That’s because they need to view and manage the database as a whole. The impact of the smallest of database problems could be gigantic. DBAs, in turn, should understand the developers’ need to know the status of the database and assist them in carrying out tests in quality clients.
Start with the technical aspects of database performance management, then move on to monitoring and insight drove improvements, followed by a focus on driving synergies among DBAs and database developers.
Author: Rahul Sharma
