SQLTech Consulting» Search Results » dbsophic http://sqltechconsulting.com Making SQL Server Consulting BETTER. Sat, 28 Jan 2012 23:29:54 +0000 en hourly 1 http://wordpress.org/?v=3.3.1 Searching for the “Qure” to your Performance Headaches? http://sqltechconsulting.com/2009/12/11/searching-for-the-qure-to-your-performance-headaches/ http://sqltechconsulting.com/2009/12/11/searching-for-the-qure-to-your-performance-headaches/#comments Sat, 12 Dec 2009 03:37:27 +0000 http://sqltechconsulting.com/?p=1714 Today I discovered a new product called “Qure“.  Qure is a SQL Server performance tuning tool and it requires only two inputs:  
  1. A trace of your production server activity
  2. A current backup of your production database  

I took a look at their tutorial video and it looks quite impressive.  Their documentation states that they focus on 4 areas for SQL Server Performance.  They are as follows:  

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

My Take on it: The product page states that they use a patented algorithm that suggests code modifications and places where indexes should be changed. I would like to review and compare, how much better it is to the Index Tuning Wizard.

2) Query Syntax Optimization –

Qure evaluates numerous parameters for each query and generates pinpoint recommendations for query rewrites, including correctional scripts!

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′)
 

My Take on it: This is just cool, no?  It makes changes to the code and then gives you the actual performance gains and time diferences?  That’s just amazing.

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.

My Take on it: This feature is like combining data profiling capabilities of SSIS with Database Tuning Advisor. This really makes me curious of exploring, how effective this feature is. If it works to my expectation, it can revolutionize database modeling.

 
4) Additional Optimizations

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

My Take on it: I would like to compare performance of this feature with that of Best Practices Analyzer tool. 

Conclusion

Overall this seems to be quite a powerful tool.  The cost of this product seems a bit higher than other vendors products that I have seen in the past so I’m not sure if there is any justification. I will review in depth in the coming days and I will post a followup so I can give a more diffinitive asnwer.

]]> http://sqltechconsulting.com/2009/12/11/searching-for-the-qure-to-your-performance-headaches/feed/ 0