Dec 19 2014

“Pretty Plans vs. Performance” or “Grant Gets Pwned”

If you get an execution plan that looks like this:

prettyplan_uglyplan

I wouldn’t blame you for immediately thinking about query tuning. Especially if the code that generated it looks like this:

SELECT  soh.OrderDate,
        sod.OrderQty,
        sod.LineTotal
FROM    Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
WHERE   soh.SalesOrderID IN (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10,
                             @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18,
                             @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26,
                             @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34,
                             @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42,
                             @p43, @p44, @p45, @p46, @p47, @p48, @p49, @p50,
                             @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58,
                             @p59, @p60, @p61, @p62, @p63, @p64, @p65, @p66,
                             @p67, @p68, @p69, @p70, @p71, @p72, @p73, @p74,
                             @p75, @p76, @p77, @p78, @p79, @p80, @p81, @p82,
                             @p83, @p84, @p85, @p86, @p87, @p88, @p89, @p90,
                             @p91, @p92, @p93, @p94, @p95, @p96, @p97, @p98,
                             @p99);

Let’s replace this with a table variable, maybe even one passed in as a parameter. The plan then looks like this:

prettyplan_pretty1

Ah, much prettier. I’m happy now, all’s right with the world… But, just in case, let’s look at performance. The first query ran in about 2.2ms and had 599 reads. The second query ran in about 24ms and had 598 reads… crud.

Well, let’s modify everything again. Instead of a table variable, we’ll use a temporary table and get some statistics into this puppy which will clean things right up. Here’s the new plan:

prettyplan_pretty2

Looks pretty familiar, although there are slight differences in the cost estimates between this plan and the preceding one. But the run time is 85ms with 714 reads AND I had to create the temporary table which added time to the whole thing.

Doggone it, that other plan is heinous and ugly and so is the query which uses an IN clause. Shouldn’t the cleaner, simpler, execution plan be an indicator that we’re going to get better performance?

Nope.

The thing is, just because the execution plan is simple and easy to understand does not mean it’s going to perform well. You can’t simply look to an execution plan to understand performance. You have to measure the run times on the query, look to the resources it’s going to use in order to understand where waits are likely, look at it’s reads, and take all this into account, along with understanding what the execution plan is doing in order to make the appropriate choices for performance on your system.

UPDATE:

I kept working with this because I was convinced I could get faster performance. The main difference as I saw it was that the optimizer sorted the data in the IN clause and I wasn’t explicitly sorting the data in any of my replacement queries. But nothing I did resulted in better execution times. And that was upsetting.

First, when you’re tuning a query, you’re going to look at the execution plans, as I did above. But, when you want to measure the performance of queries, it’s a very good idea to turn off execution plans and just capture the query metrics. I knew this and was doing it and you could see the results in the Extended Events where I was capturing each statement for the SPID I was working within. I also had the SET STATISTICS IO and SET STATISTICS TIME enabled for the query. Since each execution would cause those to fire as part of the statements and those were making my  ExEvents window messy, I decided to turn them off… WHOA! Query execution times radically changed.

In fact, my first attempt at tuning the query, substituting a table parameter, was suddenly faster than the original. The fastest was when I pre-sorted the data in a temporary table (discounting the costs of sorting and inserting the data into the temp table just for the moment). In fact, the prettiest plan was indeed the fastest.

Experimenting further, it was the STATISTICS IO that completely changed the execution times.

In short, pay no attention to my original post above, instead, let the lesson be that I need to be very cautious about the Observer Effect.

Dec 08 2014

Speaker of the Month: December 2014

For the month of November I was at the PASS Summit and Live360, so I didn’t get to find new speakers to see (heck, I didn’t get to see any sessions at Summit this year). But, that didn’t mean I didn’t get to see great speakers. I did (Rimma! Rimma! Rimma!). Live360 had some amazing presentations. I do like the mixed events like Live360 because I enjoy crossing into dev sessions as well as database sessions.

My speaker of the month for December 2014 is Bradley Ball (b|t) and his session “Inside the Query Optimizer

Oh, before I go any further:

HEY BALLS!

