The goal of database tuning is:

Database tuning describes a group of activities used to optimize and homogenize the performance of a database. It usually overlaps with query tuning, but refers to design of the database files, selection of the database management system (DBMS) application, and configuration of the database's environment (operating system, CPU, etc.).
Database tuning aims to maximize use of system resources to perform work as efficiently and rapidly as possible. Most systems are designed to manage their use of system resources, but there is still much room to improve their efficiency by customizing their settings and configuration for the database and the DBMS.
DBMS tuning refers to tuning of the DBMS and the configuration of the memory and processing resources of the computer running the DBMS. This is typically done through configuring the DBMS, but the resources involved are shared with the host system.
Tuning the DBMS can involve setting the recovery interval (time needed to restore the state of data to a particular point in time), assigning parallelism (the breaking up of work from a single query into tasks assigned to different processing resources), and network protocols used to communicate with database consumers.    
Memory is allocated for data, execution plans, procedure cache, and work space[clarify]. It is much faster to access data in memory than data on storage, so maintaining a sizable cache of data makes activities perform faster. The same consideration is given to work space. Caching execution plans and procedures means that they are reused instead of recompiled when needed. It is important to take as much memory as possible, while leaving enough for other processes and the OS to use without excessive paging of memory to storage.
Processing resources are sometimes assigned to specific activities to improve concurrency. On a server with eight processors, six could be reserved for the DBMS to maximize available processing resources for the database.

Poor database performance bogs down operations, and as the lifeblood of a business, companies can’t afford barriers to data access. One of the best ways to navigate past performance issues is by getting a regular database performance audit. Just like a car needs standard tuning and maintenance, database engines and the environments they reside in need to be assessed and serviced to ensure things are working as they should and performing optimally.

Anyone familiar with database management is all too familiar with the nuts and bolts of performance tuning. However, this overview should set a foundation for the basics of database tuning for those new to database procedures.

What’s Database Performance Tuning?

When we say, “database performance tuning aims to increase database performance,” there’s a lot of processes and procedures wrapped up in that statement. Depending on the context of the situation, database tuning may refer to optimizing performance by improving indexing, query response, or configuration of the database itself, whether that be the operating system (OS), CPU, etc. When you have these aligned with standard best practices, your organization is better suited for accessing data, utilizing system resources, and enjoying peak database performance.

Although these operations may seem straightforward for many, we’d like to give an overview of some of the more critical aspects of database tuning keeping in mind that all database platforms are unique in the plan to.

Query Tuning

In general, query tuning refers to improving the structure and thereby the execution time of queries. During a database tuning session, a DBA will look to the selection criteria users are employing and gauge the effectiveness of query return. Without well-structured queries and appropriate selection criteria, data access will be inhibited, resulting in slow returns. So, by enhancing the structure of queries, users can access data precisely and faster. Another thing to keep in mind is that the “queries” we’ve been talking about tuning usually originate in your applications. They might be from a vendor, developed in house or a part of your BI or reporting systems. People with experience writing and tuning queries for optimal performance usually aren’t the same people writing the applications.

Improving Indexing

We can’t talk about improving data structure without mentioning the importance of concise indexing. At its base, indexing is relatively simple: you have your search key containing copies of your primary key — or key of tables — and you have your data reference, which holds indicators to locate the key value. It’s simple, but if the index structure isn’t optimized, query processing is slow and inefficient. By analyzing and revitalizing indexing, you can reduce required disk accesses and quickly get the data you need. The best way to think about it is if there was no index, the database engine would step through and look at each row of data to see if it is what your query requested.

Input/Output Tuning

I/O tuning encompasses the entire assessment and optimization of database hardware configuration. Suppose you’re experiencing I/O bottlenecks (i.e., an I/O wait percentage is high, causing lag between the CPUs and disk subsystem). In that case, there are several things to consider: Solid-state drives, Redundant Array of Independent Disk (RAID) levels, block/stripe size, overall disk configuration, controller cards, storage systems, etc. Frequently, inappropriate placement of transaction logs — as bigtime consumers of I/O — can eat away at performance. During I/O tuning, the DBA will troubleshoot the particular cause of slow performance, configure the hardware as needed, and get your users back on track.

Database Management System (DBMS) Configuration

Finally, we get to the overhaul of database performance tuning, which is DBMS tuning and configuration. Database memory and processing resources play one of the most extensive roles in keeping the database operating smoothly. Without the right protocols in place, full functionality is exhausted on minute tasks. Reinvigorating the DBMS involves several processes, including managing memory, cache and buffers, managing recovery intervals, assigning parallelism with processors, and tuning network protocols for communication between various data consumers. When you have a well-tuned DBMS, you’ll have efficient data access.

Database Tuning from Virtual-DBA

When a client comes to Virtual-DBA looking for performance tuning, we don’t skimp on the details. We’ve spent years handling performance issues for nearly every database platform, and we pride ourselves on our ability to quickly identify problem areas, provide formative assessments and solutions to fortify database performance with little-to-no downtime. We work with every major platform and have developed substantial workflows to address the performance issues that plague all databases — commercial and open-source. The ultimate goal is to allow businesses to take full advantage of superior database performance.

If you see lag with your database or its performance isn’t what it used to be, reach out! Whether you need a full-scale tuning or just looking for an experienced consultant to help guide you through the process, we’re always happy to help.

What is meant by database tuning?

What is the best database tuning strategy?

What is database tuning in SQL Server?

What is database efficiency and tuning?