Methods of Tuning a Database

Tuning a database is a series of activities that are carried out to optimize and standardize the performance of a database. The main goal of database tuning is to maximize the efficiency and performance of a database through promoting the effective use of system resources. While most systems are developed in a manner that they manage their use of system resources, there is room for enhancing their efficiency and performance. The process of database tuning involves customizing the settings and configuration of a database and its system resources.

Database tuning is carried out because of various reasons including data changes, changes in codes such as releases and upgrades, and changes in usage patterns. In addition, database tuning is sometimes carried out because query optimization is imprecise and the inefficiency of underlying technologies. There are several methodologies that are utilize for tuning a database including SQL tuning, query tuning, physical tuning, and infrastructure tuning. SQL tuning is a process that focuses on ensuring that SQL statements issued by an application will run within the shortest possible duration of time.

SQL tuning usually entails three basic steps that are carried out repeatedly until the desired system performance is reached or there are no more SQL statements to be tuned (Oracle, n.d.). First, SQL tuning begins with identification of high load or top SQL statements that handle the application workload and systems resources through examining the history of the system's SQL execution. The second step involves authenticating that execution plans from the query optimizer for the SQL statements perform logically.

The third step is implementing corrective actions in order to produce better execution plans for SQL statements that are performing poorly. Sinha (2016) provides some examples of SQL including the following.

SELECT e.first_name, e.last_name, c.country FROM employee e, countries c WHERE e.country_id = c.id AND e.last_name = 'HALL'; SELECT p.product_id FROM products p WHERE p.item_no IN (SELECT i.item_no FROM items i); SELECT * FROM student c WHERE NOT EXISTS (SELECT 1 FROM class a WHERE a.student_num = c.student_num) The second methodology for tuning a database is query tuning, which is mostly carried out because queries usually require complex optimization that is mostly conducted inconsistently.

The process involves breaking the queries into smaller units through the use of temp tables to mix results (Linwood, n.d.). Some of the best practices when conducting query tuning include using hints and indexes to enhance processing consistency. For instance, when using indexes during query tuning, WITH (INDEX =. ) is utilized when a specific index is required. On the other hand, when a specific joint operator is required, WITH (MERGE, HASH, LOOPS JOIN) is utilized.

However, OPTION (FORCE ORDER) is utilized when the tables have been ordered specifically in SQL syntax for processing rules (Linwood, n.d.). The third methodology for tuning a database is physical tuning, which involves using indexing and file group or file placement. Indexing is utilized in physical tuning of a database because good indexes are the most important aspects of tuning SQL server.

The best practices in this process of indexing include using clustered indexes, using index columns in foreign keys, and considering indexing as a specific activity of a database or workload. With regards to file group or file placement, the best practices involve separating large databases into distinct file groups. Additionally, this process requires separating Tlogs from data files in storage i.e. physically separating the Tlogs.

Finally, a database can be tuned using infrastructure tuning, which is also considered as input/output (I/O) tuning through which the configuration of hardware and software is examined. Infrastructure tuning involves examining the Central Process Unit (CPU), memory, and storage to enhance the performance of the database. During the process, the number of concurrent queries, cache workload, and Tlogs are examined. In conclusion,.