Mar 21 2011

Communication

It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It’s a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap.

Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic about how they did things. They slowed me down. They got in the way. When I had problems they were slow to respond, unless the problems were on production. I know I even instigated a few fights with them in an attempt to get them to move the way I wanted (hard to believe, I know). Then came the day when I shifted over to all database work.

Suddenly, I’m responsible for making sure the production system stays online and that the data is readily available to the business. Now I’m slowing down development, because I want a chance to review their design and validate their code to ensure it’ll work well and not affect production. Now I’m acting as a gatekeeper to prevent unauthorized access to the systems or at least keep people from making any of the 10,001 simple errors that could impact production. Now when a developer wants something fixed in dev, I’m the guy telling them they have to wait because something in production is wonky. And yeah, I’ve instigated fights from this side as I tried to get devs to understand that simply delivering code is not enough and that data persistence is there for a reason (again, shocking I’m sure).

Remember, both of these groups are more right than wrong, and both are working towards that common goal, value for the business. But they really don’t get along. What’s more, what they work on and how they work with it is frequently at odds. Ever heard of the object-relational impedance mismatch? How about the concept that you don’t have a database, but a persistence layer? What about managing data integrity within the application (one of my abiding favorites)? Never heard of those terms or concepts? Then you’re probably a database specialist and you’re probably not talking to your developers. If they haven’t already, they’ll soon be introducing an Object Relational Mapping tool to your enterprise. Best of luck.

A lot of these communication issues probably can’t be solved, but I know of one place where most database specialists are not communicating well with their dev teams, and database guys, it’s your fault. Source Control. Do you think of the structures and procedures within your database as code? You should, because, to a large degree, it is. The Data Definition Language (DDL) calls that make up your tables, views and procedures are code. That code needs to be checked into a source control management system. There, the individual objects can be versioned and managed. There you can create labeled releases of your code. There you can branch your code to create alternate development or support streams that contain variations of your database. There you can merge changes from multiple users and branches into a single main source for deployment to production. There you can keep your database directly in sync with application developers code.

Did you catch that last one? You can become more tightly coupled with your development team. Best of all, you can do this using their tools and their language. This is the communication problem I want you, the database professional to solve. Very few of us database types are using source control these days. This, despite the fact that there are fantastic tools and methods under development from different vendors that directly address the issue of getting and keeping database code within a source control system.

Years ago, when I first made the jump to databases, I was appalled that I couldn’t keep my code in source control. Then, as I worked more and more with databases, despite the problems, I abandoned the idea of managing the code in source because, frankly, it was way too hard. But several years ago new tools appeared on the market to make it possible (if still somewhat painful) to get the database into source control. I’ve been working that way for years now. It has completely eliminated one of the many problems I used to have with developers. They now know that my code is stored with theirs. That my versions are their versions. That their labels are my labels. That we branch the code together. It’s taken completely for granted, and we share a common language about change and deployment.

This has not solved every problem or conflict with database teams I’ve worked with. It has eliminated a source of friction. It has increased communication. It’s something that I could do, and you can do, to get a little closer to your development team. Not to mention the fact that you will now have your databases in a known state, all the time, that you’ll be deploying from a single location, that you can manage access to your code, and all the other things that having your databases in source control will bring.

For more details on the concept of putting your database in source control, and working better within teams in general, I’d recommend reading the SQL Server Team-based Development book. It’s a free download.

Addendum (3/27/2011): If you got this link through an email, could you post a comment below as to which distribution list it’s from? Thanks.

Jan 05 2011

Encryption and the Performance DMOs

Ever wonder what you can see in the performance oriented DMOs when stored procedures were encrypted? Me neither. But, I did get that question during my DMO presentation at the PASS Summit. I did not have an answer. I did get an answer from Johan Bijnens (twitter) from the audience, which I repeated without entirely knowing what I was saying. I decided that I ought to actually know the answer to that question, so here’s a little experiment.

I’m going to create a simple stored procedure:

[sourcecode language="sql"]CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT)
AS
SELECT soh.AccountNumber,
sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @SalesOrderID[/sourcecode]

When I create this procedure and run it, you can see the general performance of the query being run by pulling data from the sys.dm_exec_procedure_stats DMO like this:
[sourcecode language="sql"]SELECT deps.type_desc,
deps.last_execution_time,
deps.execution_count,
deps.total_logical_reads,
dest.encrypted AS EncryptedText,
dest.text,
deqp.query_plan,
deqp.encrypted AS EncryptedPlan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_sql_text(deps.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) as deqp
WHERE dest.text LIKE ‘CREATE PROCEDURE dbo.GetSalesDetails%’[/sourcecode]

Now, to modify the procedure so that it’s encrypted I’m going to recreate it with a slight modifcation:
[sourcecode language="sql"]CREATE PROCEDURE dbo.GetSalesDetails (@SalesOrderId INT)
WITH ENCRYPTION…[/sourcecode]

Now, if I execute the procedure and rerun the select statement against the DMO, I won’t get any data. Why? Because of the WHERE clause. The text of the procedure is no longer available in the sys.dm_exec_procedures_stats DMO. Encryption has worked. I can’t see the SQL and I can’t see the execution plan. I will however, see values in the EncryptedText and EncryptePlan columns, showing that despite the encryption, rows for the procedure in question do exist in the appropriate DMOs.

There’s the answer to the question.

EDIT: Fixed the spelling of Johan’s name. Sorry Johan!

Dec 23 2010

PASS Summit 2010 Survey Results

As I have done in previous years, I’m going to post the results from my sessions at the PASS Summit. Feedback is a gift. I received a great many gifts this year and I’m extremely happy about all of them.

There are a set of questions with ratings, the higher the rating, the better you did. One exception to this rule is the question on time for the session. I think there, somewhat lower scores are better. There were 183 technical sessions rated during the Summit. I’m very honored to have been included in that list.

Identifying and Fixing Performance Problems using Execution Plans: 286 Attendees, 172 Responses, Avg Score of 4.23, Placed 136th

How would you rate the usefulness of the information presented in your day-to-day environment?
1 2 3 4 5 Avg
3 1 18 83 67 4.22

I’m a little disappointed with this score. Maybe people are just dealing with fewer and fewer queries these days.

How would you rate the Speaker’s presentation skills?
1 2 3 4 5 Avg
5 1 9 68 89 4.37

Again, I’m a little disappointed on this score, but I think it’s partly explained in the comments, some of which I posted below.

How would you rate the Speaker’s knowledge of the subject?
1 2 3 4 5 Avg
4 1 2 45 120 4.6

As my kid says, giggity!

How would you rate the accuracy of the session title, description and experience level to the actual session?
1 2 3 4 5 Avg
9 0 11 64 88 4.29

Not sure what was expected. I said we’d go through common problems and execution plans, we did.

How would you rate the amount of time allocated to cover the topic/session?
1 2 3 4 5 Avg
5 2 43 85 37 3.85

This was partly because I had more material than I had time for. But, it was also partly explained in the comments.

How would you rate the quality of the presentation materials?
1 2 3 4 5 Avg
4 7 24 80 57 4.04

This one, I understand, partly. I only had about four slides. Other than that, all the time was spent in TSQL playing with code. I think most people are looking for slides as documentation. I don’t entirely understand that myself, but I’ll try to improve my slide deck in the future.

