DBSophic

A new feature in Qure Optimizer v2.0 - the "save as workbook" option exports most of the analysis information to an excel report. Please note that this option is only supported in Office 2003 or later.

Starting from version 2.0, Qure Optimizer enables multi database analysis.

  • Analyses saved to a file repository are limited to a maximum of 3 databases per analysis.
  • Analyses saved to a SQL Server database repository are limited to a maximum of 10 databases per analysis.

Qure Optimizer supports analyzing databases that use SQL Server 2005/2008/2008R2/2012 and have a compatibility level of 90 or higher. If your database was migrated from SQL Server 2000 and its compatibility level was left at its previous setting of 80, you can try and change its compatibility level to 90.

Note that changing the compatibility level may fail, subject to the backward compatibility issues as published by Microsoft in the Books On Line article "SQL Server 2005 Database Engine Backward Compatibility".

  • Server side cursors and prepare/exec statements are partially supported in benchmark. These include sp_cursoropen, sp_cursorprepare, sp_cursorprepexec, sp_prepare and sp_prepexec. Qure Optimizer extracts the query from the string literal of these commands and treats it as if it were a regular query. The fetch commands are ignored and the benchmark will show the improvement gained only for the query.
  • Schema column modifications may fail to apply in benchmark if any objects, other than indexes or statistics, are dependent upon the column. You should manually drop any referencing objects in order to modify the column and recreate them after modifying the column properties.

The Qure Suite parser does not support parsing and analysis of the following types of queries: Dynamic queries in server side objects A less common query writing technique involves dynamically creating the query as a string literal, and then using EXEC or sp_executesql to execute the string containing the query. If this dynamic construction of the query is done at the application tier, Qure Suite captures the final constructed query from the trace and then parses and analyzes it. If the dynamic construction of the query is done in server side objects (such as stored procedures, triggers or functions), Qure is not be able to follow its construction or execution, and is therefore unable to analyze it. The benchmark process however, executes and records batches that call the containing object, either directly or indirectly. Extended procedures and CLR objects The Qure Suite does not analyze OLE automation (extended procedures) and CLR objects in the database. All referenced objects in the queries that use OLE or CLR objects are analyzed, except for the expressions or clauses containing references to either the OLE or CLR objects. Parameterized sp_prepare statements XML X-queries The Qure Suite does not parse X-Query expressions and does not analyze any query that contains an X-Query expression. Column name resolution limitations The following column aliasing syntaxes are not supported: > Aliases provided in an alias list. For example, SELECT * FROM () AS X (Alias1, Alias2…) > Aliases in DELETE and UPDATE using the proprietary additional “FROM” SQL Server syntax. > Aliases derived from sub queries that use set operators such as Union, Except and Intersect. For example, SELECT Column1 FROM ( UNION ) AS X. > Queries that contain any of the above syntax constructs are partially analyzed. ODBC Date and time formats ODBC specific Date & Time formats are not supported. For example, “{d '1998-02-23'}”

The version of the installed client tools must be the same or of a higher version than the version of the Profiler or server that generated the trace file.

A trace file generated by Profiler 2008 or a server side trace of SQL Server 2008 cannot be used if Qure Optimizer is installed on a computer with SQL 2005 client tools.

* NOTE: Applies to analyses saved to a file only. Analyses saved to a database repository are limited to 10GB when using SQL Server express or by available disk space.

Qure Optimizer uses an internal repository (analysis data file) for storing all analysis and benchmark information. The repository has an embedded limit on its size which cannot exceed 4GB.

As a result, the trace analysis process stops analyzing the trace files if the repository reaches a size of 2 GB, in order to leave sufficient space for the benchmark and other data. This means that only batches captured up to that time are analyzed. Qure Optimizer displays an informational message if this size limit is reached.

If your system contains an exceptionally large number of unique batches, the benchmark process may exhaust the available space of the repository. In this rare case, the benchmark stops and an informational message is displayed. Benchmark results for batches benchmarked successfully up to that point are available.

Qure Optimizer uses complex AI algorithms to analyze your database and its activities. On complex databases with many objects and a large number of unique activities, the analysis and benchmark processes may take considerable time and resources to complete. To avoid the potentially very long duration of these processes (as much as a few days), the following limitation are imposed by Qure Optimizer:

  • The time limit for the trace analysis, which is typically the longest phase of the analysis process, is set to 20 hours. After 20 hours, the trace analysis is stopped and only batches analyzed up to that point are used to generate recommendations.
  • The benchmark uses a database connection with a timeout limit of 600 seconds. This means that any batch that takes longer than 10 minutes to complete, either before or after the recommendations are applied, will fail the benchmark.