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.

Jun 09 2015

Speaker of the Month: May 2015

When I attend sessions at events, I try to mix it up. I’ll go to a session that, really, I probably will never use the information, just to expand my knowledge level a little. I also go to sessions by the masters so that I can try to expand my skill set. I go to sessions on topics that I feel like I know well just to reinforce my own learning and understanding. That’s where I got the Speaker of the Month for May 2015, Denis McDowell (t).

I went to his session titled DevOps for the DBA at SQL Saturday #380 in New York City. I present pretty regularly on this topic and I’ve helped build multi-day training courses on it. Seriously, I feel like I probably know it. However, as stated, I like to question my own knowledge of a topic and the best way to do that is by seeing what others are saying about it. Denis delivered. I really liked how he started out by defining exactly what he means by DevOps, since the term can be used poorly. He carefully examined the resistance that he sees occurring, fear and uncertainty being one of the leading causes. I also loved one of his phrases that he used, automate what you know. His delivery of the information was good. I really liked how he had a wealth of stories to emphasize the points he was making. It was a very good presentation with great information.

Denis and I talked about improving the presentation. The one thing I suggested was that he interact with the audience more. Get questions out like “How many people are using PowerShell now?” or “Who has automated a deployment process before?” or other stuff related to the presentation. I’ve found that the more you interact with the audience right from the start, the more they’ll interact with you, asking questions, etc. He could also pause a little more when he does ask questions to give the people a chance to respond.

I got good insight out of this presentation and I enjoyed watching Denis give it. I strongly recommend you track him down next time he’s in your area.

Jun 01 2015

Parallelism and Columnstore Indexes

Columnstore indexes are fascinating and really cool. Unfortunately, they’re adding an interesting new wrinkle to an old problem.

What’s the Cost Threshold for Parallelism set to on your server? If you just said “The whatsis of whositz?” then the value is 5. The cost threshold is the point at which the estimated cost of an execution plan goes from definitely serial to possibly parallel. This default was set for SQL Server 2000 and hasn’t been changed since. I’ve long argued, loudly, that it’s too low. I’ve suggested changing it to a much higher value. My advice has gone from 35 to 50 and several places in between. You could just look at the median or the mode of costs on your system and use the higher of those values as starting point. Getting a cost higher than 5 in a query is insanely simple. Which means that queries that are absolutely not going to benefit from parallelism go parallel. This causes unnecessary CPU load on your system and slow performance of the queries. So, listen to my advice and run out there and change this value immediately.

Or…

Columnstore indexes have two methods of processing. The first is the row mode. That’s actually the same method used by all your standard indexes and tables. You can write a query that does aggregate calculations against a table that has a Columnstore index. You’ll then see a performance benefit. However, check the properties on the Columnstore operator in the execution plan. Does it say “Actual/Estimated Execution Mode = Row?” Then you’re getting row level processing. That can be fine and you can see a performance improvement using row mode. There is another level of processing though.

Batch mode processing is where it takes 1000 rows at a time and processes the aggregations simultaneously across multiple threads. Wait, multiple threads? Yes. You have to have parallelism in order to get batch mode. Joe Sack talks about it here. You can read more about it in the SQL Server documentation. Batch mode processing is faster (depending, etc.). Especially if you’re dealing with larger data sets (more than tens of thousands of rows), you’re going to want to see Batch as the Execution Mode in the Columnstore operators.

There is an issue here though. That’s right, we just bumped our Cost Threshold for Parallelism up to 5000 (if 50 is good, 500 is better, and we’ll add a zero just to be sure). OK, 50. How high does the cost have to be in order to see benefits from parallelism and batch mode processing? Ah, well, there’s the rub. When I was testing this, I had about 100,000 rows in a table. Nothing large. Execution times went from 100ms to 50ms. Yep, getting parallel execution, batch mode processing, of the nonclustered columnstore index cut my execution time in half.

Next question should be, oh, well, what was the estimated cost of that query? 30? 20? 15?

Try 3.7.

Yeah, I got a 50% improvement in performance by changing the Cost Threshold from 50 to 3.

In other tests I had a plan with an estimated cost of .77. Execution time went from 75ms to 57ms and reads went from 115 to 62 because of batch mode processing.

Now, I am not advocating dropping everyone’s Cost Threshold to 3 (or less). What I am absolutely saying is that you have a new wrinkle that you must add to your calculations. Are you using Columnstore indexes anywhere? Then you need to examine those execution plans. What are their costs? If you adjust your Cost Threshold below them, do you see a benefit? If so, how low did you just go? Now, is that negatively impacting your other queries? You could leave your Cost Threshold as is and use a query hint with TRACEFLAG 8649 (documented by the Sensei Paul White) on your queries against Columnstore indexes, but that comes with a list of caveats as long as your arm. The other alternative is to, again, leave the Cost Threshold alone and use Adam Machanic’s method of forcing parallelism just for the specified queries. These options can be unattractive, but you have to deal with getting parallelism for Columnstore indexes somehow.

Welcome to the quandary. Tuning in and around parallelism just got harder because of Columnstore Indexes.

Want to talk query tuning some more? I’m doing a live, all day session at Connections on query tuning using execution plans this September, 2015. We’ll go over examples with columnstore and lots more. Click here now to sign up.

 

May 27 2015

PASS Board Update: May 2015

I’ve had a busy month on the Board.

You may have seen some tweets going by about our compliance with international laws regarding SPAM. This has been something of an education, so anything I say wrong below, it’s because I’m wrong, but not malevolent (at least in regards to this topic), so please be kind. It appears that the mechanisms we had on our emails for showing where the email came from and how to unsubscribe from it, weren’t completely in compliance… IF… we were sending emails that involved advertising. By we, I mean Chapters, not HQ. Now, that IF, could mean that we could skip out of meeting this requirement when our emails didn’t involve advertisements, but then it’d have to be in compliance when it did and we’d have to adjust our footers depending on the type of email… blah, blah, blah. I decided that we can get in compliance, now. Stay that way. Sure, many, most, of our Chapter emails don’t have to meet these regulations, but, if we set it up so that we do, then we never have to worry. I’ve worked with HQ. We’re in compliance. We’re getting the word out to the Chapters so if they use email other than ours, they too can be in compliance. We’re also making it part of the documentation so that future people will also be in compliance. We have some other work to do in IT to make an adjustment to the unsubscribe process, but that’s going to happen to. In short, this is almost completely fixed. Many thanks to Karen Lopez for all her help through this process. We couldn’t have done it with you.

I’ll bet that’s a lot more sausage making on display than most of you bargained for. This is what being on the Board looks like.

There was also a Board meeting this month. I was traveling so I only got in the last half of the meeting (on the phone in a shared van sitting in traffic at Logan Airport I might add, oh the glamorous life of a Board member). Good information was exchanged, largely setting us up for the in-person Board meeting next month.

Other than that, standard stuff, meeting with the HQ people regularly so that we keep the Chapter side of things running. A few minor decisions to move things forward. Still pushing on the goals and I hope to figure out how to get IT support for what we need to meet a couple of them.

I’ll report back after the in-person meeting next month. Please, please, please, any feedback on me, the Board, PASS, Chapters, my updates and other Board-related blog posts, the whole magilla, I want to hear it.