Comments (responses in parenthesis):
  • Not Good. Talks about to read other material.
  • no microphone-couldn’t hear him-couldnt see screen (I had a mic, I did sound checks before we started, but there were a ton of these comments, so there’s something for me in the future, double-check sound levels, especially once the room is full)
  • A little to fast.
  • A little too basic for what i was expecting. (yeah, they bounce like that)
  • Couldn’t hear or see presentation. Too many people. Couldn’t hear questions. Worked at bottom of screen too much. (another lesson learned, exec plans are at the bottom of the screen, it’s hard for people in the back to see. I need to figure out how to post them up higher)
  • I felt that Grant needed to pull in the adhoc questions at times, just a little distracting after a point. (noted, there were several of these. I might encourage audience participation too much, or I need to restrain questions more to the end. I just enjoy the conversation so much.)
  • A lot to cover in 75 minutes. (there were a ton of these too. I’ll take it as a mixed blessing, one, they wanted more, two, I need to control the questions a bit more)
  • this would be better in an all day session in Pre/Post. (a bunch of these comments. I’ll see how SQL Rally goes & I might submit)
  • Excellent and very useful session. Presenter was very engaging. (several of these, thank you)

Overall, a decent session. I’m not excited about the results, but the feedback is excellent. I’ll have to work on some skills to deal with low screens in large rooms (other presenters who don’t already have this problem licked, take note).

DMV’s as a Shortcut to Procedure Tuning: 235 Attendees, 122 Reviews, Avg. Score of 4.626, Placed 31st

How would you rate the usefulness of the information presented in your day-to-day environment?
1 2 3 4 5 Avg
1 1 1 26 96 4.71
How would you rate the Speaker’s presentation skills?
1 2 3 4 5 Avg
1 0 4 17 100 4.76

How do I square this rating with the one above? I don’t know. Better room? The screen was higher & I was working on code mostly that I could keep near the top. I’m not sure.

How would you rate the Speaker’s knowledge of the subject?
1 2 3 4 5 Avg
2 1 3 26 90 4.65

And again, giggity! But seriously, I don’t know everything about this topic, I’m constantly learning, I learned during the presentation, but if I can get info to people that I have & they don’t, mission accomplished.

How would you rate the accuracy of the session title, description and experience level to the actual session?
1 2 3 4 5 Avg
2 0 7 28 85 4.59

Why so many 1’s and 3’s? I’m not sure. Based on comments, I think it’s the 300 rating the session has. I’ll tell you true, I’m not sure it should be 300, but then again, I don’t think it’s a 200. So if it’s a 249 or a 251, where does it go? Tough question.

How would you rate the amount of time allocated to cover the topic/session?
1 2 3 4 5 Avg
2 1 0 50 69 4.50
How would you rate the quality of the presentation materials?
1 2 3 4 5 Avg
3 2 4 30 83 4.54

Clearly a bit of a weakness of mine. I’ll try to work on it.

Comments (again, responses etc.):
  • One of the best presenters I’ve seen in the years I’ve attended PASS. Good material presented in a very lively and informative manner. (can’t help it, you get a comment like that, you share it)
  • More like a 200 level course. (several similar comments. I’m not sure where to go with it, but if I present this again, I’ll try marking it 200 to see how it goes)
  • Not sure about the legs! :-) (it was Kilt Wedensday)
  • Very interesting topic…and the presentation got better over time. (there really were a ton of comments of this type. Everyone, really, thanks)
  • There were lots of “I don’t knows” from the presenter.
  • Would say if he didn’t know something. That’s good. (yes, this was the reversal commentary this time. Fact is, I did say, I don’t know, at least three times that I recall. One time the answer came from the audience. I’m good with that, but it clearly bothers people who expect the presenters to be all knowing. People, with the exception of Paul Randal, we’re not)
  • Used lots of humor (some self-deprecating)throughout. Well applied. Engaged the audience. (I think I used “I’m stupid & lazy” a few too many times. I am lazy, which is why learning how to use DMOs is so wonderful. It enables my laziness)

I’m very pleased with this one, obviously. Some details to work on, but overall, a pretty good presentation that seems to have been pretty well received.

That’s it. I have results from the lightening talk that I did, but they’re aggregate across all the speakers and don’t provide much in the way of good information beyond the fact, that most people seemed to think the lightening talks were a fun thing.

Dec 20 2010

The iPad/SQL Monitor Results

