Aug 06 2015

Reading Your Execution Plans

I’m putting the finishing touches on the all day seminar on execution plans that I’ll be putting on at DevConnections this year. I have tons of examples to show you how to interpret execution plans as part of query performance tuning. We’ll be able to fill our time. However, I’ve got a pretty high degree of flexibility on which examples I use. With that in mind, I’d love it if you could bring in your own execution plans that you’d like some help reading. I won’t guarantee a solution for your query problems, but I promise to read through it and make some suggestions.

If you have an execution plan that you don’t mind sharing with the group and you’d like a little consulting time on tuning the query, bring it with you to Las Vegas.

Aug 05 2015

PASS Needs You!

kitchenerIt’s time.

No more procrastinating. No more complaining about the process from the outside. No more excuses.

You have to run for the PASS Board.

The Nominations Committee is all set, off and running. We’ve got great people involved who are going to do their best to ensure that the community gets the right people to run for the board. However, the NomComm isn’t running for the board and the NomComm can’t compel people to run. It’s up to you to step forward and take on this task. If you’ve been out there thinking that maybe you need to step up, you do. If you’ve been thinking that you might be able to give back a little more, you can. If you think you might be the right person to help lead this organization into the future, you are.

It’s you. It’s time.

Applications open today.

Click on this link to find all the information you need to make this happen.

Also, apologies to Lord Kitchener.

Aug 03 2015

Differences Between Actual & Estimated Plans

I have, in the past, made way too much of the need for Actual Plans when doing performance troubleshooting. The primary reason for this is to get the Actual Plan in order to see the differences between the Actual and Estimated Row Counts as a means of understanding how the optimizer saw the data. But, is that the only thing that’s different between Actual & Estimated Plans? Well, pretty much, yeah.

I took two fairly average execution plans from SQL Server 2014 and ran them through Altova’s XML Spy, which does XML comparisons similar to how Redgate SQL Compare will compare two data structures for you. Here is every single difference I found. Everything was additional information in the Actual Plan.

In the information for the first operator, in my case, a SELECT operator, in the QueryPlan element, two properties:


In the MemoryGrantInfo element, six properties:


Then, in all the operators, in each RelOp element in the XML of the execution plan, one additional XML element, RuntimeInformation with these properties:


This data is not exhaustive. I used a relatively benign query (it hit about 10 tables, had some suggested missing indexes, and about 40 operators all told). I’m sure if I tried lots of different types of queries, I might find a few additional differences. But for your average, “Hey, Let’s Tune This”, query, we’re looking at one additional element in the operators and twelve (12) additional properties, only four of which are in the majority of the operators.

Don’t get me wrong. If I have the choice, I want to see the Actual Plan, because that Actual/Estimated row count and execution count comparison are important. However, they are not the be all, end all, of reading execution plans and query tuning. This means, if you can get the Estimated Plan, it’s probably good enough most of the time. Heck, you can look at the estimates and then look at the data and statistics directly to get a sense if they’re accurate or not. The only time you’re likely to see any differences (other than those noted above) between an Actual and Estimated Plan is in the event of a recompile. It’s also worth noting, the plan you’re going to retrieve from the cache is going to be an Estimated Plan.

Do not be afraid of the Estimated Plan.


If you want to spend a lot of quality time with me, talking execution plans and query tuning, I’ve got a couple of different all day pre-conference seminars coming up. The first is in Las Vegas at the Connections conference. Click here to register. The second will be down in San Diego, the day before their SQL Saturday event.

Jul 29 2015

Hey Kids! Let’s Put on a Show at the Old Barn

Alternate Title: I’m traveling a bunch. Let’s get together and talk.

A bunch of trips and presentations coming up, so I thought I’d share. First, I’ll be SQL Saturday Omaha for my first time ever presenting in Nebraska. I’m excited to add this state to my list (which is almost over 40 now). If you’re not doing anything August 15th, let’s have a chat. Next, fingers crossed, I’ll get selected to fly back to my home state, Oklahoma, to go to SQL Saturday OKC. These guys put on a great event and hey, it’s Oklahoma so how can it be bad. I hope they announce soon. I need to schedule my flights. This one is on August 29th.

