Optimize your Database Performance with SQL Tuning
Database optimization is frequently a reactive behavior whenever a database goes down. However, DBA performance specialists regularly recommend the proactive approach to identifying problematic issues inside your database ecosystem. Identifying such issues has traditionally been executed with a combination of scripts, statistics, and patience. However, there are tools, such as DB Optimizer, that can dramatically improve DBA productivity when it comes to performance tuning.
In this brief exercise, you will be able to identify, tune, and load test problematic SQL queries by using DB Optimizer. Here are the steps that you would need to take:
Download DB Optimizer | Profile Your Database | Identify Database Bottlenecks | Tune Problematic SQL | Test your Solution
Download DB Optimizer
Download a free trial copy of DB Optimizer to follow along.
Configure Your Database Environment in DB Optimizer
Using DB Optimizer, register and connect to your target data sources. In order to profile and tune statements, you need to register the data sources to be analyzed in the environment by providing connection information and other details to DB Optimizer.
See Adding Data Sources for step-by-step instructions >
Watch the video on Introduction to DB Optimizer >
Profile your Database
SQL Profiler samples a data source and builds a statistical model of the load on the database. Profiling is used to locate and diagnose problematic SQL code and event-based bottlenecks. Profiler also enables you to investigate execution and wait time event details for individual stored routines. Results are presented in the profiling editor, where you can identify problem areas and view individual SQL statements, as needed.
See Profiling a Data Source for step-by-step instructions >
Watch the video on Profiling >
Identify Database Bottlenecks
The SQL Profiler is composed of three essential diagnostic views that provide information about load on a particular database in the system. These views enable you to identify bottlenecks and view details about specific queries that execute and wait over the course of a profiling session.
In the profiling details, you will be able to determine which SQL statements, events, blockers, etc. are taking the longest to execute. For those SQL statements that may be causing bottlenecks, the next step is to tune them!
See Analyzing Session Data for step-by-step instructions >
Tune Problematic SQL
SQL Tuner provides an easy and optimal way to discover efficient paths for queries that may not be performing as quickly or as efficiently as they could be. Tuner enables the optimization of poorly-performing SQL code through the detection and modification of execution paths used in data retrieval. This is primarily performed through hint injection, and index and statistics analysis.
Automatically generate hundreds of alternative SQL statements or manually tune SQL with DB Optimizer's visual SQL tuning (VST) diagrams!
See Tuning SQL Statements for step-by-step instructions >
Watch the video on Tuning >
Test Your Solution
The SQL Load Editor/Tester enables you to configure and execute SQL code against a data source. This feature enables you to specify a data source against which the code will be executed, and then provides options that enable you to choose a period of time that you want the script to execute for, and at what intervals the execution "loop" occurs.
See Using SQL Load Editor/Tester for step-by-step
Watch the video on SQL Loading >
If you have any questions, or are ready to take the next step, contact us >