Sorry, but Brad had us do that at the start of the session. I figured I’d just carry it on. Anyway, wow. What a great session. Brad’s delivery is just awesome. He’s light and funny, but hits the points he means to hit and hits them well. I really loved the partitioned view example to illustrate simplification within the optimizer. Like I do with most of my speaker of the month sessions, I learned a few things. I also heard things that are driving me to do some research to help further my understanding. That right there should be one of the criterion for what makes a great session. Everything worked. His jokes were funny, but not overwhelming the content. As already noted, the demos supported the content extremely well. It was great.

Improvements… Yeah, a couple, maybe. I loved the way Brad presented, but, it had a feel as if he was speaking really fast. I don’t think he was, or maybe I just heard really fast, but you might try slowing down slightly. Again, more of a feeling than a real criticism. Brad had more scripts and material than he had time for. Like with my last winner, I can’t say this is a problem. But I’ve heard people complain about it. Again, you might try hiding some of the stuff and only pulling it forward as needed.

If you want to learn about the optimizer, come see me speak… kidding! Seriously, you won’t go wrong attending this session. You’ll walk away with a smile on your face (Brad’s funny) and more knowledge in your head. It’s a winner.

Side note: The last two winners are both established speakers who do this all the time and are therefore awesome. Isn’t this supposed to be about helping build up new speakers?

Yes.

I’ll try to do better. But this will happen sometimes.

Dec 05 2014

Speaker of the Month: November 2014

I have been very remiss in my blogging of late. Apologies.

One thing I haven’t blogged about is Speaker of the Month. I’m behind. So, I’ll be posting two of them over the next few days in order to catch up.

First up, Speaker of the Month for November.

I went to quite a few events in October, but I actually didn’t get to see too many sessions. However, the few I saw were actually quite good. Speaker quality is just going up and up. I finally got to see someone present that I’ve known for years and years, but just never had the opportunity to sit in on a class. Well, I sat in on one, and this is the result. My speaker of the month is Kathi Kellenburger(b) and her presentation, Writing Better T-SQL Queries with Window Functions.

Kathi is just great. If you’ve ever met her in person, she’s this quiet, unassuming woman. She’s like your favorite Aunt, maybe why her nickname is Aunt Kathi. But when she’s presenting, she takes total charge of the room. You know who the presenter is. She spoke without a microphone and I didn’t realize she could project like that, while sitting. The content of the session was great. She mainly worked in the code and through examples, but she had just enough slides that there was some content documentation to guide people through the lessons. Awesome work. Her examples were extremely illustrative of the points she was making about window functions. I picked up a few things and was reminded of several things that weren’t at the front of my brain any more. I loved it.

Everyone can improve a little. While Kathi presented a tour-de-force, I found a couple of things I didn’t like. She had a series of graphics about this guy on set of stairs, that just didn’t work for me. I don’t have a good suggestion on how to improve it, but it just didn’t hammer the point home well for me. Kathi also had more material than she could cover in the time. Now, that’s a mixed bag. I’d say it’s better to have too much material rather than too little. But, I’ve heard people complain that they didn’t get to see X, or Y, or Z, because of time constraints when, in fact, the presenter intended that material as padding in case the session ran short. I guess in order to avoid the complaints, maybe put the padding after the final slide so you can go to it, but only if needed.

However, as you can see, if those are my complaints, one slide graphic didn’t work, and she had too much great material, how amazing was this presentation? Exactly.

This is extremely useful material. I strongly recommend tracking down Kathi and checking out this session if you get the chance.

 

Nov 13 2014

PASS Summit 2014 Speaker Idol

For the first time ever at the PASS Summit, a competition was held to select a speaker for the 2015 Summit. This competition was organized and run by Denny Cherry. You can read all about what he thought of the event right here. I was asked to take part as a judge. I was on the panel for all the preliminaries and then sat in the room for the final. Here’s what I thought of the event.

First off, thank you to the PASS organization for taking a chance. This is a deviation from the way things have been done. Trying new things can be difficult, but the organization stepped right up and supported this addition to the rich pageant that is the Summit. Second, I want to thank Denny for putting it all together. He put a lot of personal time into this event and pulled it off largely without a hitch (there is a minor bit of controversy, I’ll talk to that in a minute).

The speakers…