I ended up with 131 posts (1 late hit, sorry), not counting any duplicates with Brent or Steve. It was clear that people really took this thing seriously and put thought and effort into the posts. I wish I had about 60 iPad’s to give away instead of only 10. But, decisions had to be made. The point system is utterly meaningless, but, points were awarded for pithiness (I warned you), for a well-turned phrase, and as much as I’m loathe to admit it, for poetry (just because of the work involved). Originally, I planned on just randomly picking the winner. But, I only had 131 entries, and you all worked so hard, I decided to read them all and work hard for you in return. This really was a lot of fun for the time of year. Thank you all for taking part. Thanks to Brent Ozar (blog|twitter) and Steve Jones (blog|twitter) for their hard work behind the scenes. Thank you Red Gate, the SQL Monitor Team, the DBA Team, and the Friends of Red Gate, for sponsoring such a wonderful contest and for picking this blog as one of the three.

In no particular order, here are my top 10 posts, and the winners of the iPad and SQL Monitor license:

Gianluca Sartori - For “Sometimes I ask myself if you’re married with me or with your boss”
Allen Kinsel - ‘Cause I identify with the need to monitor the teenage daughter
Gary L - Because, just because
Tim - Because he avoided the use of Nantucket
Wendy - For “play file tetris to keep your apps running” because it’s a game I play too
Carla Johnson - To help
Matt Whitfield - Haiku, what else can I say
Jane - While not a tea fan, I support the effort here
Dave Mulanaphy  – How could I not support the efforts here
Allen White - He said PowerShell

Tomorrow I might pick a different 10. These were all excellent posts. Congratulations to the winners.

Dec 15 2010

The iPad, So Far

In the front yard, using the iPad & SQL Monitor

After a great deal of unseemly begging, I managed to acquire an iPad. My intentions were to make it into a internet browser, email reader, blogging, and light writing machine. Additionally, I knew there’d be some time suck from games and I could set it up as a book reader, music player, movie viewer. I had hopes of possibly doing something resembling work with it.

Illusions are easily shattered.

Let’s start with internet browsing. It’s great. Really. As long as I stay away from Flash, which, for the most part, so far, I’ve been able to. +1 for the iPad. Email reading, it was easy to set up an email account, and there’s a Google app, so getting to my Gmail is easy. The email client with the iPad is very pretty (let’s face it, Apple does pretty). I like how it stores conversations too. +1 for a score of 2 out of 2. Blogging is where things started to break down for me. I have yet to find the equivalent of LiveWriter for the iPad, but there is a WordPress app available. It crashed three times while I was setting it up. Then it crashed once in the middle of writing a post. Then it crashed when I decided to save my work by sending the draft to the web site, thereby losing all my work, again. At this point time, I’m not using it. -1 for a score of 2 out of 3. On to light writing. Some article work, maybe some editing of existing stuff, taking notes, functioning as a highly portable laptop. Yes, the keyboard in landscape is big enough for you to touch type. But there’s that word, touch. As soon as you touch any part of the keyboard, you’ve just pressed a letter. I suppose, over time, you could get used to not touching the keyboard, but currently, it’s killing me. I type reasonably well. In my youth I was up to 85 WPM. I probably type more like 45 now, but on the iPad it’s more like 4. -1 to change the score to 2 out of 4. Those were the main work purposes and I only got a score of 50%.

I haven’t started buying much software yet, because I’m still not convinced there’s a win here. I have set up free Dropbox. I’ve got the free version of Documents for word processing. I’m using the Analytics app to look at Google Analytics. Springpad has an iPad app, so all my notes & stuff from the Droid X are available to me, and that’s a win. The Tweetdeck app for the iPad is really good. But… those are really pretty slim pickings for functionality. Oh, and I hope this doesn’t come as a shock to anyone, iTunes is a horror show.

The one place that does stand out is the ability to browse the web on a device that’s big enough to really read. So, if you have a monitoring tool that exposes itself over a web page, such as Red Gate’s SQL Monitor (and have you entered the contest to win your own iPad) or Confio Ignite, you have a method to take advantage of those utilities in a much better way than you can on your smart phone.

