SQL Optimizer for SQL Server 10.1.2 - User Guide

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:

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:

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

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

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

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.