Wow!

Clearly people brought their “A” game. Every presentation was good. Period. But, some were better than others. The one clear differentiator, the thing that just made the top sessions stand out from the rest was how the presentations were put together. The winners from every day and the final clearly had focused on presenting a five minute session. They had a simple, single, clear idea that they wanted to communicate within the five minute time frame. The people who didn’t win (note, I didn’t say, didn’t do well), took one hour sessions and then tried to boil them down to five minutes. It just didn’t work. It very likely couldn’t have worked (although, I took a five minute session once and turned it into a one hour session). That’s because adding stuff in is easy. Taking stuff away is hard.

I personally learned a few bits of information from the sessions. They were great. But, someone has to win. As of this moment, there is exactly one known presenter at PASS Summit 2015 and that is Pieter Vanhove (b|t) . Flat out, he was amazing. He did things in his first five minutes that I, personally, would never do in a five minute session, and he got away with it. Then, to top it off, instead of coming to the final with his original presentation polished for the judges, he brought in an all new presentation and proceeded to blow everyone away. It was great. All the other presenters, especially in the final four, were fantastic, but Pieter just clearly won.

The weaknesses displayed were somewhat common to what we all do. People didn’t always make good eye contact with the audience. There was some bad time management (see that bit about the 5 minutes vs. hour presentation). A few people were dealing with presentation mice for the first time and just hadn’t practiced enough. Nervousness took over. People wondered aimless around the stage. Actually, I do a lot of these myself, but, when you’re watching good presentations and you need to differentiate between people, you identify the things that need to be improved on, whether you do them or not.

Let’s talk about the judging. What did I learn? I don’t do mean well, at all. Let me rephrase that. I do mean EXTREMELY well. I don’t do mean/funny/I’m not really mean well. I tried to be humorously mean to Reeves Smith, who did a good presentation, and oh, man, I just came off as nasty. It was horrible and I could tell when I was doing it. In fact, I couldn’t sustain it. The next person came up and I utterly broke persona and said I couldn’t do it any more. Luckily, Reeves is a great guy and took it as it was intended (although not as it came across, I really sounded like an ass). I’ve apologized to him personally and let this stand as my public apology for that. I won’t attempt mean/funny again. I’ll still be mean as required, but lesson learned. Sorry Reeves.

The rest of the judging, well, it’s all from people who do a lot of this. Except for my momentary dive into a cesspool, the comments were all constructive and helpful (including mine after Reeves, mea culpa, mea culpa, mea maxima culpa). If you were there, you were in an advanced presentations class. I hope people took notes and will adjust their presenting and presentations based on it. Heck, I changed some of what I did on Friday after hearing all the other judges speak.

Finally, the controversy. OK, it’s pretty small potatoes, but we were supposed to pick a wild card for the final. We didn’t have any specific instructions on how to pick the wild card. So, after the judges talked a little, we picked someone who had not scored as a runner up in the initial rounds. Why? Because many of us had seen him present before and we knew he could do better (he did, there was ALMOST a second controversy). Was that against the rules? Since there were no rules, I’m going out on a limb and saying no. It’s like when a fighter doesn’t get a KO or TKO and leaves the decision to the judges, you can’t whine about the outcome. We made a decision based on the knowledge we had (and don’t think I was playing favorites, my very first Speaker of the Month was in the competition and he didn’t make the finals). I stand by that decision.

I thought the format worked well. I hope it’s part of the program next year. I’m not sure I’ll get to judge again since I’ll be on the PASS Board by then. However, assuming the schedule allows, I’ll sure go and watch some great presentations.

Nov 11 2014

I’m Still Not on the Board

I’m just back from the PASS Summit 2014. What a great event. But this year, it was a little different. I did a lot of the usual things, presented a pre-conference seminar to about 130 people, helped out at the Red Gate booth, presented a session on execution plans on Friday, went to a few after hours events (that included karaoke). You know, the Summit. My tenth one. But, I am starting the process of transitioning onto the board. This will be my first report on the work I’ve been doing around that. However, please let me point out something, that was made very obvious to me during the event, I’m coming on to the board, but I’m not yet on the board. I say this because whatever work I put in for board business last week, it was nothing compared to the time put in by the people who are actually on the board. Make no mistake, that’s insanely time consuming work.

