Qure Optimizer

DBSophic

Overview

THE WORKLOAD TUNING SOLUTION THAT AUTOMATICALLY OPTIMIZES MILLIONS OF QUERIES IN ONE GO

Tune millions of queries automatically with no load on production
Save years of expert DBA time by automating the tuning process
Save millions by avoiding unnecessary hardware upgrades
Accelerate enterprise applications to run 5 times faster or more

Features

SQL Server Tuning Made Easy

> Multiple workload sources: Qure Optimizer supports SQL Server workloads captured in one or more trace files or trace tables, as well as workloads captured by select 3rd party monitoring tools.

> Large workloads supported: Qure Optimizer supports SQL Server tuning with large workloads consisting of many millions of queries. Up to 10 GB of trace files or (for trace tables) 20 million events are supported.

> Fully automated: The workload tuning process runs completely unattended.

> No load on production: The working tuning process runs against a copy of the production database, restored onto a non-production server, thereby imposing zero overhead on the actual production system.

> Holistic balancing of performance benefits: Qure Optimizer tunes the entire workload holistically. When recommending any specific improvement (eg, “add an index”), Qure Optimizer considers the SQL Server performance effects of that recommendation across the entire workload, not just on an individual query.

Comprehensive SQL Server Performance Recommendations

> Recommendations: Qure Optimizer provides a wide range of detailed recommendations to improve SQL Server performance, including changes to the indexing scheme, SQL rewrites, schema tweaks and more.

> Detailed explanations and mappings: Every SQL Server tuning recommendation is accompanied by a detailed textual explanation, and all recommendations are ranked according to benefit. Recommendations are mapped to the SQL Server queries that they affect, and vice-versa.

> Executable scripts: Wherever possible (eg, for index recommendations and stored code rewrites) executable scripts are provided for deploying the selected recommendations to production.

> Index recommendations: These include recommendations to add, modify or drop specific indexes. ‘Drop’ recommendations apply only to redundant or duplicate indexes, not to unused indexes. Qure Optimizer’s advanced indexing algorithm recommends an optimal set of indexes that takes into account the entire workload, not just individual queries, so as to maximize performance benefits while avoiding over-indexing.

> SQL rewrite recommendations: Recommendations for SQL rewrites include a side-by-side comparison of the original SQL and the alternative syntax, as well as a detailed explanation. Both stored code and application-side code are supported.

> Schema and miscellaneous recommendations: Schema-level and other recommendations include potentially missing constraints, potentially unused columns, missing join conditions, SELECT * abuse, cursors not properly closed, and much more.

Before-and-After Performance Benchmark

> SQL Server Performance benchmark: Every recommendation is automatically benchmarked against the copy-of-production database. Qure Optimizer replays the workload, applies the recommendations, and then re-runs the workload to measure actual performance improvement.

> Performance metrics: For each query, the performance improvement achieved is shown via a range of metrics: Duration, Physical Reads, Logical Reads, CPU and Writes.

> Validation of recommendations: The benchmark also validates the functional correctness of the recommendations. For example, in the case of SQL rewrites, the benchmark verifies that the result sets returned by the original SQL and the rewritten SQL are identical.

> Predictive knowledge: Thanks to the performance benchmarks, the benefits of the selected SQL Server tuning recommendations are known and quantified in advance of deploying the recommendations to production.

SQL Server Tuning Recommendations - Deployment to Production

> Apply scripts: Executable scripts are provided for applying the recommendations to production, selectively or in bulk.

> Revert scripts: Together with every script for applying recommendations, a corresponding revert (undo) script is provided.

> Flexible deployment options: Recommendations may be deployed and tracked directed from Qure Optimizer, or externally via the executable scripts provided.

> Post-deployment comparison available: Qure Profiler, a free companion product, supports comparison of production workloads before-and-after the deployment of the recommendations.