As far as the time-suck goes, it’s great! I watched a movie streamed from Netflix (have I mentioned that I love my Verizon FiOS internet connection). I’ve got Angry Birds loaded up. I put the Kindle app on and have some of my books on there as well some in ePub in the built in book reader. I can hit my favorite web sites for news & information. In fact, I’ve only hit one web site so far that had any noticible FLASH installed. So, on that level, the iPad has been fantastic.

I’d like to find more ways to make this into a useful tool. If anyone has suggestions, I’m open. In the meantime, I’m enjoying the toy aspects a lot.

Dec 13 2010

Life/Work Balance

Apple iPad - Work Life Balance ToolTechnology, especially information technology, is the greatest thing to ever happen to mankind, freeing us from toil and drudgery. Technology, especially information technology, is a pernicious evil taking over our lives forcing us to work harder and longer. Depending on the time of day, the day of the week, my mood, my wife’s mood, or the direction the wind is blowing, either of these statements could be true.

The fact is, I love technology and I do have to wrestle with keeping it from taking over my life, but only because I have so much fun with the toys that technology brings. You want to know how much I love toys, ask me about my Droid sometime. Pull up a chair. We’re going to be here a while. The trick is, finding that sweet spot, where you use the tools presented to you in order to enhance your life while enhancing your work. Just enough of each and you can be a hero at home and on the job and have a blast doing both.

The one thing I really hate about being a DBA is being on call. I’m not sure why but most systems fail one of three times, right when I’m going to sleep, so I get to stay up another 1-3 hours fixing the issue; around 3AM, so I can spend about 1/2 an hour figuring out how to log into the network before I spend 1-3 hours fixing the issue; or, when I’m half way up a mountain with the Scouts, in which case, I just have to call the boss and get someone else engaged (and yes, I do prefer these last failures to the others). The real trick here is, to get your systems set up so that you don’t have constant emergencies, regardless of the time of day. How do you do this? Proactive monitoring.

Red Gate handed me 10 iPad’s along with 10 licenses for SQL Monitor, their new monitoring tool. I’m to give these 10 devices away to the best responses in the comment section of this post to the question I’m going to put to you shortly. That’s right, you can get out in front of the issues you’re running into and avoid whenever it is that you get called from work and get an awesome toy at the same time.

The goal is life/work balance. Notice which one I put first. That’s the priority. Here’s your question:

What do you think the most common cause of server outages is, why, and how would being able to monitor your systems remotely help solve this issue, thereby improving the quality of your life?

The contest runs from now until 11:59 PM, December 17th, 2010. Please reply below, but keep it pithy. Don’t publish your version of War & Peace in the comments (I might delete it). I’m the sole judge and arbiter (which means, I probably will delete anything resembling War & Peace). One entry only. Make sure there’s a means of contacting you in the post, or I’ll give your iPad to someone else. Remember, pithy is our watch word. You can answer this question in three well constructed sentences. If you win, I’ll want to get a picture of you using the iPad to monitor your systems remotely. Plan on sending me that picture by January 31st. An interesting picture. Something with you sitting in your cube at work just won’t fly.

That’s it. I’ll announce the winners in a new post on the blog at the end of the week. Here are the official rules:

  1. The contest is open to professionals with SQL Server monitoring responsibility. Entrants must be 18 years old or over.
  2. Entries must be received by Friday, December 17, 2010. The contest organizers accept no responsibility for corrupted or delayed entries.
  3. Employees of Red Gate, the contest organizers and their family members are not eligible to participate in the contest.
  4. Entries are limited to one per person across the three simultaneous contests hosted on SQLServerCentral.Com, BrentOzar.Com, and ScaryDba.Com.
  5. The organizers reserve the right, within their sole discretion, to disqualify nominations.
  6. The organizers’ decisions are final.
  7. Red Gate Software and those involved in the organization, promotion, and operation of the contest and in the awarding of prizes explicitly make no representations or warranties whatsoever as to the quality, suitability, merchantability, or fitness for a particular purpose of the prizes awarded and they hereby disclaim all liability for any loss or damage of any kind, including personal injury, suffered while participating in the contest or utilizing any prizes awarded. 