I went to a series of meetings that reflected my past volunteer work and my upcoming time on the board. I attended meetings with the Chapter Leaders and the Regional Mentors. There were some great discussions around past performance and support of the organization and future needs. I’m not going into details on this stuff. Some of it is NDA, most isn’t, but I don’t think it’s my place to address any of it yet. Let’s just say it was really interesting. I especially loved hearing about why the Regional Mentors do the work that they do. Ask one sometime. We have a lot of work ahead of us and I’m pretty jazzed about some of it.

I also attended a series of PASS Community sessions at the event. I really enjoyed the session on how to build a user group by two of the best Chapter Leaders I know, Kendal Van Dyke and Jes Borland. I also went to a couple of sessions in and around the work I helped with on the Summit Selection Committee. We had a workshop on building an abstract. It was attended by committee members and other speakers. If you didn’t get selected, you both learned why, and we reviewed individual abstracts to help people write better ones. It was a great session and if you needed more feedback on your abstracts, I’m sorry you weren’t there. We then had a mission report session from the Selection Committee. We walked through how the various teams on the committee did their work, how it’s going to change next year, and how well, overall, despite quite a bit of heat generated at the announcement, the process went. Based on the fact that attendance was up all over the board, even on pre-cons sold, I don’t think the committee did too poorly. But, the committee is going to try to make it even better next year, especially around getting better feedback to everyone.

My biggest impression from everything I did in and around the board, is actually not a shock or even news to me, the board is a team of great people who are doing simply amazing work. I’m actually humbled and more than a little fearful to be joining such a great team. I’m also really impressed by the people at PASS HQ. Another amazing team I’m looking forward to working with. If I had to provide criticism about everything I’ve seen so far, I’d say that I think all the work done by all these people isn’t adequately communicated. Maybe it’s better that it all looks like effortless magic, but I think I might have been slightly less critical in the past (slightly), if I had known how much work it was.

If there was a unifying theme to everything being said and done, I’d say it’s communication. More of it is needed. More of it seems to be promised. More of it is wanted. You will see quite a lot more work done in that area. Heck, it was part of Adam’s keynote.

Look for more updates as I get to work with the teams more.

Nov 06 2014

PASS Summit 2014: WIT Luncheon

Since I’m starting on the board in January, I’ve stopped taking part in the bloggers table during keynotes. First time since they had a bloggers. But, I am going to blog through the Women in Technology (WIT) Luncheon because I just love getting to take part in this amazing event.

For the luncheon this year, they have changed the format. Instead of a panel, they’re just talking to some really interesting people who have been doing amazing stuff supporting growth of women within STEM types of work and education.

First up is Kimberly Bryant, the founder of a non-profit organization called Black Girls Code. She started out trying to build a for-profit startup, but when she found that there just weren’t that many women in technology. But when her daughter started to pursue computers, through gaming, she got her into a programming camp. That changed both her daughter’s life and her own.

More than 1/2 of all girls in Middle School are interested in STEM. But it’s only 3% in High School. That drop-off alone could explain where all the women in technology have gone in recent years.

Black Girls Code secret sauce is putting all the girls together and letting the girls teach each other and with adult women mentors. They focus on having women-only instructors in order to model the behavior that they want the girls to emulate.

They use just about any language they can get developers in to teach. They’re also partnering with Lynn Langit’s “Teach Kids Programming” organization (Lynn is one of the more amazing people I’ve had the opportunity to meet and her program to teach kids Javascript is amazing).

Black Girls Code is an international organization, not just local or US only.

If you’re interested in getting involved in supporting your local community, according to Kimberly Bryant, there just aren’t enough teachers in technology in local schools. Schools need to have classes made available by people who know how to do it teaching during the school day. She suggests that doing this, in addition to an after-school program like Black Girls Code, is necessary to make a more profound difference.

She was asked if she advised students about how taking part in STEM, because it’s currently so under-represented by women, is going to difficult for them. They do take part in making sure that it’s part of the stuff that they’re teaching.

12:40
Kimberly Bryant is asked how to keep more women involved in technology past the mid-point of their careers.

