Copy Execution Plans
To copy an execution plan
| » | Right-click the execution plan and select Copy. |
Note: You can paste execution plans displayed as a Tree Plan or Plain Language Plan in text or bitmap format. Applications such as Microsoft Word allow you to choose the format using the Paste Special option. You can use text applications such as Notepad to paste execution plans in text format or graphic applications such as MS Paint to paste execution plans in bitmap format.
Scan SQL
SQL Scanner helps identify problematic SQL statements in your database environment by automatically extracting statements from your source code. SQL Scanner retrieves and analyzes the execution plans for the extracted statements and classifies them according to complexity. You can then optimize statements classified as problematic using SQL Optimizer. SQL Scanner can extract and analyze statements from the following:
| Database Objects | Database objects with embedded SQL statements which includes stored procedures, triggers, views, rules, and defaults. |
| Single Command Line Dynamic (SCLD) Files and Database Objects |
Database objects and files that contain SCLD SQL statements including Java or Visual Basic source code. Notes: SCLD SQL statements are the only type of dynamic SQL statements that SQL Scanner can extract directly from source code. |
| Microsoft SQL Server Profiler Files | Trace files and tables created using Microsoft SQL Server Profiler. You can use SQL Server Profiler to capture SQL statements while the applications are running. |
| Text/Binary Files | Text and binary files that contain embedded SQL statements. |
| COBOL Files | COBOL source code that contains embedded SQL statements. |
|
Notes: If your application generates dynamic SQL statements at run time, use SQL Server Profiler to capture the statements and store the output in a trace file or trace table. Afterwards, use SQL Scanner to extract and analyze the captured statements. | |
About Scanning SQL
SQL Scanner helps identify problematic SQL statements in your database environment by automatically extracting statements from your source code. SQL Scanner retrieves and analyzes the execution plans for the extracted statements and classifies them according to complexity. You can then optimize statements classified as problematic using SQL Optimizer. SQL Scanner can extract and analyze statements from the following:
| Database Objects | Database objects with embedded SQL statements which includes stored procedures, triggers, views, rules, and defaults. |
| Single Command Line Dynamic (SCLD) Files and Database Objects |
Database objects and files that contain SCLD SQL statements including Java or Visual Basic source code. Notes: SCLD SQL statements are the only type of dynamic SQL statements that SQL Scanner can extract directly from source code. |
| Microsoft SQL Server Profiler Files | Trace files and tables created using Microsoft SQL Server Profiler. You can use SQL Server Profiler to capture SQL statements while the applications are running. |
| Text/Binary Files | Text and binary files that contain embedded SQL statements. |
| COBOL Files | COBOL source code that contains embedded SQL statements. |
|
Notes: If your application generates dynamic SQL statements at run time, use SQL Server Profiler to capture the statements and store the output in a trace file or trace table. Afterwards, use SQL Scanner to extract and analyze the captured statements. | |
About SQL Conversion
SQL Scanner retrieves execution plans when it extracts SQL statements. If SQL Scanner cannot retrieve the execution plan, it attempts to apply a SQL conversion to render the statement as a valid standalone statement. Click the following to display more information for each type of conversion SQL Scanner attempts to apply:
COBOL ConversionCOBOL conversion searches for three syntax items allowed in COBOL that are not valid SQL syntax. Review the following for additional information:
| Syntax Item | Conversion |
|---|---|
| A dash or minus in a variable name | If a variable name contains a minus sign (-), then it is replaced with an underscore (_). |
| Comments in the middle of a SQL statement | If the seventh column of the line is an asterisk (*), then the complete line is recognized as a line comment. |
| The ]] (double right square bracket) as the concatenate symbol |
If two right brackets (]]) are used to concatenate column names, they are replaced with a plus sign (+). For example: Original SQL statement SELECT * FROM EMPLOYEE WHERE EMP_ID > :employee-id AND ENAME]]JOB = :name-job
After conversion SELECT * |
|
Notes:
| |
SQL Server generates a different execution plan for a SQL statement if that statement is used inside a cursor declaration. When SQL Scanner finds a SQL statement used inside a cursor declaration with a FOR READ ONLY or FOR UPDATE clause, it embeds the retrieval of the execution plan inside the cursor declaration. This ensures that the execution plan SQL Server generates matches the execution plan for the same statement not used inside a cursor declaration. This conversion does not affect the SQL statement.
Note: If you send a SQL statement used inside a cursor declaration to SQL Optimizer from SQL Scanner, the Cursor Setting window displays automatically. See Optimize SQL Used in Cursors for more information.
Some source code uses a question mark (?) to define external parameters. SQL Scanner adds a number to these external parameters so each parameter in the SQL statement has a unique name. For example:
Original SQL statement
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?
AND EMP_NAME = ?
After conversion
SELECT EMP_ID
FROM EMPLOYEE
WHERE EMP_ID = ?1
AND EMP_NAME = ?2
Some front-end tools allow you to use the INTO clause of a SQL statement for variable assignment. Since this is incorrect SQL syntax for the INTO clause, SQL Scanner comments INTO clauses with more than one variable.
Original SQL statement
select EMP_ID, EMP_NAME
into a, b
from EMPLOYEE
After conversion
select EMP_ID,
EMP_NAME /* into a, b */ /* Commented by SQL Scanner */
from EMPLOYEE
Trigger operations store deleted and inserted records in two logical tables that cannot be referenced outside the trigger body. In order to optimize SQL statements used in a trigger, SQL Scanner uses two temporary tables to simulate the tables for deleted and inserted records. For example:
Original SQL statement
INSERT INTO EMP_SMALL (EMP_ID,
EMP_NAME,
EMP_SALARY)
SELECT A.EMP_ID,
A.EMP_NAME,
B.EMP_SALARY
FROM EMPLOYEE A,
Inserted B
WHERE A.EMP_ID = B.EMP_ID
After conversion
SELECT *
INTO #inserted_simulation_table
FROM dbo.EMPLOYEE
WHERE 1 = 2
INSERT INTO EMP_SMALL (EMP_ID,
EMP_NAME,
EMP_SALARY)
SELECT A.EMP_ID,
A.EMP_NAME,
B.EMP_SALARY
FROM EMPLOYEE A,
#inserted_simulation_table B
WHERE A.EMP_ID = B.EMP_ID