Workload Tuning for SQL Server Applications

Qure for SQL Server is an innovative software solution that automatically analyzes a database workload and generates recommendations for optimizing its performance.

What Makes Qure Unique?

Qure provides you with accurate, tested, and optimal recommendations by using an automatic benchmark process that applies all recommendations to a copy of the production database and measures the actual impact of these recommendations on your database activities.

How Does It Work?

Using a copy of the production database and a standard SQL Server trace, Qure performs a comprehensive analysis of the database and the workload (production database activity). Qure then generates detailed recommendations, benchmarking these against the copy-of-production to accurately measure their impact. And, of course, Qure provides the SQL scripts needed to apply these recommendations to production. Recommendations address four main areas:

1. Smart Indexing

Qure recommends creating, removing and modifying indexes for best overall performance.
Qure evaluates all possible indexes for a query and then uses heuristics to combine the indexes for all queries, guaranteeing that:

Redundant indexes are eliminated

Indexes have the highest performance impact for as many queries as possible

The creation and dropping of indexes produces a significant, database-wide performance improvement

2. Query Syntax Optimization

The specific syntax a query uses can have a significant effect on its performance - even the best indexes won’t help if a query is written in a manner that prohibits optimal performance. Qure evaluates numerous parameters for each query and generates pinpoint recommendations for query rewrites, including the SQL scripts needed to implement them.

Query rewrite example

The following query retrieves all orders made in 1997 and uses the YEAR function, which may prevent efficient index use:

This query can be rewritten without the YEAR function, potentially enabling efficient index seeking. In this case, Qure provides the following alternative syntax recommendation:

SELECT *
FROM Orders AS o
WHERE YEAR(OrderDate) = 1997

SELECT *
FROM Orders AS o
WHERE OrderDate >= ('1997-01-01T00:00:00')

AND
OrderDate < ('1998-01-01T00:00:00')

3. Schema Optimization

The database schema is the foundation for all database operations. Qure evaluates the scehma structure and can recommend changing column data-types, adding or removing constraints and correcting potential design anomalies. Qure performs a comprehensive statistical analysis of the data and its usage patterns and may suggest modifying the schema to enhance performance.

4. Additional Optimizations

This category includes dozens of miscellaneous performance tuning recommendations, which may have a huge impact on performance.
For example:

Coding tecnhiques and best practices violations

Maintenance optimization

Hint usage

Potentially erroneous queries

Database and server settings