She says that what needs to happen is that women have to stay within a company for long terms. But they have to have more mentors and advocates of women to grow them, over time, in order to break into the C level jobs.

Q&A with Kimberly Bryant:

How can more women be brought into the graduate degrees?
She says the same thing again, more organizations and support mechanisms for women on that track.

How can I assist my 15 year old girl to be interested in technology and software?
You need to get them interested before high school. It’s extremely difficult to get them interested at that level. But the younger girls, it’s down to getting the right role models in front of them.

How do we deal with the fact that many people don’t have laptops?
Work with libraries. Also find businesses that have high technology turnover to get them to donate older machines.

How do you change the culture to keep women around longer?
Hire more women. In order to change them from the ground-up, have more women in place.

What happens when it’s a woman vs. a man in an interview?
There is unconscious bias in interview processes, so you need to find ways to eliminate that as part of your hiring processes.

12:54

Go to career fairs (not a question).

How do convince children that they need to know about technology, not simply have a degree?
Black Girls Code also works with parents to get them involved in what’s going on with tech in order to enlist them in helping the kids. Other than that, it’s about teaching that technology has to be a part of all classes in schools. Convincing people that everybody is involved in computers.

How do you get a young girl involved?
Find programs that both teach the skill sets, but also have other girls involved.

What are some recruiting strategies for getting more women and minorities?
Have more women doing the recruiting. Women are not going to work for your organization if they don’t see other women working there.

Comment: Women have a hard time climbing the ladder. They should build doors and then open them.
Response: And then reach back through that door and pull other women through it.

Comment: Boys should also be taught that it’s OK to like smart girls.

Missed the question:
Every women’s experience is her own. Some young woman should look around her at her peers. Somehow women are not adequately represented.

What can the person who is not a woman or not a person of color can do to avoid unconscious prejudice?
Asking the question means you’re thinking it through. Mentor someone who doesn’t look like you. Making your company be accountable to what they do in this regard.

Microsoft employee with PHD stands up to thank everyone for their support for women. Her question is what does success look like in five years?
See some definitive actions from companies to make a difference. Have government involved more. Getting women in college or middle management to get them moving into C-level stuff.

 

It was a very interesting discussion. Thanks to the organizers within PASS for putting on something so vital and interesting. Thanks to Kimberly Bryant for her time, thoughts and efforts.

Oct 20 2014

Database Lifecycle Management

There are lots of people who talk about Application Lifecycle Management. But, the database is a major part of every application and if you do a similar search, there aren’t very many people talking about Database Lifecycle Management at all. Why not? I’m positive you’re deploying a database with your applications. I’m also positive, because of the unique problems that databases present, primarily around data persistence, that you need to think about how to get your database(s) deployed. Unfortunately, even for strong, capable data professionals, deployment is something thought about later. Or, you’re still doing the old school method of waiting until there’s a deployment script that you’re going to review, line-by-line, before you run it against production. There’s a better way.

What you need to do is start thinking about mechanisms for automating your database deployments. This means getting your database into source control to start with. Yes, your database in source control. Right along side the application, getting versioned with the app, labelled and branched with it. This is fundamental to your automation because if you don’t know what’s getting changed when, you can’t automate anything. Then, you want to set up mechanisms to start testing your database, both the behavior and the deployment. This means taking advantage of all the work done by our dev peers on continuous integration (CI) using a CI server to automate builds and tests from your source control system. You can also use some the tools and methods around unit and behavioral test automation. Finally, once you get through these mechanisms, you can explore going for full blown continuous delivery or even continuous deployment by automating your processes using a deployment manager.

You need this, because if you want to have smooth, accurate, successful builds to production, you don’t want the production server to be the first place you run your deployment script. You want to test that thing, 5, 10, 100 times before it gets to production. And you do this through automation.

Interested? Steve Jones and I are going to be talking about this, in detail, and at length at the SQL in the City event in London coming up this Friday, October 24. We’ll also talk about query tuning and other topics. But, if you want to really explore database deployment automation and begin to get your database deployments under control and more tightly integrated with your application code deployments, then come on down so we can discuss this.

Oct 17 2014

Why Haven’t You Changed Everything?

