Zero to hero

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.

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

 

 

Awesome new tool , T-SQL Unscrambler…just had to share

As SQL Server professionals we all rely on up to date documentation from clients as a way for us to get to know their environments quicker. More often than not it is either outdated or they don’t have any, which makes our task a bit more difficult as we now have to go through their servers and databases individually which adds days to the engagements.

Recently I faced this situation again but I was prepared. I have recently started using GenisiOne Solutions T-SQL Source Code Unscrambler to help me understand and document client environments within hours instead of weeks. GenisisOne is also the first partner chosen by Redgate to be part of its SSMS Ecosystem.

Why I decided to use GenisisOne?

My client is in the financial services industry. They had a tight deadline and a very limited budget. After showing my client the results they handed me more work J

The database while not big is used by their main application.

925 Tables

832 other database objects including stored procedures, views and functions

And for good measure add one week to learn the environment and make recommendations on how to improve database performance … did I add no documentation?

ContinueUnlock Trial Account

Enter your product key and click on activate

Add server

Now enter your server name(s) then click add server. You will notice that we support both SQL and windows authentication. You will notice on the right side pane you are able to remove servers you no longer wish to document

ServerExplorer

In the left pane you can view the database objects and on the right you are able to see a graphical view of your table and its dependencies

Next expand the view tree and select a view. As you can see there are four tabs each giving more insight into your view(s) . 1st  is the data flow chart , its gives you a visual flow of your view as well as which objects make up the view

ViewExplorer

Next is the property tab showing you everything in a tabular form

PropertyView

I like the next tab , the summary tab its tells in English the story of you objects code !!! Awesome!! This save a hell of a lot time having to explain to people what the code does

Summaryviewcode

The next tab is the dependency viewer in a tabular form. It tell what makes up your object in my case I chose a view and as you can see it tells me which schema.table.collumn makes up my view.

DependencyView

Last but not least is the dependency diagram which gives you a high level view of your object. As you can see the each colour represents a different object.

DependencyDiagramview

On the top right hand side of the pane you have the option to export your object to either .PNG , PDF or SVG formats or if you do not want to export objects individually  you can document your entire database within seconds into PDF format. To do this simply right click on the database and click generate report as shown below.

SaveGenisis

Now select a location to save the pdf to and click save. At the bottom of the left pane you will see a progress bar indicating how long it will take to complete your document. Once completed (very very quick) you will get the download completed screen

Capture

After which you can now open the documentation for your entire database.

To help you get out of the block quicker please watch the demo video  below

I managed to finish within 5 days and make the required recommendations. I have also asked .NET MVP Dirk Strauss (T|B)  to give GenesisOne a go . His feedback “As a developer GenisisOne allows me to learn my client(s) environment fast allowing me to produce results faster.

What I would like to see

SSAS support

SSIS support

SSRS support

The ability to generate reports for entire instances instead of per database

The option to save generated files in a custom location

From all the tools that I have used in the past T-SQL Unscrambler is different as it is not a traditional documentation tool but a visual aid for SQL professionals.

Should have any suggestions on how we can improve on the product drop us a mail at

CodeVisualization@GenesisOneSolutions.com  . We love the SQL community and we want get involve by sponsoring your community drop us a mail at sqlrocks@genesisonesolutions.com

SQLSaturday Cape Town 06 September 2014

It’s that time of the year again. SQLSaturday Cape Town is around the corner 06 September with pre-cons on 05 September.  We will have 3 pre-cons  on SQL Perf tuning , SSIS internals and BI . For details on the pre-cons click here  This years event will take place at Protea Hotel Techno Park in Stellenbosch. To register for this year’s event click here

This years line up is awesome!!! Speakers include SQL Server MCM’s & MVP’s from around the globe  but most all local speakers which is awesome!! Check the schedule here

Hope to see you there

Jody

MVPLogo_ColorSQLSAT301_web SQLSAT327_web SQLSAT327_SPEAKING SQLSAT301_SPEAKING

 

%d bloggers like this: