In Part 1 of this blog post, I review how you can get an evaluation copy and explain the various integration points from Quest’s monitoring solutions – Foglight and Spotlight Enterprise. You can find it here -> https://www.quest.com/community/b/en/posts/tune-your-queries-with-sql-optimizer-part-one-why-tune-and-where-can-i-get-it
This blog (Pt 2) is intended to familiarize you with the SQL Optimizer interface and understand some primary capabilities.
For detailed workflow tutorials, reference the SQL Optimizer User Guides.
(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%20Optimizer%20for%20SQL%20Server/10.0.1/User%20Guide/
After getting a query into SQL Optimizer (as described in Pt 1), you are ready to tune...
You will see this screen. Choose SQL Rewrite.
* (click images to enlarge)
* This example is shown in SQL Optimizer for SQL Server.
The top left displays the SQL statement that you will tune. The top right displays the plan. The bottom section shows the alternatives and execution statistics. At this point, some of this data will not yet be displayed until you initiate the SQL Rewrite.
First, note the options in the top right corner referred to as "Intelligence Levels" for both SQL Optimization and Index Generation.
It is here where you can choose to leverage one of five Intelligence levels. Simply put, the higher the level number, the more options that are included to check for ideal tuning but the longer it takes. Level 1 is best when you need a handful of alternatives within a few minutes while Level 5 will provide tens or even hundreds of alternatives but will likely take several hours. You can also choose 'Custom' instead of a pre-defined level and select specific optimization options.
Tip: To better understand what the icons mean and how to access key features, right-click next to the icons across the top and choose 'Show Captions'
Then, you'll see this....
The SQL Rewrite mode processes the statement in two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives each with unique execution plans for your original SQL statement. A SQL Server cost estimate is calculated for each alternative.
The second step executes the alternatives (after choosing your connection and confirming to 'Test Run All/Selected when prompted) and provides execution times and run-time statistics to more accurately identify the best SQL statement based on your performance criteria. The performance criteria can be chosen by clicking on the blue link shown below.
** Remember: Generating the execution statistics can cause overhead, so it is a good idea to run tuning sessions in a Development or Test environment.
You will see a trophy next to the best alternative chosen by SQL Optimizer based on your performance criteria.
You can choose to have Index alternatives generated too.
From here, you can view a side-by-side comparison of the best alternative (both SQL and Plan) to your original SQL Statement by choosing the 'Compare' tab.
You can even generate a report with the pertinent details.
Simply select the 'Report' tab.
This review of SQL Optimizer shows just some of its powerful capabilities. Learn more via the User Guide links provided above.
Download SQL Optimizer (via the instructions and links provided in Pt 1 of this blog series) and start tuning your SQL statements today!