The first in what I hope will be many regular reports on what I’m doing on the PASS board.

To answer the question that I’ve already been asked (who had 47 hours in the pool?), first, I’m not on the board yet. I was elected to it and my term starts in January. So, don’t expect a lot in the way of ongoing structural changes at the organization instigated by me. Second, I’m the lowest of the low on the board (after I’m actually on the board), so I won’t be making a whole lot of changes at all (whether I wanted to or not). Third, the board is a board, a bunch of people, who get to vote on things, so even if I were able to call a meeting (I’m not) and I had some grand idea for rearranging the Summit (I don’t), I’d still have to get a majority on the board to support what I wanted done. Fourth, on the board, different individuals are responsible for different aspects of the PASS organization. These aspects are organized into what they call portfolios. The portfolio I’ve been assigned (yes, assigned, and I so wanted the “Drink Tickets at Summit” portfolio), is Chapters. So, I won’t be tasked with rearranging SQL Saturdays or whatever your favorite issue might be. Instead, I’m going to be working with the people running your local user group, the regional mentors, and the HQ people responsible for everything around Chapters. Oh, and, a healthy chunk of the decisions for direction in 2015 for this portfolio have already been made. You can read about the portfolio direction for 2015 on the PASS web site (in fact, if you are interested in what’s going on at PASS, you should read all the portfolio plans).

So no, I haven’t, and won’t, change everything. I’m learning about what I can and can’t do as a board member (and what I can do, I still can’t do until January). If you have ideas for things you’d like to see PASS do for the Chapters, I’m open and interested. I have a couple of ideas of my own that I’m going to push for. More on that once I’m actually on the board. I’m writing this just a couple of days after the election, and I’ve already been on multiple phone calls with other board members, getting a handle on this job AND I HAVEN’T EVEN STARTED YET. In short, please, let’s set some reasonable expectations (and no, I haven’t drank any kool-aid, haven’t even been offered any yet).

Expect another blog post after the Summit.

Oct 16 2014

Query Performance Tuning in SQL Server

I have a pre-con coming up at the PASS Summit. You can read about it here. I named it “Query Performance Tuning in SQL Server 2014″ because it seemed like a good idea to bring out the aspects of 2014, and we will. But, I need to tell you, this is primarily a session about query performance tuning in SQL Server, full stop. I’m going to cover information that’s applicable all the way back to SQL Server 2000 and 2005. The majority of the information will be applicable to 2008 and up. I’m going to go over the things you can do with dynamic management views to pull information about queries to tune right out of the cache. That’s applicable to more than 2014. We’re also going to go over how and why you should use extended events to more accurately, with less load, capture query metrics on your systems, 2008 on up. We’ll be covering all sorts of common query problems and mechanisms for getting around them so that you’ll be able to fix your code at home. Through it all we’re covering the optimizer, statistics, indexes, constraints, query best practices, pretty much all the things you need to tune your queries.

So, don’t get hooked on that name. Yes, we’re also going to be going over some of the changes to the cardinality estimator and we’re going to discuss in-memory tables and queries and I’ll show you information about updateable columnstore storage. But really, the 2014 stuff is icing on the cake. The majority of the work is going to be good old fundamental query tuning.

If that name has been putting you off, please don’t let it. Go here, register for this event. There are a few seats left.

Oct 15 2014

Thank You: #passvotes

The results are in and it seems that I’ve been elected to a place on the PASS Board.

Thank you.

I will strive to be worthy of the position you’ve placed in my hands. I will do my best to make a difference on the board. Watch this space for regular updates on what I’m doing as your representative. If you don’t see updates here, feel free to call for them. It’s the one promise I made during the election and I think you should hold me to it. Worth noting, I’ll be speaking for myself, as a member of the board, not as an official spokesman for the PASS organization. It might be a distinction without a difference, but it’s worth noting.

Thank you to Sri Sridharan for your service on the board. I’m sure you’re going to be missed.

Congrats to JRJ and Wendy for getting reelected. You’re making a positive difference and I’m glad it’s going to continue.

Thanks again to all who voted, whether you voted for me or not. You’ve done your part to influence how you want the PASS organization to be run. Now, please, hold the board responsible for doing what you want with your organization.