Zero to hero

The Mission
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
Rewrote queries
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.

Hero Status
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



About JodyWP

I am a SQL Server MVP based in Cape Town, South Africa. I am a DBA by heart and I am a huge sports fan love WP rugby and SPURS (football/Soccer). I also serve as a PASS Regional Mentor for the MEA region. In this role I assist other like minded people in growing their user groups or starting one.. I also serve on the SQLCape leadership committee . SQLCape is the local SQL User Group in Cape Town and meets the 2nd Tuesday of each month at the local MS office. I also serve as the organizer in chief for our local SQLsaturdays
Video | This entry was posted in DBA, SQL Tuning, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s