Recently I was asked to take a look at a customer database and make recommendations on how and which code they can improve. The deadline for the assignment was extremely tight (3 days). I had to go through just over 7000 stored procs at look at the design of 1323 tables in the process.
Light bulb Moment
I remembered from a while back at a SQLCape user group meeting someone recommended Qure Optimizer. I decided to download the trail version and give it go. As I like tools that make my life a bit easier so I can spend more time on R&D.
What it does
What happened next was awesome. Why? Because I didn’t expect this level of detail Qure Optimizer provided. But before you start please do not run this on your live environment. Please run it against a backup of your live environment and then implement the changes afterwards with the scripts provided.
It restructured stored procs
Add new indexes
Modified existing indexes
Tweaked the schemas
Happy DB , Happy DBA
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 (in my case SQLSentry).
Large workloads supported: Qure Optimizer supports SQL Server tuning with large workloads consisting of many millions of queries. Trace files up to 10GB are supported.
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.
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.
By using Qure optimizer I was able to complete my tasking within a couple of hours and thus impressing the powers that be and them hiring me as to look at their other SQL environments (Consultant dream come true). What was a big bonus for me is the fact that Qure optimizer works with SQL Sentry’s monitoring tools.
Check it out
So buy yourself a from me to me gift this Christmas and checkout the Qure Optimizer video on YouTube