September also has several events. First, I’ll be at SQL Saturday Las Vegas on the 12th of September. My first time at this event so I’m looking forward to it. That’s followed immediately by a pre-con and a couple of sessions at ITDev Connections. I love inter-disciplinary conferences because it gives you a chance to branch out and learn more knowledge across the stack. It’s extremely useful if you’re getting into DevOps (which you should be). My pre-con is on query tuning and execution plans, using one to help the other. This is a great event with a lot of excellent speakers. Go here to register. But I’m not done. Down in San Diego, at their SQL Saturday on the 19th, I’ll also be doing a pre-con on query performance tuning on the 18th. I’ll see if I can’t squeeze some 2016 stuff in there too. Click here now to register for the precon.

But we’re not done. In October, I’m going to hop the pond for one my absolute favorite events, Red Gate’s very own SQL in the City: London. It’s a great event with a great collection of speakers. I won’t lie, we’re going to focus on Database Lifecycle Management (DLM), but there’ll be sessions on query tuning and all sorts of other stuff as well. It’s focused on the Red Gate tools and, let’s face it, it’s a Red Gate style event, so it’s fun and entertaining. If you really do want to drill down on DLM, there’s also the all day seminars that Red Gate is hosting that week. We’ve put a lot of work into these and I think they’re some pretty amazing hands on classes that will get you started automating your own database deployments. Also, while I’m over there, I think I may find my way to a SQL Relay event, so eyes peeled.

Later in October, well, it’s the PASS Summit. ‘Nough said. I’m doing a session focused on the beginner that’s all around statistics. They’re so important to everything you do with queries, that it’s a good idea to spend some time understanding them before you get into all the craziness of query tuning. If you’re attending Summit, please stop by my session. Click here to register for the PASS Summit. Fingers cross, I might be at SQL Saturday Portland if I get accepted.

One more that I don’t mean to leave off, but I think it deserves it’s own paragraph. Right before the PASS Summit, we’re bringing SQL in the City back to Seattle. That’s right, another shot at seeing the great stuff we’re going to bringing to London, plus a few different things (we mix up the speakers and sessions to help keep it all fresh).

Jul 28 2015

Grant’s Fly-by-night Leadership Course – The Plan

  1. Get a better name
  2. Define my personal goals for the course

I won’t be sharing these with you. I may share them with my Inner Circle (defined below). I’m thinking this is a 2-3 year commitment I’m making. I need to have my own set of measurements to ensure that this will be worth the time and effort that I’m putting into it.

  1. Define a set of principles and goals for the course

I want this course to have meaningful aim. It’s not, absolutely not, about management. Management and Leadership are two different things. This is about being a leader. It must have a purpose. The goals defined here, and like everything else, subject to change over time, will set the tone for each of the other choices. Here’s another place where I may lean heavily on the Inner Circle

  1. Create a first draft course agenda

At least initially, I’m just going to sketch out some topics that I want to cover. These include things like: setting goals (SMART goals), setting up methods of review, problem solving, institutional vs. personal power, coaching & mentoring, setting a vision, running a user group/meetup/?, other stuff. I’ll break them into a series of classes and/or articles and/or readings (yeah, this will be work for the people who take it on, tough). The classroom material will have to be generated. Some articles may have to be generated. I’m going to try to find some subject matter experts for some of this, just to get started.

This agenda, as I stated in my initial ramble, will include two very distinct and important points. First, it is going to be oriented towards volunteering. I believe that a very substantial aspect of leadership is helping people, leadership through service. With that in mind, even if this never gets associated with an organization such as PASS, we’re going to be bringing in some subjects that are, at the very least, PASS related. It’s an excellent structure in which to perform volunteer work within a technical sphere. I’m going to use it. Second, there will be tasks that must be completed. You will define these tasks based on the principles and goals of the course

  1. Get volunteers

Four sets of volunteers needed:

Set 1: Inner-Circle
These will be people that I’m going to contact directly that I know have a background in teaching about leadership, mentoring and volunteering. I’ll be using them to help me vet the agenda and the classes I hope to teach as well as support me through the process of picking the mentors who will guide the first group of people doing this.

