Review: Idera SQL Doctor

Recently, a co-worker practically slammed me up against the wall, exclaiming “You have to check out this new tool, right now!” The piece of software he was so excited about was Idera’s SQL Doctor. Based on this assaultrecommendation, I decided to take a little time & look the software over.

SQL Doctor, as the name implies, is a diagnostic tool. It runs a set of best practice rules against your server, your databases and your code. As the rules are executed, your system’s compliance with these best practices is evaluated and an interactive report is generated. With the report you can drill down on various aspects of your system to see where you may have gone wrong.

All that sounds very clinical, just laid out like that. But the fact of the matter is, if you have a lot of inherited systems, or systems that have not been maintained, designed, or developed as well as they should have been, you know you have problems all over the place. How exciting would it be to get a pretty fast evaluation of the systems? Wait. Don’t answer that yet. What if, after the evaluation, a set of reports was available? Hang on. It gets better. What if the reports included a pretty decent set of suggestions on how to resolve the issue? Yeah, that’s right, suggested solutions.

I know some people reading this are tuning monsters who won’t benefit from a piece of software like this. But a lot of you are not. For those, here’s how it works.

First, you connect to the server you’re interested in. Once connected, SQL Doctor will ask what type of evaluation you’d like, a health check or some type of system slow-down, as shown below:

FirstQuestion

For this example, I’ll just pick the health check. SQL Doctor then asks if this is a production system or not. Then it asks what kind of system, OLTP or not, and then it runs the evaluation. The evaluation of the rules is somewhat time consuming. Once it is completed, a new window showing the health of the system is displayed:

ServerHealth

And this is where things get interesting. You can see the server name, the state of health, etc., but the good stuff is down near the bottom, the recommendations. On this particular server, chosen because I knew it would list interesting results, you can see 509 total suggestions, 427 Query Optimization issues, 48 Index Optimization issues, 2 network issues and 2 memory issues. You can use the tabs to look directly at the recommendations or you can click on the recommendation categories. Clicking on the Query Optimization category the list of optimizations is shown:

Optimizations

The optimizations are listed by severity, meaning, the damage they can cause to your system. At the very top are two instances of functions being used on columns in the WHERE clause of queries. I can’t drill down and show you the details on this system, but what you get is a precise listing of the procedures and the specific location where the problem exists along with a recommendation for how to fix it.

This is momentous. Basically, what you’ve got, is the ability to capture the low hanging fruit. The easy, but painful, stuff that people who optimize queries all the time are fixing, can be found, fairly quickly, and you don’t have to be a tuning expert to make this happen. There’s quite a bit more to Idera SQL Doctor, but this is the main point. You can also flag certain optimizations, marking them for others so that they know what you consider to be a priority. You can block optimizations if you either know about them or don’t care about them. There’s quite a bit of control possible through the tool. But, I can’t emphasize the main point enough. You can get an automated basic health check on your system that will suggest the best ways to fix the problems that it finds.

There are recommendations I’m not crazy about. For example, it flags UNION operators as a potential performance problem. Yes, they might be, and if you could use UNION ALL instead, performance would improve, depending on the query we’re talking about, but having this one poking me in the eye over & over… well, at least around my shop, it’s a candidate for getting turned off. Further, the explanation of why it could be problematic is very short. It just doesn’t provide enough information for people to make up their mind based on a full set of data. I’m also a bit nervous (shocked and apalled are almost applicable) that they’ve included index usage stats in a tool for less experienced DBAs. Yes, that index may not have been used for the last six months, but tomorrow, the CIO is running his favorite report that he runs once every six months, and it had better be lightening fast… but you just dropped the index that the report depends on because some piece of software told you it’d be OK… oops. In short, there are areas within the software that could be improved.

Most of the TSQL recommendations I saw were excellent. I already mentioned the functions making arguments non-sargeable and there are more good ones, use of SELECT *, unfiltered deletes (I’ll be talking to those developers real soon), ANSI settings that affect performance, differences between estimated execution times and actual times (neat use of DMOs), and more. In short, I think there’s one heck of a lot more good here than bad.

Is SQL Doctor magic? Are all your performance problems going to disappear over night? No, absolutely not. If you’ve made poor choices in database design, if you’ve got stacks and stacks of bad code, this tool is not going to swoop in to clean up your mess. You’re still going to have to do a lot of hard work, depending on your situation. Further, don’t take the suggestions of any tool (or some yahoo on the web for that matter) as messages from the gods. Evaluate and test the suggestions, independently.

However, the workload for tuning your systems was just reduced. Because there are a lot of problems, possibly even the majority, that are very clearly defined, that can be found by running a search routine, that absolutely do hurt the performance of your systems, badly. Those problems will be identified by Idera SQL Doctor which will enable you to fix them, in a much faster and more efficient manner. That’s a win.

12 thoughts on “Review: Idera SQL Doctor

  • You wrote “You can use the tables to look directly at the recommendations or you can click on the recommendation categories.” Do you mean the recommendations are exposed in a database table? Because I tried out there tool and really wanted to be able to view the results differently. If I could query a table or view that would be great.

    • Grant Fritchey

      You know I must have read that post three times looking for typos. Ah well. I typed “tables”, I meant to type “tabs.” Sorry about that. I’ll update the text (which will confuse everyone who reads the comments). I didn’t meant to cause either confusion or excitement. The data is stored in files on your system in a binary format. At this point, you can’t open them to look at the data in other ways. Sorry.

  • Glenn Thompson

    I was thinking about trying SQL Doctor and now I know I should. Should I install it on a machine other than a production box?

  • Grant Fritchey

    Good luck with it. No, I wouldn’t install it on a production system. I would run it against production systems, but only during off-hours. I wouldn’t run it at the height of the business day. I suspect your users might notice.

  • Thanks for the clarification. It seems like a great product but it would be nice if the presentation of the suggestions was a little easier to manipulate – maybe an export feature or something.

  • Grant Fritchey

    I’d suggest you get in touch with Idera. They have a forum and I’ve found them to be pretty responsive to requests. Let ’em know what you’d like. You might improve the thing for all of us.

  • I am one of the developers working on SQLdoctor and I wanted to stop by and personally thank Grant for taking the time to write this blog post about Idera SQLdoctor.

    I also wanted to encourage everyone to continue to provide feedback on what you need SQLdoctor to do for you. Please feel free to visit the SQLdoctor development blog at http://sqldoctor.idera.com and post comments on what you would like to see in the next release of SQLdoctor. All of us at Idera look forward to helping you find and solve problems in you SQL Server environment and are happy to hear from you.

    As Richard pointed out in his comments, he requested the ability to export recommendations from SQLdoctor and I am pleased to say that this was a feature that we implemented in the release version of the product. The following blog post covers the steps needed to export the recommendations: http://sqldoctor.idera.com/general/export-recommendations-to-excel/

    We are now in the process of researching the features that we want to implement in the next major release of SQLdoctor. This will include the ability to ‘rollback’ an optimization, along with extending the current ‘flagging’ logic in the product to include options such as ‘To-Do’, ‘Completed’, etc.. Out of all the features we are considering for the next release, I am the most excited about the new recommendations we are researching. Once you have a product that generates somewhere around 150 different recommendations, it becomes very difficult to find new recommendations to add to the product.

    If there is something that you want included in the next release of SQLdoctor, now is the time to let us know!

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.