What is SQL Doctor?
SQL Doctor is the newest tool offered from Idera that allows DBA’s and non-DBA’s alike to run a “check-up” on a SQL server and obtain a general health of the SQL server and recommendations on how to improve performance. It is a very powerful tool that when used correctly can save you a ton of time.
- Scripting is Done for Me – SQL Doctor will give recommendations based on what it finds and will rank them based on what will give the biggest performance gain. Based on the type of recommendation, a script might be created for me. For instance, adding an index on a table is presented as a recommendation and SQL Doctor will gives me a script to create the index. All I need to do is ensure it is correct and execute it to create the index! However, what if I add that index and it turns out to cause an unexpected result…well that brings me to number 2.
- Undo Scripting is Done for Me – Along with the create script, SQL Doctor also gives the ‘undo’ script. In the example described above, it will not only create the script to add the index, but it will also create a drop statement for the same index. A built in Control-Z! I recommend copying both scripts to a safe location. Remember that all of these script should be reviewed and tested before implemented in a production environment.
- Links to Resources – SQL Doctor displays links to several different technical websites which have a great deal of information and knowledge sharing available based on the recommendation. I like the fact that this allows a DBA like me to research the issue further and decide what the best approach is to take to fix the issue. This tool is recommending possible solutions and can be used as a stepping stone to point me in direction for a resolution.
- SQL Query Check – This is new to SQL Doctor 2.0. This piece allows me to test a query and based on the resulting execution plan, offer recommendations on how to improve its performance or make corrections. Based on a recommendation from SQL Doctor, I changed a T-SQL statement to move an indexed column out of a function which was performing a table scan. After the update, the query was performing a table seek and the results to be dramatically better.
- Block Recommendations – Say for instance I am not concerned with Security or Network recommendations, SQL Doctor allows me to block those recommendations from a checkup. The ability to customize a checkup by blocking certain recommendations can be very useful in helping to pinpoint areas I feel are causing trouble.
- Checkup History – The ability to be able to compare checkups from week to week can help me to create a baseline and see the improvements that have been made. It also allows me to go back in time to see if there were recommendations in the past that I might have overlooked or forgot about.
- Granularity of a Checkup – During the user Interview, I’m asked a few specific question to figure out where I am experiencing my problems, whether it is an application experiencing poor performance, a database performing poorly or perhaps I want to target a specific performance category. This will focus the Doctors checks only those areas of my concern.
- Emailing a Recommendation – Idera has added the ability to email a recommendation to someone. This is great feature to have, especially when I have a developer that is having an issue and I find a recommendation may help solve his problems. I just send him the recommendation directly from SQL Doctor.
- Save Recommendations to an Excel File – What if there are multiple recommendations that I want to send to a developer? SQL Doctor allows me the ability to extract the recommendations I want to an Excel spread sheet and save it locally. The file is formatted with everything including the code with the issue, scripts to fix it and script to undo it.
- Huge Time Saver – It is hard to calculate exactly how much time this tool has saved my company and I. Something that might have taken a few hours to solve can now take a matter of minutes. This now allows me more time to research and write blogs!
There is a new version for SQL Doctor scheduled to be released this month (April 2011). With this version, they have added some health checks that are related to Wait DMV’s and Query Checks. Granted, nothing is perfect, this tool can help get you closer to perfection.