Set 2: Subject Matter Experts
There are people out there that know more than I do about, well, everything. So for the classroom material and written material that we need to generate, I’m going to call for volunteers. It will be based on the agenda of the course, and I’m going to exercise editorial control (no biggie since I’m a marshmallow of an editor, I made need to take lessons from Tony). But it’s an opportunity for people to share their knowledge. Developed courses are going to become the property of this entity, whatever it is, because it’s the course that has to be the focus, not any one class. With permission, I think I’ll let classes be taught in other venues. I want to share this knowledge, plus the full experience is from the next set of volunteers.

Set 3: Mentors
Ideally, this group will only ever be drawn from attendees of this course (similar to how Wood Badge works). However, we find ourselves in a chicken/egg situation. So, I’m going to try to get 3-6 people who are prepped to be mentors, but who will also be taking the course at the same time (and possibly giving it in part, nothing to say there’s no cross-over between the first three groups of volunteers)

Set 4: Attendees
AKA, rhesus monkeys. We need someone to experiment on. I’m imagining between 9 and 18 people, three for each mentor. Each class will be the same, a group of three for each mentor and only a few mentors. Many more than that and we’re going to spending way too much time sweating personnel management and this is going to be hard enough as it is. These are people who want to step up. They want to become better leaders in their community, their company, or, best of all, both. Also, this first set have to be people who are going to commit to two things. First, they’ll do the course. I’m assuming that’s a requirement for everyone going forward. Second, they’ll help us build the course. We need volunteers who, right out of the gate, are going to be willing to provide actionable feedback (another thing we’ll teach) so that Class #2 is better.

With the exception of the Inner-Circle, I’m going to call for volunteers in a separate blog post, a little farther down the track. I’m not ready at this point to start the process of evaluating people’s CV to determine if they’re a fit for Sets 2-4. So, adding it up, 2-3 in the Inner Circle, 3-4 SMEs, 3-6 Mentors, and 9-18 monkeys, uh, I mean attendees (and yeah, my humor will have to be turned WAY down for this), we’re looking at, assuming zero overlap, 31 people. Woof! I hope there’s lots of overlap. But each course will only be this big. I want it to be a intimate experience.

  1. Pick the tools

Powerpoint. There, done.

OK, slightly more complicated. We’ll need to set up a place for discussions to take place, lots of discussions. I’m leaning towards Slack for the discussions. I’ll probably set up a task list in Trello. I’ll also need to beg borrow or steal a place to host the meetings online, but only for, max, 31 people. I’m not sure about this one. Suggestions are welcome. I think we’ll also need to have a permanent presence on the web. It’ll be a place to describe the course, but also to advertise those who accomplished everything to arrive at their… award? certificate? tramp stamp? (I know, my humor…)

  1. Legal?

I’m serious about the copyright. For this course to be something people will put on their resume, and yeah, that’s one of my own goals, there I leaked, it needs to be a Thing. For it to be a Thing, the information and delivery needs to be regulated through a pretty rigidly defined method with little deviation. Other than that, do I need legal to set up a general course? I’m not sure. This will be non-profit, so maybe a structure will have to be established once any kind of cash transactions get involved (and at least for the first course, it’s going to be free). Any advice here would be useful.

There. That’s the initial outline. Please, don’t start volunteering for anything yet. However, if you see any glaring holes in the start of this, please let me know. I will continue to post back here regularly as things progress.

Jul 20 2015

SQL Server Management Studio – Footloose and Fancy Free

That’s right. There’s been a divorce. SQL Server Management Studio (SSMS) has been divorced from the server product. In fact, Microsoft is inviting you to the new SSMS coming out party.

I’m pretty excited about this. While I’m very comfortable in SSMS, to a large degree, it’s like that old pair of jeans that you’ve worn for the last 10 years. They’re comfortable too. Well, maybe a little tight when you pull them on out of the wash. One of the knees is gone. The legs are frayed so much it almost looks intentional. You just noticed a hole in the bottom. The zipper is acting up… Yeah, OK. These jeans have had it. So has SSMS.

The plan from Microsoft is to upgrade SSMS independently from the boxed product. In fact, since one of the goals is to coordinate functionality within SSMS with releases of Azure SQL Database, I think it’s pretty safe to say that we’ll be seeing very regular and frequent updates. While this is a good thing for SSMS and it’s a good thing for Azure SQL Database. This level of frequent updates might not be a good thing for individual data pros if you’re not used to actively pursuing new knowledge and new tooling all the time. Or, it can act as an incentive to help keep your knowledge levels up to date because you’re more employable if you’re maintaining your skill set.

