Database Performance Tuning: What, Why, and How?

What is database performance tuning?
Database performance tuning refers to the various ways database administrators can ensure databases are running as efficiently as possible. Typically, this refers to tuning SQL Server or Oracle queries for enhanced performance. The goal of database tuning is to reconfigure the operating systems according to how they’re best used, including deploying clusters, and working toward optimal database performance to support system function and end-user experience.

Database performance tuning works initially by using historical data to establish a performance baseline. Baseline data should include:

• Application statistics (transaction volumes, response time)
• Database statistics
• Operating system statistics
• Disk I/O statistics
• Network statistics

Once a baseline is established, a database administrator can track all queries, indexes and tables for performance issues, as well as working toward making the database as efficient as possible. Once issues are identified and resolved, a database administrator can create an effective monitoring plan to prevent future performance issues.

Why should you perform database performance tuning?
Tuning your databases for enhanced performance is only the first step to keeping your applications running smoothly. The purpose of database tuning is to organize your data in a way that makes retrieving information easier. Without database performance tuning, you could face problems when running queries, whether the response is incorrect or the query takes too long to perform.

Typically, database administrators, admins or managers look to tune their database performance after complaints of slow queries or applications. When user satisfaction begins to decline, business performance starts to take a hit as well. Database performance issues can present themselves in multiple ways, including:

• Slow physical I/O
• Can be caused by poorly-configured disks or significant amounts of unnecessary physical
• I/O on those disks issued by poorly-tuned SQL.
• Latch contention
• Latch contention usually is resolved through application changes.
• Excessive CPU usage
• Caused by an inadequately sized system, untuned SQL statements or inefficient application programs.

Once these problems are detected, it’s important for a dedicated database administrator to remediate and consistently monitor them going forward.

Database performance tuning practices
Database performance tuning practices vary between databases, most commonly SQL Server and Oracle. While performance tuning can be complicated, below is a checklist of basic tasks a database administrator should complete when performance tuning.

SQL Server:

• Targeted list of wait stats
• Execution plan cache and/or query store to find long-running queries that may need tuning
• Missing indexes, fragmented indexes, or out-of-date statistics
• SQL Server configuration settings (memory, file handling, and parallelism)
• Operating system settings and disk configurations (layout, format, drivers)
• Sufficient memory and CPU allocations
• User activity and scheduled job executions
• Transaction throughput
• Overall code and design of the database

Oracle:

• AWR and Stats pack reports
• Top weight reports – such as bottlenecks- that can be tuned
• Long-running top SQL in AWR for optimization
• Storage configuration
• Memory settings for SGA and PGA
• Memory consumption/configuration
• Various init.ora for config
• Disk IO statistics
• Transactions throughout

While this is a summary of what to look for in a database while performance tuning, there are also several other specific ways to make sure your database is performing optimally.

SQL Server Execution Plan
A SQL Server Execution Plan allows the database administrator to track a query’s complete execution to figure out where an issue may be. The Execution Plan tool in SQL Server achieves this by graphically displaying the data retrieval method by the SQL Server query optimizer. To display the execution plan in SQL Server, you can follow these steps:

• Enter Ctrl + M after executing a query and the full Execution Plan will be displayed, or Ctrl + L for the estimated Execution Plan
• Right-click on the query window and select “Display Actual Execution Plan” from the context menu

From there, the database administrator can interpret and save the Execution Plan for the select queries.

SolarWinds Database Performance Analyzer (DPA)
A data performance analyzer allows database managers, developers and admins to monitor databases on-premise and in the cloud. SolarWinds Database Performance Analyzer, or DPA, provides in-depth compute metrics including CPU, memory, disk, network and more. Key features include tools for identifying performance bottlenecks, response-time analysis and database monitoring. This is designed to correlate database activity, wait times, SQL statements, application requests and other dimensions to help the database administrator pinpoint the exact cause of database slowdowns.

Query fine-tuning
One of the main purposes of database performance tuning is the improved execution of queries. Optimizing the queries in a production database is important to keep exerting the minimum impact on database performance. Without consistently fine-tuning, queries can use unnecessary production database resources and cause slow performance.

Some examples of query fine-tuning include:

• Improve the use of SELECT queries
• When running an exploratory query, use SELECT instead of SELECT* (“Select all”) to avoid using unnecessary data.
• Check for indexes
• Work with the smallest data set required
• Remove unnecessary fields and tables
• Remove calculations in your JOIN and WHERE clauses.
• Use wildcards only at the end of a phrase
• When searching for plaintext data (i.e., cities or names) try to use wildcards sparingly to search large amounts of data.
• Run your query during off-peak hours

Performance tuning with result set caching
Once result set caching is enabled, a dedicated SQL pool can automatically cache query results for repetitive use. Result set caching improves query performance and reduces compute resource usage by allowing subsequent queries’ executions to get results directly from the persisted cache so recomputation is not needed.

The cache eviction is managed by dedicated SQL pool automatically following this schedule:

• Every 48 hours if the result set hasn’t been used or has been invalidated
• When the result set cache approaches the maximum size

Users can manually empty the entire result set cache by using one of these options:

• Turn OFF the result set cache feature for the database
• Run DBCC DROPRESULTSETCACHE while connected to the database

If you have any further questions on performance tuning and how to best optimize your SQL Server or Oracle database, visit our Solutions Page or contact one of our expert DBAs below.