Organizations spend significant sums out of their budgets on improving and optimizing performance. These large expenses are a result of purchasing expensive hardware upgrades and additional software licenses, countless men-hours, paying external consultants and specialists, purchasing tools and services for monitoring and servicing the database, etc. Slow response times and users’ disconnections due to timeouts cause substantial user dissatisfaction, loss of income and loss of business opportunities. Our Qure solution can help you minimize all of that and save your organization valuable time and money resources.
Performance optimization and tuning of databases requires high levels of knowledge, experience and expertise. The Qure solution is an innovative software solution for automatic analysis, identification and resolution of slow performance issues in databases. Using Qure allows even a junior DBA, to quickly and effectively improve the performance of a database across all performance metrics – Duration, CPU and I/O. Qure is also valuable for preemptive performance optimization processes and is a very effective solution for availability maintenance.
Qure stands out in comparison to other solutions with its innovative holistic approach to performance optimization. Within minutes, Qure is installed and performs a database-wide analysis – no connection to the organization’s production servers is required. Qure analysis encapsulates the structure, schema, queries, database objects, indexes, data and activity performed on the database. Within a few hours, Qure generates pinpoint, prioritized performance optimization recommendations, including automatically generated scripts for immediate testing and implementation. These recommendations may include schema changes, query rewrites, general database configurations, addition / removal and rewriting of indexes and more. Additionally, Qure provides accurate measures of the performance improvements achieved for each and every query and database activity following the recommendations’ implementation.
Qure has been successfully deployed in many organizations. VOLARO will be pleased to perform a live demonstration of Qure on one of your databases, present its ease of use, communicate with your team the automatically generates recommendations and allow you to experience their unparalleled effectiveness.
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
Qure evaluates numerous parameters for each query and generates pinpoint recommendations for query rewrites, including correctional scripts!
3. Schema Optimization
Qure evaluates the scehma structure and can recommendt 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
Miscellaneous performance tuning recommendations, which may have a huge impact on performance
* Coding tecnhiques and best practices violations
* Maintenance optimization
* Hint usage
* Potentially erroneous queries
* Database and server settings
1. Download and install Microsoft .Net framework 3.5 SP1 from the Microsoft web site
2. Install SQL Server 2005 or 2008 client tools from the SQL Server installation CD.
3. Download the Qure installation file (Qure.msi) from the button above.
1. Use the SQL Server Profiler template provided with Qure installation (typically in “C:\Program Files\DBSophic\Qure\TraceTemplates\”) to perform a trace on the production database.
2. Restore a recent backup of the production database on the server to be used for the analysis.
Note: More information about “Preparing a Trace File Using SQL Profiler” and “Preparing the Analysis and Benchmark Server” can be found in the Qure User Guide. It is recommended that you read these sections carefully for best results.
1. Launch Qure for SQL Server.
2. Click Start New Analysis and follow the wizard’s on screen instructions. Three simple steps are required to set up the analysis:
a. Provide connection details for the analysis database.
b. Select the prepared trace files for analysis.
c. Configure two optional analysis settings.
Note: The analysis process may take from a few hours up to a few days to complete.
Use Qure’s ribbon to navigate to the batches, objects and recommendations windows to review the analyzed batches and Qure’s recommendations.
The main functionalities of these windows are:
1. The Batches window lists all batches and their aggregated resource consumption as analyzed from the trace and their actual performance improvements. For each batch, Qure provides high level summary information regarding its operations and interactions with other database objects, the batches’ script using sample parameter values, and links to its associated recommendations.
2. The Objects window enables you to view recommendations by objects instead of by batches. Use the objects tree on the left to select an object and view its operations and interactions with other database objects and the links to all its associated recommendations.
3. The Recommendations window provides full recommendation details including detailed information and correctional/revert scripts. The filter pane on the left enables you to focus on a subset of the recommendations and is automatically set in case you navigate to this window using the links from the Batches or Objects windows. Use the Clear Filter button to view all recommendations. In this page you can change the recommendations status from their initial Pending status to either Accepted or Rejected and apply the appropriate filters to easily manage recommendations.
Select the recommendations you wish to apply and perform a rigorous QA process. After the QA process has completed successfully, you can use Qure’s automatic Optimize feature to apply the accepted recommendations on the production database or any copy of it.
To apply recommendations:
1. Navigate to the Optimize window.
2. Review the list of accepted recommendations in the optimize queue.
3. Click Apply on database to launch the apply wizard and follow the on screen instructions.