Dec 10 2010

12 Days of SQL, On the 2nd Day of SQL…

Microsoft gave to me, an excellent new management language. Yeah, so it doesn’t rhyme or match the song in any way, but as far as gifts of the season go, PowerShell is it. There are a couple of problems with PowerShell, first, it’s not installed everywhere, and second, DBAs just haven’t quite latched on to this new language as a management tool.

That second problem is absolutely not one that I would say about Aaron Nelson (blog|twitter), our next 12 Days of SQL blogger.  Aaron is one of the leading lights out there educating DBAs, and everyone else for that matter, about the strengths and capabilities of PowerShell as a management language for SQL Server. I’ve said it twice, and I hope you noticed, but I didn’t say scripting language, because PowerShell isn’t. It’s a management language. It’s a way to automate the management of your servers and that’s what Aaron’s work is all about. He blogs and tweets and presents quite a lot about PowerShell. If you’re trying to learn this excellent tool, you should be reading his stuff.

In particular, the post that has me the most excited, out of all the work that Aaron has done this year, is this excellent explanation of remoting with PowerShell. Why this post in particular? Because remoting, combined with asynchronous calls means you can send a PowerShell script to any or all servers in your environment, at the same time. It means you can perform serious, enterprise level management tasks in an automated and repeatable fashion and you can do it to all your servers at the same time, easily. Yeah, I said easily. That’s because it is easy. Read Aaron’s excellent explanation and you’ll agree. Once you understand how to call all your servers remotely, it’s even easier to then pass them a script, call a stored proc, send a DBCC command, or just about anything else. This is why I call PowerShell a management language, because we’re not talking about scripting here, we’re talking about managing your servers, and that’s exciting.

On the 3rd Day of SQL, Microsoft gave to me, 3 excited screams… Sorry, but you’ll have to wait until Monday for the next installment, but the wait, I assure you, will be worth it. That’s because David Stein (blog|twitter) will be shouting a post in your direction that will absolutely be something you want to hear (and you’ll hear it, loud & clear). Now, I’m teasing Dave, and you can ask him why, but I mean it in good fun. David’s a FreeCon alumn, a nice guy, and a talented individual. If you’re not checking his stuff regularly, you really should be.

Dec 09 2010

SQL Saturday New England, #71

I’ve finally got everything together to get the web site going for SQL Saturday #71. The call for speakers is open. We’re open for registrations. I’ll be contacting sponsors shortly (or you can always contact me). In short, SQL Saturday #71, New England Data Camp #3, is a go!

Dec 08 2010

So you want to write a book

I had posted a while back on the process of book writing. It was an attempt to educate while scaring you half to death. Writing a book is extremely hard work, especially when you try to do it while holding down a full-time job and maintaining something resembling a life. Enough scare tactics though.

My Apress editor, Jonathan Gennick, great guy, wrote a detailed overview of how to put together your book proposal. The overview is targeted at Apress, but that’s largely a question of formatting. The gist of the article, and the wisdom and excellent information within it, are going to be applicable to any & all publishers. Some of the stuff that really jumped out at me:

  • You’re writing the proposal not only to sell the book idea, but to sell your writing ability
  • You have to think about what’s going to be on Amazon describing your book
  • You actually have to know, list, and argue why your book is better than competing books (and that my friends, is VERY hard to do)
  • You have to build a table of contents for the proposal (yep, time to put the thinking cap on)

There’s tons more information over there. If you really aren’t scared away from book writing by my post, then I strongly suggest you check out Jonathan’s excellent article. I promise you’ll get useful information out of it.

Dec 06 2010

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.