Category Archives: SQL Doctor

Idera SQL Doctor First Look

I recently saw a tweet about Idera’s new SQL Doctor tool that is currently in beta. This differs from other tools you may think of like Diagnostic Manager. DM and other tools like it gather some of the same information but are geared for real time alerts. This is more like the Best Practices Analyzer. It takes a look at several key points on your SQL Server instance and the OS. It makes recommendations on how to improve any problems it identifies. You may ask, does this do anything I can’t do on my own? No, it doesn’t. If you are doing this in house with your own tool set you may not need this, or any other tool like it. To be honest, you aren’t the target audience for SQL Doctor. This is really designed for shops that ether don’t have enough DBA’s to watch everything, or don’t have a full time DBA at all. That isn’t to say it has no use even in a highly monitored and optimized shop. I picked a horribly abused development box and let SQL Doctor tell me just how bad things were.

Idera_sql_doctor1

The interface is clean and simple. First thing we have to do is pick a server and connect.

Idera_sql_doctor2

Next it starts the interview process. It isn’t extremely in depth, and it isn’t suppose to be.

Idera_sql_doctor3

I would be a little worried if I didn’t know if the server was in production or not. But, the person running SQL Doctor may not be the DBA.

Idera_sql_doctor4

I’m sure they wrestled with this one. How do you describe OLTP workload vs. OLAP workload if the person running the tool doesn’t know if the server is production or not?

Idera_sql_doctor5

That’s pretty much all the questions at all. You can change some things in the settings tab like the databases

Idera_sql_doctor6

I’d say the time estimate can be a bit optimistic. It took over an hour to run on this box. The progress bar is a tad misleading as well since it spent the bulk of that time basically at 99% complete.

Idera_sql_doctor7

Looks scary! Some of these problems are. They do scale them according to problem and push the less likely stuff to the bottom for you. It is a lot of information to look at though. 600 recommendations, wow. Lots of index issues and query issues. Time to dig in.

Idera_sql_doctor8

They do a good job of grouping issues together. Like other tools in this category, sometimes the recommendations aren’t 100%. They throw the disk queue around quite a bit but not disk latency, which is really what we are worried about here. It does pick up some pretty nifty stuff that other tools I have looked at don’t really catch. It warned me about a NIC setting being optimized for file sharing and that it could put memory pressure on the box. It also hit on some other fun stuff at the OS level for memory like lock pages in memory that I bet plenty of folks may not be using. I was pretty happy with the level of OS recommendations over all. I am concerned with some of the recommendations that seem to be out of date. It recommended a file per CPU core on tempdb for example. I would recommend that they get a few more SQL Server folks to look at the recommendations and submit tweaks to them. It also will script the SQL Server changes for you or point you to Microsoft Kb’s to get them fixed up. Again, my worry with this kind of tool is someone is just going to blanket run every recommendation, and some of them like disabling the CLR could be detrimental if that feature is on for a reason. I know its hard to get everyone to read the warnings. Lets be honest though, if they don’t know if it is a production OLTP system they probably don’t know if the CLR is on for a reason ether.

I have to say, for a beta and for a tool of this type, I am impressed. With a little extra work I think it will be a worthy addition to any shop that is lacking in deep SQL Server expertise.