Category Archives: Uncategorized

Why Relying on SSMS for Execution Plans Isn’t Enough

One thing I’ve noticed about myself – I don’t tend to want to blog about the same subjects that I want to talk about at SQL Saturday. Different media, different audience, so different topics probably aren’t surprising. But in an effort to be more consistent … here is a taste of the kind of material that I cover in my ‘Why Should I Care about .. The Plan Cache” presentation.

If you’re still reading this, there’s a great chance than you’ve looked at an execution plan by putting a query into SSMS, hitting the button to ask for the actual plan, and then executing the query. Pretty easy, pretty awesome, that’s how performance tuning is done, right? Well, even if we ignore the possibility of different session settings (won’t get into that here), there are a few issues.

Why It’s Not That Easy

  • If the end user doesn’t report an issue right away, they might not remember exactly what they were doing at the time they noticed a performance issue.
  • Even if the user does remember, they usually can’t tell you what query was running (they were probably using some kind of software).
  • Documentation for that software likely won’t drill down to the level of what queries are run against the database.
  • If software documentation does exist and actually does include queries, that’s a very detailed software document. Has it been kept up to date?
  • If the software was developed in house, why not ask the developer? If the developer that wrote it is still around, they probably won’t know off the top of their head exactly what the query looked like. Odds are they would have to investigate.
  • Even if it’s possible to quickly find exactly where in the code the problematic query is executed, that might not give us the query. It’s becoming more common for some kind of dynamic SQL to be involved, which means the actual query could depends on parameter values.

It may sound like I’m trying to say it’s not reasonable to ask the development team what TSQL is actually getting run. Of course that’s a reasonable thing to ask. The point I’m trying to make is just that it likely that you will not get an answer immediately. When troubleshooting performance issues time usually matters, so there is usually better if you can quickly find out for yourself what is causing the problem.

So … It Appears that September Will Be all About the MCM

News about the death of the MCM / MCSM broke right around the time I was starting to write up an interesting case study involving the ascending key problem (for those unfamiliar, see Gail Shaw’s excellent writeup for example), so that post will probably need to wait for a while. It’s not my intent to rehash the #SQLMCM issue here, if anyone who cares about the MCM (Microsoft Certified Master) program isn’t already up to speed on the basic issues a good starting point can be found from Jason Brimhall and at the #SQLMCM hash tag on twitter. Part of the reason that I am not really ranting is that I am actually relatively fortunate. I had been planning to take the lab exam (final hurdle before becoming a MCM) at the end of September anyway so the only real impacts on me are

  • I need to decide whether or not it’s worth following through with the exam when the certification is dying (the answer is probably yes).
  • I am sure I will need to cover this out of my pocket now. I’ve been fortunate enough to have a part-time W-2 gig (on top of my consulting load) that has actually been quite supportive of my MCM quest up to this point. Now that “the email” has gone out I am frankly too embarrassed to even ask if they would care to pony up a couple of grand more for the final test when the plug is getting pulled the day after (maybe literally) I take the exam. Maybe the only reason to consider doing so is that I actually wrote the MCM attempt into my performance plan for the year. This is actually not as bad as it sounds, I am dual employed so it really is only fair that Rick-the-consultant pays part of the certification expense which will benefit me equally in both of my current roles.
  • Some lost prep time. When I said I planned to take the exam at the end of September that was my optimistic estimate. In the back of my head I had actually started telling myself I may wait until the end of October. That exam is no joke, an extra month of prep time would have been handy.
  • It’s all riding on this attempt. Before the announcement I figured that if I did not pass this attempt I could consider regrouping for another try before the MCM exams were retired in favor of the new MCSM exams.

First : MCM vs MCSM – A Long Tangent

But I’m not interested in dwelling because I really am one of the lucky ones. I’m more interested in offering my perspective as one who is in the pipeline at this point in history. First and foremost I was always a lot more excited about the legacy MCM certification than I was about the MCSM. I just felt “Microsoft Certified Master” was an awesome description. It is intuitive. It rolls off the tongue. It is clear. I imagine my future self shaking hands slightly more firmly and standing slightly straighter as “Rick Lowe, Microsoft Certified Master”. It is immediately clear to any person who hears this exactly what I am claiming (that I am awesome), why I feel justified in claiming it (my awesomeness has been certified), and what evidence is available (this Rick guy should be able to cough up a transcript sharing code). This is actually why I am ready to take the exam, if I had not been concerned with becoming a MCM I probably would have been hanging back, updating my credentials from MCITP to MCSE and waiting from the MCSM exams to be published.

