While attempting to optimize some stored procedures I was working on, I found an tool that its much better to do performance optimization on SQL.  This has helped me so much and I would gladly recommend this tool to anyone that wants to perform the review quickly and efficently.  This is my second review of a dbForge product, the first one being dbForge Schema Compare.  However, this below tool actually includes features to handle both Schema and Data compare as well, but that will not be the focus of my post.  I mainly am interested in this tool for the purpose of optimizing queries.  Coming from a background of using SSMS for almost 10 years, this blew me away.  The productivity improvements are so gigantic I can’t imagine working without this tool or a similar one.

dbForge Studio For SQL Server.  This tool is far superior in just about every way to SSMS (SQL Server Management Studio) when it comes to this task.  Overall it is a very refined and powerful tool that has a wide variety of uses.  I am going to focus on the query plan analyzer features in this post.

In a nutshell: Get it, now.

First of all, while writing your query, it will provide autocomplete and nicely capitalize your keywords.  I twill also auto generate the join for you and create nice short table aliases.  There are also built in snippets like CreateTable, CreateTempTable, CreateTrigger, etc.  When you mouse over the name of the table in this view, it will give you a popup with the column names and other useful information.

dbforge_autocomplete

Not only that, but you can actually edit the results if you like, directly here!  Compare this to having to open a “Edit top 200 results” window in SSMS where you get that ugly view that is had to write code in (it automatically adds brackets everywhere), to this one:

dbforge_results_editable

Talk about a breath of fresh air!  These two features alone make the whole process of working so much smoother and give you a much cleaner SQL result.  Beats having to paste your SQL here!  Also, if you have existing SQL, you can simply paste it and then right-click and pick “format code”.

Next, let us look at the query plan view:

dbforge_perf1

In this plan, we can see a visual display of the query plan with top operations highlighted in red to green by cost. You can see that its a nice clean display and shows you the information you need.  It allows you to visualize the query execution plan and pinpoint the issues.

There are several other screens in the “Profiler” mode which I will share with you:

In the next image, we can see the amount of CPU, I/O, and Operator / Subtree cost for each item in the plan:

dbforge_perf2

Here is a slightly more complex one below:

dbforge_perf3

If you have multiple executions, you can highlight two of them and see them compared:

dbforge_perf4

Comparing multiple query plans visually is quite a nice feature.  It allows you to take a look at both of them, with the Cost highlighted above.  If you run two queries in SSMS, you will also see the percent of time that each one took, but it doesnt allow you to stack them above each other like shown here.

In the following image, we can view and compare statistics on execution CPU, Memory, Disk.

dbforge_perf5

In the image above, you can see the difference in the number of rows, compile memory, compile time, cached plan size, etc.  You can also see the common options and what you have them set to.  It’s very handy that it colours them in red and green to quickly show you where you have improved.  Trying to do this in SSMS would take a lot longer and would lengthen the code->test->confirm cycle that optimizers do.

In the next image you can see the a different aspect of the results compared side by side in regards to actual execution time and CPU, and read/scan count on each of the tables.  This is very important as it will also tell you the cost of missing indexes.

dbforge_perf6

 

For all of these, you can rename the result name and give it a more meaningful name than “3:45:00 PM 7/1/2015”.

Other features which I have not got a chance to try yet but look very promising:

  • code analysis rules – look for errors and warnings.  You can use this to enforce certain standards in your code quality across the team if you like.
  • table designer – far superior table designer to SSMS.  You can switch back and forth from the visual editor to the T-SQL editor.  You can also preview ALTER table script and rebuild the table without losing data.
  • database diagram editor – Visualize database structure, track relations, print out diagrams, and edit objects directly. choose the syntax you like (IE or IDEF1X).  You can create logical contaniners to group tables together.  In the print dialog it gives some handy options such as displaying certian markup above the tables.    Also far far better than SSMS.
  • schema compare and sync – compare two servers and create a delta script based on the changes.  You can either execute or save the script it generates (patch script).  The sync scrypt can also include making  a backup first.
  • data compare – like schema compare, but you can actually check the data based on its key.  This can be helpful for verifying proper deployment was done.
  • t-sql debugger – similar to SSMS, you can step through your code line by line.
  • data export/import – from csv, excel, access, xml, etc.
  • data generator – to create test data, you can fill up tables with a bunch of values say dates, text, numbers, etc.
  • event profiler – profile events such as query locks and blocks for use in diagnosing issues or improving performance.  Profiling events does not cause as much load as running a full SQL Server profiler.
  • master/detail browser (i.e. Class and Students)
  • pivot table (visualizer)
  • create simple data reports

Overall, with a price that is very reasonable for the quality of product, $499 for the Professional edition, along with competitive discounts, I would highly recommend this product.  If you are not interested in some of the features such as Data Compare, Schema Compare,  Reporting, etc, you can get a more basic version that will still do the job and not hurt your bottom line.

Disclaimer: I will receive a free license for writing this review.  Note, license or not, I would still 100% stand behind this product, especially because I used the trial and loved it, and the prices are very reasonable for the quality of product you receive.

Here it is again: DBForge studio for SQL Server (https://www.devart.com/dbforge/sql/studio/)