Chat now with support
Chat with Support

Welcome, erwin customers to Quest Support Portal click here for for frequently asked questions regarding servicing your supported assets.

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

Welcome to SQL Optimizer

SQL Optimizer for SQL Server® maximizes current IT resources by establishing benchmarks and best performance standards within an organization. It ensures optimal SQL performance by identifying problematic SQL, generating alternatives, and testing SQL for maximum application performance.

Optimize SQL

SQL Optimizer analyzes your SQL statements and generates alternatives for improving performance. You can test these alternatives to identify the best SQL statement for your database environment. Optimizing SQL consists of the following functions:

SQL Rewrite Mode Description
Optimize SQL Statements

SQL Optimizer uses an Artificial Intelligence engine to execute SQL syntax rules that produce semantically equivalent SQL statement alternatives. See About SQL Optimizationfor more information.

Execute SQL Alternatives

SQL Optimizer executes statement alternatives to view their execution statistics. This provides execution times for identifying the best SQL statement for your database environment. See Test Run SQL Alternatives for more information.

Generate Index Alternatives

SQL Optimizer analyzes SQL statement syntax and database structure to provide index alternatives that improve performance. See About Generating Index Alternatives for more information.

Test for Scalability

Benchmark Factory™ simulates potential workload conditions to test SQL statement performance. See Test for Scalabilityfor more information.

Plan Control Mode Description
Generate Execution Plan Alternatives Generates execution plan alternatives for your SQL statements without changing the original source code.
Deploy Plan Guides Creates plan guides from the execution plan alternatives and deploys these plan guides to ensure optimal database performance.

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 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.

See About Optimize Indexes for more information.

Find SQL

Find SQL helps you locate the most resource-intensive SQL in your server. You can use Find SQL to inspect SQL captured by Plan Cache and SQL Profiler.

Find SQL from Plan Cache evaluates existing SQL performance by different statistics (such as CPU Time and Elapsed Time) from different areas (such as Top N SQL statements, Top N SQL Batches and Top N Database Objects).

Find SQL from SQL Trace allows you to extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profiler. You can identify the most resource-intensive SQL statements in your traces.

Additionally, you can:

  • Extract SQL executed in your server through Plan Cache or SQL Trace
  • Review execution statistics and query plans of the SQL
  • Check resource consumption in SQL Batch and Database Object levels
  • Optimize the performance of your SQL by sending them to Optimize SQL or Optimize Indexes
  • Save a SQL and its XML plan for others to review

Scan SQL

SQL Scanner identifies problematic SQL statements in your source code and database objects without execution. SQL Scanner then analyzes the problematic SQL statements and categorizes them according to performance problem levels. See About Scanning SQL for more information.

Manage Plan Guides

Manage Plan Guides allows you to manage existing plan guides with SQL Optimizer. Plan Guides improve the performance of queries. You can use plan guides to add or modify query hints before executing a query. SQL Optimizer also generates a plan you can apply to the query when that query is subsequently run.

Note: SQL Server supports plan guides from 2005 or above.

See About Managing Plan Guides for more information.

 

About SQL Optimizer

SQL Optimizer for SQL Server® maximizes current IT resources by establishing benchmarks and best performance standards within an organization. It ensures optimal SQL performance by identifying problematic SQL, generating alternatives, and testing SQL for maximum application performance.

Optimize SQL

SQL Optimizer analyzes your SQL statements and generates alternatives for improving performance. You can test these alternatives to identify the best SQL statement for your database environment. Optimizing SQL consists of the following functions:

SQL Rewrite Mode Description
Optimize SQL Statements

SQL Optimizer uses an Artificial Intelligence engine to execute SQL syntax rules that produce semantically equivalent SQL statement alternatives. See About SQL Optimizationfor more information.

Execute SQL Alternatives

SQL Optimizer executes statement alternatives to view their execution statistics. This provides execution times for identifying the best SQL statement for your database environment. See Test Run SQL Alternatives for more information.

Generate Index Alternatives

SQL Optimizer analyzes SQL statement syntax and database structure to provide index alternatives that improve performance. See About Generating Index Alternatives for more information.

Test for Scalability

Benchmark Factory™ simulates potential workload conditions to test SQL statement performance. See Test for Scalabilityfor more information.

Plan Control Mode Description
Generate Execution Plan Alternatives Generates execution plan alternatives for your SQL statements without changing the original source code.
Deploy Plan Guides Creates plan guides from the execution plan alternatives and deploys these plan guides to ensure optimal database performance.

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 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.

See About Optimize Indexes for more information.

Find SQL

Find SQL helps you locate the most resource-intensive SQL in your server. You can use Find SQL to inspect SQL captured by Plan Cache and SQL Profiler.

Find SQL from Plan Cache evaluates existing SQL performance by different statistics (such as CPU Time and Elapsed Time) from different areas (such as Top N SQL statements, Top N SQL Batches and Top N Database Objects).

Find SQL from SQL Trace allows you to extract SQL statements and their performance statistics from trace files or trace tables collected by SQL Profiler. You can identify the most resource-intensive SQL statements in your traces.

Additionally, you can:

  • Extract SQL executed in your server through Plan Cache or SQL Trace
  • Review execution statistics and query plans of the SQL
  • Check resource consumption in SQL Batch and Database Object levels
  • Optimize the performance of your SQL by sending them to Optimize SQL or Optimize Indexes
  • Save a SQL and its XML plan for others to review

Scan SQL

SQL Scanner identifies problematic SQL statements in your source code and database objects without execution. SQL Scanner then analyzes the problematic SQL statements and categorizes them according to performance problem levels. See About Scanning SQL for more information.

Manage Plan Guides

Manage Plan Guides allows you to manage existing plan guides with SQL Optimizer. Plan Guides improve the performance of queries. You can use plan guides to add or modify query hints before executing a query. SQL Optimizer also generates a plan you can apply to the query when that query is subsequently run.

Note: SQL Server supports plan guides from 2005 or above.

See About Managing Plan Guides for more information.

 

New in This Release


<<<<<<< HEAD

What's New in
® 10.1.1

=======

What's New in
SQL Optimizer for SQL Server® 10.1.2

>>>>>>> ca29f7d35af1d33471347d031d00093211d7f505
<<<<<<< HEAD

This release of includes the following new features and enhancements.

10.1.1

=======

This release of SQL Optimizer for SQL Server includes the following new features and enhancements.

SQL Optimizer for SQL Server 10.1.2

SQL Optimizer for SQL Server 10.1.1

>>>>>>> ca29f7d35af1d33471347d031d00093211d7f505

For a complete list of resolved issues in this release, see the 10.1.1 Release Notes at: Support - Technical Documentation.

10.1

For a complete list of resolved issues in this release, see the 10.1 Release Notes at: Support - Technical Documentation.

New Features in 10.0

 

  

Connect to SQL Server

This topic may not include a description for every field in the screen or dialog.

To create a connection

  1. Click .

  2. Review the following for additional information:

    Server Name

    Select the server alias for the database you want to connect to or click to browse.

    Note: The server alias is defined in the SQL Server Client Network Utility.

    Authentication

    Select whether to use Windows or SQL Server credentials to connect.

    Note: Login name and password are only required for SQL Server Authentication.

    Tip: Right-click a connection and select Remove Selected Entry to delete a connection.

 

Self Service Tools
Knowledge Base
Notifications & Alerts
Product Support
Software Downloads
Technical Documentation
User Forums
Video Tutorials
RSS Feed
Contact Us
Licensing Assistance
Technical Support
View All
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating