Tune Your Queries with SQL Optimizer (Pt 1)

One of the many ways to improve database performance is to tune SQL queries running in your environment. Two primary reasons for tuning are:

1. Minimize Response Time – End users who are interacting with data want that data as quickly as possible. It may be mission-critical data ( e.g. medical, financial). Often, the speed with which the data is returned shapes the level of customer satisfaction. No one likes to wait.
2. Minimize Resource Usage – Both hard and soft costs are associated with resource utilization. If the queries are not optimized, they may use more CPU and/or I/O than necessary. That can translate to the need for increased hardware spend. Additionally, throughput can be affected by the inefficient use of resources. Everyone is familiar with the concept of “time is money”.

If keeping SQL queries running at peak performance is on your plate, Quest offers a SQL tuning solution called SQL Optimizer. Though is it a stand-alone client-based installation, it integrates with both of Quest’s monitoring and diagnostics solutions – Foglight and Spotlight (ask for the “Expert” edition).

First, I will explain how you can get an evaluation copy to put it through its paces in your environment.

Next, I will point out various integration points from Quest’s monitoring solutions – Foglight and Spotlight Enterprise.

**Part Two of this blog will focus on SQL Optimizer workflow steps and primary features.

Note that this add-on .exe download allowing for the integration with Foglight is available for SQL Server and Oracle only and for Spotlight, SQL Server only (as Spotlight is a SQL Server-specific solution).

1. Navigate to the evaluation page for Foglight and/or Spotlight Enterprise.

Foglight for SQL Server -> https://www.quest.com/register/55604/

Foglight for Oracle -> https://www.quest.com/register/55603/

Foglight for Cross-Platform databases -> https://www.quest.com/register/55612

          Note: The Foglight for Cross-Platform databases download page will list SQL Optimizer for both SQL Server and Oracle.

Spotlight Enterprise -> https://www.quest.com/register/54738/

2. Log in if you already have a Quest account or fill out some basic contact information, and you’ll be brought to the download page.

3. Scroll down on the download page until you see this… 

          (Click on images to enlarge)

      4. Click on the down arrow icon to download it.

      5. After a quick and easy wizard-driven installation, you’re ready to go.

There are several ways to initiate a tuning session.

As described earlier, one way is via integration with Foglight or Spotlight Enterprise.

Here’s where to find that integration in each solution.

Foglight:

In Foglight’s Performance Investigator (PI) drilldown page, once you have highlighted a SQL Statement, click the ‘Tune SQL‘ button.

Spotlight Enterprise:

In Spotlight, there are a couple of places to initiate the integration.

Monitor tab > SQL Activity button > Sessions tab > Highlight the statement > square SQL icon > right-click in the white space where the SQL is displayed > choose 'Optimize SQL'….or it can be accessed the same way via the ‘Query Execution Statistics’ tab ( from the Monitor tab > SQL Activity button…..7 tabs to the right of the Sessions tab.

In either example, the last step launches SQL Optimizer and brings the highlighted SQL over to SQL Optimizer to initiate a tuning session.

As mentioned, there are other ways to bring SQL into SQL Optimizer to tune. You can simply type the SQL text or copy/paste SQL text from other sources (including copy/paste options in Foglight and Spotlight) …like here, for example…

Additionally, the 'Scan SQL' option (tab) retrieves and analyzes SQL from sources including binary files, statements embedded within database objects, application source code, SQL Profiler, the SGA (Oracle), and select Quest solution repositories.

Once you have the SQL you’d like to tune in SQL Optimizer, you’re ready to tune.

Feeling adventurous? Give it a try! SQL Optimizer has built-in Help files accessed via the ‘Help’ tab at the top to the far right side of the interface.

** Remember: Generating the execution statistics can cause overhead, so it is a good idea to run tuning sessions in a Development or Test environment.

Grab the SQL Optimizer User Guide as a reference.

(You may need a Quest Support account to access)

SQL Optimizer for Oracle -> https://support.quest.com/technical-documents/sql-optimizer-for-oracle/9.2.2/user-guide/

SQL Optimizer for SQL Server -> https://support.quest.com/technical-documents/sql-optimizer-for-sql-server/10.1.2/user-guide

Keep an eye out for Part Two of this blog with details around SQL Optimizer workflow and key capabilities.