SQL Performance Tuning – Tips and Tricks

As a Database Administrator (DBA), you are constantly challenged about how to improve, optimize, and maintain the performance of the database and SQL server. SQL performance tuning is one of the ways to improve and optimize the performance. All your performance related queries are answered when you browse through the Oracle Performance Tuning tutorial which elaborately explains database performance, performance tuning, describes how to optimize queries, and provides handy tips on resolving performance related issues.

If you are a beginner and looking to understand SQL database and server, then SQL Database for Beginners, Introduction to Microsoft SQL Server Databases, Microsoft SQL Database Fundamentals, SQL Server Essentials: What you should know! are very good courses to start your learning process.

SQL Performance Tuning

Why is SQL Performance Tuning Required?

SQL Statements are used to retrieve data from the database. We can get same results by writing different SQL queries. But we need to optimize the queries for better performance. By tuning the performance of the queries, users benefit as they get faster results and can take quicker decisions. The general goal of SQL performance tuning is to decrease response time (the time to complete a specified workload), increase throughput (the amount of work that can be completed in a specified time), and generate the query which will return the desired effect in the minimum time through minimum utilization of server resources.

Outline of SQL Performance Tuning

Here are some of the ways by which you can reduce the response time for end users of the system and also reduce the resources used to process the same work:

• Reduce the workload: To reduce resource consumption, it is possible to change the execution plan of the statement without altering the functionality. For example, if a commonly executed query needs to access a small percentage of data in the table, then it can be executed more efficiently with lesser resources by creating an index.
• Balance the workload: Systems often have peak usage during daytime when real users are connected to the system, while usage decreases in the night time. So it may be a good idea to run the noncritical reports and batch jobs at night and keep the daytime resources free for the more critical programs.
• Parallelize the workload: If required, you can opt to parallelize the queries that access large amounts of data. This helps to reduce the response time in low concurrency data warehouse.

Process of SQL Tuning and Analysis of Performance Issues

In a nutshell, SQL tuning involves three basic steps:

1. Identifying the high load or top SQL statements that are inefficient and are responsible for a large share of the application workload and system resources.
2. Verifying that the execution plans produced by the query optimizer for these statements are performing satisfactorily.
3. Implementing corrective actions to generate better execution plans to rectify the poorly performing SQL statements.

The above three steps are repeated, parameters analyzed and adjusted until the system performs satisfactorily and there are no more statements to be tuned.

You can have different approaches to analyze the performance issues. But to start with, it is important to know what problems to look for and identify. Here are some such common problems:

• CPU bottlenecks: Identify if the CPU performance and utilization are adequate for the application.
• Undersized memory structures: Identify if the buffer cache is adequately sized, monitor memory utilization, and execute process management, memory management, and scheduling.
• I/O capacity issues: As database resides on a set of disks, the performance of the I/O subsystem is vital for efficient database performance. Disk statistics such as disk I/Os per second and length of the service times will reveal if the disk is performing optimally or whether the storage system is being overworked.
• Concurrency issues: Find out if the database is slowing down due to high degree of concurrent activities in the database. This will be manifested in the forms of locks or waits for buffer cache.
• Database configuration issues: Determine if the database is configured to perform at the desired performance levels. Looking into incorrect sizing of log files, archiving issues, excessive checkpoints, suboptimal parameter settings may provide the required evidence.
• Inefficient or high-load SQL statements: Identify the high-load SQL statements or statements which are using excessive system resources and affecting the system performance. The query optimizer aids in this process and determines whether it is more efficient to read all data in the table, or use an index.

Determination of an execution plan is an important step in the processing of any SQL statement, and can greatly affect execution time. Query optimizer can compare the cost of all possible approaches and choose the approach or execution plan which is most economical. It can also help you tune SQL statements. By using SQL Tuning Advisor and SQL Access Advisor, you can run the query optimizer in advisory mode to examine a SQL statement or set of statements and determine how to improve their efficiency. SQL Tuning Advisor and SQL Access Advisor can suggest various recommendations, such as creating SQL profiles, restructuring SQL statements, creating additional indexes, refreshing optimizer statistics, and so on.

Unexpected performance regression or degradation in performance can occur after tuning SQL statements. Tuning SQL statements may cause changes to their execution plans and may reduce the SQL performance. Before making changes on a production system, you can use SQL Performance Analyzer to analyze the impact of SQL tuning on a test system.

For database problem detection and self-tuning purposes, it is also a good idea to analyze the available database statistics which provide information about the type of load on the database and the internal and external resources used by the database. It is helpful to have an automatic database statistics collecting, processing, and maintaining system.

Conclusion

To summarize, we can say, to improve SQL server performance it is important to tune the system and effectively optimize the queries. To run the database economically and optimally you need to identify the long-duration queries, isolate them, examine the query execution plan, analyze the individual execution steps and change the steps as required to improve the performance of the system.

If you want gather further knowledge on performance and tuning, and how to analyze PL/SQL Code, then check out the course Oracle PL/SQL 11g – Advance. If you want to add more feathers in your cap, then Microsoft SQL Server 2012 Certification Training Exam 70-461 will help you study and prepare for the MCSA or MCSE certification exam.