Udemy logo

SQL Performance TuningAs 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:

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:

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.

Page Last Updated: April 2014

Top courses in SQL

SQL for Healthcare
Mark Connolly
4.5 (190)
The Complete SQL Bootcamp: Go from Zero to Hero
Jose Portilla
4.7 (186,181)
Bestseller
The Advanced SQL Course
Malvik Vaghadia
4.5 (1,217)
Advanced SQL : The Ultimate Guide
Oracle Master Training | 250,000+ Students Worldwide, Code Star Academy
4.5 (5,582)
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jean-Paul Roberts
4.5 (47,440)

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo