立即与支持人员聊天
与支持团队交流

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

Use Temporary Tables

Creating temporary tables to extract data from permanent tables is a technique you can use to improve SQL statement performance. When SQL Optimizer rewrites a SQL statement, it may use this technique to generate some of the alternatives. The following is an example of an original SQL statement rewritten to use temporary tables.

Original SQL

SELECT *

FROM A 

WHERE A.KEY IN (SELECT B.KEY  

FROM B)                   

 

SQL Alternative

SELECT DISTINCT COL1 = B.KEY

INTO #TEMP1  

FROM B  

 

SELECT *

FROM A, #TEMP1  

WHERE A.KEY = #TEMP1.COL1 

Notes:

  • Since temporary tables are session related, you need to create temporary tables for each new session.

  • SQL Scanner uses separate SQL Server sessions than SQL Optimizer. Therefore, temporary tables you create in SQL Scanner are not available when you send a statement to SQL Optimizer.

  

Related Topics  

Use Temporary Tables in Scan SQL

Scan Source Code with Temp Tables

About Temporary Tables

Creating temporary tables to extract data from permanent tables is a technique you can use to improve SQL statement performance. When SQL Optimizer rewrites a SQL statement, it may use this technique to generate some of the alternatives. The following is an example of an original SQL statement rewritten to use temporary tables.

Original SQL

SELECT *

FROM A 

WHERE A.KEY IN (SELECT B.KEY  

FROM B)                   

 

SQL Alternative

SELECT DISTINCT COL1 = B.KEY

INTO #TEMP1  

FROM B  

 

SELECT *

FROM A, #TEMP1  

WHERE A.KEY = #TEMP1.COL1 

Notes:

  • Since temporary tables are session related, you need to create temporary tables for each new session.

  • SQL Scanner uses separate SQL Server sessions than SQL Optimizer. Therefore, temporary tables you create in SQL Scanner are not available when you send a statement to SQL Optimizer.

  

Related Topics  

Use Temporary Tables in Scan SQL

Scan Source Code with Temp Tables

Create Temporary Tables

You can use the Temp Table Manager window to create temporary tables in SQL Optimizer or SQL Scanner. The Temp Table Manger supports the following SQL statements that create or modify temporary tables:

  • SELECT INTO
  • CREATE TABLE
  • CREATE INDEX
  • DROP INDEX
  • DROP TABLE
  • INSERT
  • UPDATE
  • DELETE

SQL Optimizer or SQL Scanner automatically drops the temporary tables you create using the Temp Table Manager when you quit the program or select a new connection.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To create a temporary table

  1. Click in the Alternative Details pane of SQL Optimizer or the Job List pane of SQL Scanner.

  2. Review the following for additional information:

    Temp Table Manager Description
    SQL Script Editor

    Enter SQL statements to create temporary tables.

    Tip: Click to open a file with your SQL statement.

    SQL Script Displays the SQL script for temporary table you select.
    Temp Table List

    Displays a list of temporary tables for your current session.

    Tip:Click to drop all temporary tables for your current session.

    Note: If the selected SQL statement uses a variable, the Set Bind Variables window displays so you can define the variable. See Set Bind Variables for more information.

  

Related Topics  

About Temporary Tables

Use Temporary Tables in Scan SQL

 

 

Optimize Indexes

Optimize Indexes analyzes a SQL workload or any group of SQL statements and determines the best index set for the workload or group of statements. You can instruct SQL Optimizer to gather SQL from an application workload during a specified time period. SQL Optimizer evaluates your SQL workload and provides you with the best set of indexes to optimize database performance for that workload. After this evaluation process is complete, you can review the results and then select the new indexes you want to save and test.

You can instruct SQL to gather a SQL workload from any of the following sources:

  • Scan Code — Scan SQL statements from file, database object source, or clipboard.
  • Plan Cache — Collect SQL from the Plan Cache
  • SQL Profiler — Collect SQL from a trace table or trace file.
  • Spotlight Statistics Repository — Collect SQL from Spotlight Statistics Repository.

After collecting the statements, SQL Optimizer provides you with the execution plans and run-time statistics. Select the statements for which you want to optimize indexes. After running the evaluation process, review the results to determine the performance improvements.

Considerations

Things to consider when using Optimize Indexes:

  • When analyzing workloads, SQL Optimizer uses proprietary, modified genetic algorithms (patent pending) to continuously improve its results. Therefore, the longer the evaluation process runs, the better the result.
  • While performing a search for new indexes to optimize performance, SQL Optimizer updates its recommendations over time to reflect any changes in data distribution or configuration.
  • You can schedule the evaluation process to run once, continuously, or periodically at defined intervals.
  • You can stop the process manually or configure it to stop automatically when specific goals are met.
  • The evaluation process can be paused and resumed.
  • Optimize Indexes' evaluation of SQL and search for new indexes is a low-overhead process.
  • Select, update, delete, insert, and merge statements are supported.
  • Only one index search process can be active at any one time. However, multiple SQL workloads can be opened and reviewed concurrently.

About the Evaluation Process

When evaluating your SQL workload, SQL Optimizer creates virtual indexes. No indexes are created, so this process does not significantly affect database performance.

Note: To optimize indexes for a single SQL statement, use Optimize SQL. See About Optimizing SQL (SQL Rewrite) for more information.

 

Related Topics

Optimize Indexes for Spotlight Statistics Repository Workload

Optimize Indexes for Plan Cache Workload

Optimize Indexes from Scanned Code Workload

相关文档

The document was helpful.

选择评级

I easily found the information I needed.

选择评级