Let this act as a prod to go out and learn some new functionality. Oh, and buy a new pair of jeans.


Jul 13 2015

How Do You Know You’re Good At Your Job?

You’ve been working as a DBA for X number of years. How do you know you’re good at it? Heck, you’ve been doing any sort of job for a while. How can you measure whether or not you’re competent?

The single best measure isn’t how much work you do, your accomplishments, the number of databases designed, whatever measure you have. That’s not it. The real measure, the one that counts, how do you perform when everything goes south? When that server goes offline or that database develops corruption or that SSIS package fails or, heck, you get a request to fix something that’s broken, even non-technical stuff like an incorrect W-2 form (fighting this battle currently)? Did you run around like your hair was on fire? Did you sit there stunned into immobility? Did you incorrectly read and interpret the requests and send a new copy of the same, uncorrected, W-2 form (again)?

Or, did you sigh, roll up your sleeves, spit on your hands, and get the problem fixed?

If you did that last thing, you’re good at your job.

Let’s face it, just about anyone can set up a server, install SQL Server, set up backups and then sit there monitoring them for the next five years. The test is not the day-to-day functions. The test is the abnormal, the broken, the wrong. I know people who’ve never had to fix a W-2 form, uh, I mean, a corrupt database. They’ll say, “Yeah, never happened, so I haven’t really looked at what to do about it.”

Oh boy.

Jul 06 2015

But I Don’t Have a Test Server…

I frequently see statements on forums along the lines of “I don’t have a test server, so I’m going to do something I’ve never done before directly on my production servers…” This is usually followed by questions along the lines of:

  • But that’s OK and I won’t get fired, right?
  • So how do I know if it worked?
  • Is that dangerous?

The replies are:

  • No. You should be fired
  • You won’t
  • By All the Gods! Yes! It’s stupidly, insanely, I don’t really like my production server, my data, my boss, or my job, dangerous. Yes.

I understand. You’re working for a not-for-profit, so you can’t afford tons of new servers. You’re looking at a 50tb production server of which, you can’t make a copy. Your bosses just don’t want to spend the money on servers for testing. So these are valid reasons to just skip any semblance of testing or assurance that what you’re doing is good, bad or indifferent for your servers, right? No. No, no, no. 10,000 times, no.

If you’re doing something for the very first time in production, you’re going about your job wrong.

You need to set up a test machine of some type, even if it’s just a tiny VM on your laptop. At a bare minimum, this test machine should have a copy of the structure of your production database. This means you can at least validate any script that’s going to modify structures will compile and run without error. After that, you should strive to have a representation of production data. This doesn’t have to be an exact copy. In fact, it shouldn’t be an exact copy. You should clean any sensitive data prior to using it for testing. Barring that, you should at have, again, this is part of the bare minimum, a copy of your production statistics applied to this test database.

To get a copy of statistics from within SQL Server Management Studio (SSMS), right click on the database in question and select Tasks. Within the Tasks drill down menu select Generate Scripts. You’ll get a simple wizard. Script the entire database. On the Set Scripting Options screen, you’re going to choose Advanced. There, you’ll scroll down until you see Script Statistics. By default, this will be disabled. You’re going to select Script Statistics and Histogram. From there, generate your database script. You can now run this script to create a copy of your production database.

After you create your brand new test database, I’d suggest turning off Auto Update Statistics. If you get a statistics update event to fire through any of the automatic means, it’s going to see that you have no data and will replace your production statistics with empty ones. Done.

You now have the bare minimum of a test database and you no longer have any excuses for not validating your scripts prior to running them on production.

Want to talk in person about query tuning? I’m doing two different all day pre-conference seminars. The first is in Las Vegas at Connections on September 14th. Click here now to register. The second is at SQL Saturday San Diego on September 18th. Let’s get together and talk about query tuning and execution plans.

Jul 03 2015

Speaker of the Month: June 2015

With my travel schedule, I don’t always get out to events where I can see a community speaker. Sometimes, I just see pros. That’s the case this month. Instead of skipping the month entirely, which feels wrong, I’ve decided to do a special award.