On the other hand, when I imagine a possible future self introducing himself as “Rick Lowe, MCSM”, or worse “Rick Lowe, Microsoft Certified Solution Master on the Data Platform” I am pretty sure I will be slouching as the eyes of whoever I’m speaking to glaze over slightly. “That is not just more alphabet soup”, my future self says, “that is a pinnacle certification. Here, let me draw you a diagram of all the new SQL Server certifications so you can see this blue pyramid thingie. Look, there’s no gray at all in this pyramid which means this is the good one. You should really be rather impressed with me about now”. I suppose the alphabet soup issue is what really bothered my about the change from MCM to MCSM. Aside from just being a cool title MCM, both when spoken and written, looks quite different from MCITP which could be important when speaking with somebody outside of the SQL Server community. Even if they may have seen hundreds of resumes for “paper” MCITPs it is possible that the fact MCM sounds different may be enough to get their attention. MCSM, on the other hand, visually and verbally kind of blends in with MCSA and MCSE.

If it seems like I’m taking cheap shots at a particular set of visual aids that is not my intent. I love visual aids and have nothing against blue and grey pyramids. If I haven’t been clear enough, the point I am really trying to make is that the visual aids are not just helpful for understanding the certification roadmap, they may actually be kind of necessary for understanding the current roadmap unless one has a very good head for acronyms. And this does not help when it comes to acceptance of the current generation of credentials by business leaders.

But more importantly, Can we chart our own destiny?

Plenty has been written on the tricky revenue problem posed by the MCM exams (rather steep fixed costs would need to be offset by a relative handful of test takers before this test is profitable). One commonly expressed concern is that it may just be too difficult for Microsoft to make a profit from the MCM/MCSM program and may simply never reintroduce the concept of a pinnacle certification. Another is that they may fix the revenue issue by dumbing down the tests enough to increase the percentage of SQL Server MCPs who would be able to pass. More potential conversions would probably mean more test takers which would definitely mean more revenue. Both of these cases are troublesome for many in the community who value the rigor and the “unfakeability” of the MCM/MCSM exams.

My question at this point is this : if we as a community do not have a lot of faith that Microsoft will bring back the MCM in a satisfactory form, or if we are concerned that we just care more about this particular certification than they do, why are we waiting for them to do so?

It’s probably not realistic for anyone to expect the creation of a “Community Certified Master of SQL Server” program. Don’t get me wrong, it would be fantastic if somebody could come to a conference, sign up for a “test your mettle” hands-on precon or postcon, and potentially walk out as a “CCM”. But getting the community involved in the master testing process does not change the underlying economic issues. Developing the test would take a tremendous amount of effort. Administering the test would be a nightmare. Do we offer the test online? Probably not because it would be too easy for somebody to either cheat or capture the questions to look up later. Establish a dedicated testing center? Probably not realistic for this volume of test takers. Remote proctor? Maybe, but that could be very expensive because it probably requires something close to a 1:1 ratio of proctors to test takers. Co-locate with a conference? Might be the most workable but is it a problem if the exam can only be attempted once or twice a year?

And of course, even if a delivery method is found this does not change the underlying issues. Developing a MCM-type test would be very expensive (it bears repeating). Convincing industry that it should care about the CCM would be even more difficult than it was to try to convince them to care about the MCM. I am sure there are many many more.

Unfortunately I actually do not have any productive suggestions here, all I can really do at this point is suggest that the death of the MCM could be an opportunity for us to do something even better. And it may be foolish to believe this means testing – as Brent Ozar points out there are a lot of cool experiments we could conduct that have nothing to do with a traditional certification program. But if anyone does have an idea I may be interested in pitching in. After October 1, of course. The rest of September is cut out for me.

Coming soon

Hi all,

I have a little extra time on my hands for the next few months, which means this wasn’t only the ideal time to start blogging but also that I should be able to post relatively frequently for a while. That said, I’m getting ready to disappear on a rafting trip for a week. If you discover this space while I’m gone and are wondering whether or not it’s worth coming back here are some topics I’m planning to write about in the next few months.

  • The correct way of getting Oracle Instant Client for work with SSRS / SSIS. Google search may be leading you astray.
  • For the DBAs : The potential of Entity Framework. Why I really wish I could love EF.
  • For the developers : The failure of Entity Framework to live up to its potential. Why EF may be causing your DBA to drink in the morning.
  • The ascending key problem. Why did performance suddenly get inconsistent shortly after we deployed?
  • CRUD squared. When stored procedures go awry (AKA Rick loses some friends part 1).
  • That time I turned on RCSI for the sole purpose of getting the developers to stop using nolock. Wasn’t that awesome? Or was it more of an evil waste of resources? (AKA Rick loses some friends part 2)
  • Social capital at the office. How to get the mean kids to realize how brilliant you are and start listening to you.
  • The limitations of self learning from the internet. Why I frequently pay out of my own pocket to go to conferences.

But more importantly, feel free to contact me to ask questions or even just suggest that I cover a particular topic. This request may be more relevant in the future because you probably can’t tell from a single blog post how valuable my opinion is, but ultimately I do this because I love talking about SQL Server. If nobody is reading this then I’m just talking to myself which I have been known to do that on occasion, but I would much rather talk to somebody else. The more I know about what issues you would most like my warped perspective on, the more productive that conversation can be.