Speaker of the Month for June 2015 are all the speakers (except me) on SQL Cruise Mediterranean 2015.

I’ve said it before and I don’t mind repeating myself, SQL Cruise changes people’s careers. There are a lot of reasons for this. The number one reason is all the work that Tim & Amy Ford do to put together the cruise, create the itinerary, ensure plenty of structured and unstructured time on the boat to ensure intense interaction, and all the rest of the stuff that they do. The second reason is the people that they get to staff the event as Technical Leads. These really are some of the smarter, knowledgeable, and most importantly, friendly and approachable, people in the industry. Look at the list: Stacia Misner, Andrew Kelly, Argenis Fernandez, Aaron Bertrand. Want to learn about BI? Done. Interested in hard core system tuning? Done. Want to know how to properly write your T-SQL? Done & Done. Most importantly, want to sit down on a couch, hand Aaron/Argenis/Andrew/Stacia a drink and have a nice long chat about ways to improve processes at your work, how to get started blogging, how to make the next jump in your career path and get answers to these questions from people who know what they’re talking about? Seriously and thoroughly done. Let’s not dismiss other Technical Leads from other cruises which includes: David Klee (flat out one of the single smartest AND nicest people you’ll meet), Jes Borland (probably the single fiercest community advocate going, plus a bundle of unbridled energy and intense knowledge), Kevin Kline (I’m not worthy), and Jeff Lehmann (let’s talk cross-platform knowledge as well as process and experience).

All these people working within the structure that Tim & Amy have created have more than earned Speaker of the Month. These are the people you want to learn from and they teach the knowledge you need.

Bad news is, next month won’t have any community people either. After that, back on the regular community speaker track.

Jun 30 2015

Leadership Training Possibility

I know a few people in the SQL Server community who have been involved in Scouting. A couple of them have taken or helped lead Scoutings leadership training program known as Wood Badge. It’s an amazing program. I won’t even attempt to tell you what Wood Badge has done for me and what it does for Scouting. Suffice to say, Wood Badge helped to make me a better person and in return, I helped make Scouting better.

What’s this have to do with SQL Server? Not much really. But, what does it have to do with you and your career? Tons.

It was pointed out to me that there’s an Oracle users group that actually runs a leadership program. You can read about it here.

It strikes me that, maybe this is something we in the SQL Community ought to have. I’m posting this to see what kind of interest there is, if any. So if you regularly just read this blog and never post, sorry, today is the day it’ll really matter if you respond. So please, read through my general thoughts and let me know what you think.

First up, this needs to cover generic leadership principals such as vision, planning, communication, goals and measures and all the rest. It’s not about management. It’s about leadership. These are different and will be differentiated. This also has to have available some mentors, people who volunteer to monitor & guide attendees through the process. There will probably also be volunteers leading the classes, possibly independent of the list of mentors, we’ll see. Additional training would be on, well, easier to say what it won’t be on. It won’t be on speaking, technical stuff of any kind, or any company or technology. Instead, it would be about leading, maybe community building, running a user group, setting up and running an event, that sort of thing. It’s not going to be a single class. It’s going to take time. Let’s say, somewhere between 6 months to a year of commitment to the leadership courses (done remotely, anything else is too insane for me to consider managing) and then another 6 months committed to delivering on your project(s). And yes, this means that there will be an expected amount of work to be delivered as part of the whole thing. I’m not suggesting holding a class and then printing out certificates. I’m suggesting something similar to Wood Badge, something that you would be proud to put on your resume, something you’d be clamoring to help out with after you complete it. One more thought, it would be best if we could get an organization to back it. Barring that, we’d need to create an organization to back it. There would probably have to be a charge for this, and not a trivial one either (I haven’t thought it through, but let’s say more than $500, less than $5000, figure it out as needed). It won’t be open to anyone. I suppose an open call for application, but since it requires commitment, it might be invite only. Something to talk about. It needs a name.

This is not a course outline or an abstract. It’s really just a stream of thoughts to act as the starting point towards building a structure. My question to you is, what’s the interest? Please post. If I get my usual three responses, I’ll just write this off as an idea that won’t fly and move on.