Sep 30 2011

SQL In The City: LA

Just a reminder that there are a few seats left for SQL In The City: LA on the 28th of October. It’s a free event put on by Red Gate Software. I’ll be speaking there. But much better than that, you can listen to, and interact with, Steve Jones, Brad McGehee, Ike Ellis, Aaron Nelson, and ta-da, Denny Cherry, and double-super ta-da, Kalen Delaney (line forms behind me to talk to Kalen), plus the developers and program managers from Red Gate software that will be there.

Take a Friday off work and go get your learn-on. Show your boss the agenda. I guarantee they’ll let you go. Click here to register.

You know what else? If you pick up a copy of MVP Deep Dives II before the event, I’ll bet you it won’t take much cajoling to get autographs from some of the authors and editors.

Let me list it out: Red Gate, MVPs, Free Training, Great Networking, Books, Cool Software, Red Gate Pale Ale…

See you there!

Sep 26 2011

Book Review: Smarter, Faster, Cheaper

In my continuing quest to not get personal visits from Buck Woody (blog|twitter) I’m making sure that I make good on my commitment to read 12 personal development books in 12 months. We’re up to #4 (again demonstrating the degree of fear that Buck can put in a person) and the book is Smarter, Faster, Cheaper: Non-Boring, Fluff-Free Strategies for Marketing and Promoting Your Business by David Siteman Garland (blog|twitter).

The extended title on the book covers what it’s about quite well. David Garland is considered one of the top marketers these days and he seems to follow the processes laid out in his book. I say this because I received a tweet from him after I tweeted that I’d finished reading the book. One of the processes laid out is to set up search routines to keep an eye out for your name, your companies name, your book’s name, and go to where those things are being posted and respond, in person.

I can sum up a huge part of the book in those last two words, in person. He’s very much about the concept that what you’re selling is not a widget, but yourself and that the more and better you sell yourself, the more and better your widgets will sell. The focus of the book is on marketing and selling, which is good, that’s what I expected and wanted. I’m just still having trouble wrapping my brain around the fact that I’m in sales & marketing. But, the good news, for me anyway, is that David Garland’s idea of sales is to generate useful content. Wait, what was that? You mean writing books, blog posts, articles, recording videos and presenting online & at user groups is sales & marketing? Sweet! I’m in favor.

The point is, you have to move away from the ideas of marketing the old way, buying commercial time on TV and move into using the intertubes to do your marketing. That marketing is done by becoming a trusted advisor, or as Seth Godin has it, a linchpin. You do that by generating material, like this blog, and handing it out for free. You take part in discussions in forums, twitter, whatever, and grow yourself into a trusted resource THEN, you carefully sell. I’m sold. Of course, I’ve been sold. I got this message a year ago, right before I changed my career path. The book goes on to discuss various mechanisms of engagement and production you can use to build up that material which will turn you into a trusted resource. There’s a lot of great advice about how to manage your online presence, how to overcome fear (of failure, success, what have you), produce video, and probably most importantly, building a community. The book is all about building out a community of people that you help and who in turn help you. A real community, not just a bunch of readers or viewers, but an interactive group of individuals. Again, I’m in favor.

Unusual enough for a modern book, he has an Index. In fact, he has a good index. When I saw that I had to check to see if I was reading one of my history books or a technical book. No one puts indexes in books any more (apart from historians & geeks). Excellent.

So that’s what the book is all about, how did it affect me, personally? I have to say, I’m a bit… meh, about it. Don’t get me wrong. It’s a very well written book and I believe in what David Garland is pushing here. I think he’s 100% correct and I think the book would be very useful to others. Unfortunately, I’ve read several of Seth Godin’s books and I’ve been pursuing this line of approach already, so… while the book reinforced the things I know, the approach I’m taken, the beliefs I’m working under, it didn’t add a lot to them. But I think the fault here is the reader, not the book. However, I still found it useful, if for nothing else the reinforcement that I’m at least striving in the right direction (which is very nice to have).

I did find the chapter on reputation, “Your Reputation in the Transparent World We Live In” … scary and useful. I’m sure that doesn’t sound good, but it is. I’ve made several adjustments to how and what I do online over the last year and I suspect I’ll be making a few more changes based on this. I’m just nervous about going too far and becoming boring (right, right… more boring).

Anyway, on to the next book, Free Agent Nation: The Future of Working for Yourself by Daniel H. Pink.

Sep 22 2011

SQL Server Execution Plans

PASS_2011_SpeakingButton_180x180-blackI write quite frequently about SQL Server Execution Plans. I started in that area just because that’s how you figure out what a query is doing and sooner or later, we all have to tune a query. I found I was doing it sooner and more frequently. When the opportunity came up to write a book , I jumped on it. Now I find myself presenting, rather frequently, on execution plans.

One of the people I’ve learned from over the last several years is Gail Shaw (blog|twitter). I first saw Gail on stage at the PASS Summit, I think it was 2007. A co-worker of mine was picked, along with Gail, to go on stage for the Quiz Bowl. Gail was answering all the questions. If you go over to SQL Server Central, Gail doesn’t answer all the questions, but she’s involved most of the important discussions. If you read her blog and articles, Gail has also been deeply involved in query tuning and execution plans for a long time.

Why am I telling you all this?

In just a little over three weeks, Gail and I will be presenting an all day seminar on SQL Server Execution Plans. We’re going to cover the optimizer and the plan cache and we’re going to show you how to read a ton of execution plans. We’re going to go over how to spot problems and how to fix them. We’ll be examining plans from people in the audience (yeah, bring your problem plans) live on stage. You’ll get the chance not just to hear us talk, but to ask us questions, to get clarification on topics and meet other people who are learning the same stuff you are.

In short, we’re going to be having a really good time and talking a lot about Execution Plans. This is your chance. Register for the Summit and, please, register for our pre-conference seminar. You won’t be disappointed.

Sep 21 2011

MicroTraining: Performance Tuning Checklist

I’ve been playing a lot with Google+ and the Hangouts there. I love them. I think they’re opening up a whole new way of interacting and building community. Today Andy Leonard (blog|twitter) and Ken Watson (blog|twitter) were trying out the new features in G+, including desktop sharing. We were talking about how great this was and suddenly the idea of short, sharp training for small groups of people that can interact came into being. Andy termed it MicroTraining, and an idea was born.

So, Friday, 9/23/2011, at 11:00AM EDT, I’ll host a Google+ hangout. It will be public and accessible to anyone. I’ll make it one of the Air ones that gets recorded. That means that 10 people can attend & ask questions, but an unlimited number can watch.

It’s an experiment.

Please stop by. I’ll be going over a couple of slides from an upcoming presentation, Performance Tuning Checklist, that I’ll be putting on at the Connections conference that takes place in November. Please stop by.

Sep 20 2011

Statistics in Execution Plans

I was presenting on execution plans when another question came up that I didn’t know the answer to immediately. Yes, I know you’ve seen that phrase before on this blog. I love presenting because you get exactly the kinds of questions that make you think and make you learn. I’m presenting, in part, to learn, just as much as I am to teach. It was the same with kenpo. The more I taught, the better I learned the art. Wait, this isn’t supposed to be a blog post about learning. This one is about statistics.

The question was, does the execution plan have the statistics that were used by the optimizer to decide on the execution plan. And no, what was meant, was not does it show the estimated rows, which come from the statistics, but specifically does it show that it used a set of statistics named X? The answer to that is yes & no. Or, to be more DBAish about it, it depends.

Let’s take a really simple query run against a freshly installed copy of AdventureWorks2008R2:

[sourcecode language=”sql”]SELECT p.BusinessEntityID,
p.FirstName
FROM Person.Person AS p
WHERE p.FirstName LIKE ‘Toni%';[/sourcecode]

This query generates this execution plan:

Stats

Yes, a very sophisticated and hard to understand execution plan. Now, here’s the deal, there were two sets of statistics used to make this plan, but only one of them can be seen in the plan. See any statistics there? Sure you do. IX_Person_LastName_FirstName_MiddleName. That is one of the two sets of statistics that were used to make this execution plan. Where’s the second set? Not in the execution plan.

[sourcecode language=”sql”]sp_helpstats N’Person.Person’, ‘ALL';[/sourcecode]

The results are here:

statslist

There is the second set of statistics used for this query, right at the top. Because I was searching the FirstName column, the optimizer found that it did not have the statistics it needed, so they were created, on the fly, and then, were not a part of the execution plan. Further, I probably looked at other statistics such as the PK_Person_BusinessEntityID because that’s the clustered index for the table. It could have scanned that to get the list of values just as easily as the other index. But, that other index is probably smaller, which means fewer pages scanned.

So, back to the question, can you see the statistics used by the optimizer inside the execution plan? Some of them, yes, but not all of them.

Please, if I’m presenting, ask questions. I’ll know a few of the answers, right off the top of my head. Others will make me go and learn so that I can answer the next person who asks the same question. I don’t mind losing at a game of Stump the Chump, so let’s play.

Sep 16 2011

St. Louis SQL Server Users Group Feedback

You know I share what feedback I get from conferences. I don’t usually get feedback from users groups (well, I do, but it’s seldom written down and the bruises heal eventually). The St. Louis SQL Server Users Group did collect information. Since I share the other stuff, I may as well share this too.

They didn’t have a metric. It was just written down comments. Here are a few, my comments, as usual, will be in parenthesis:

  • Enjoyed the demos; Everything worked (ditto, the enjoying demos working part)
  • Good speaker; just dislike presentations w/ remote speakers.(Me too. I prefer interaction. I like seeing heads nodding or shaking or eyes rolling up so I know if I’m covering things well enough. Remote presentations are hard & can be very boring to watch)
  • Would have liked to have seen more how to fix the query with the messages provided by SQL. (I’m a bit at a loss for this one. The whole session was on how to fix the issue, parameter sniffing, and how to spot it, so I’m unsure what I missed)
  • Good presentation –very clear, good demos
  • Enjoyed the lively pace and examples

There were some more, but you get the idea. Thank you to everyone who submitted feedback. It’s always appreciated. It’s the one gift you can give people who present for you, so when given the opportunity to give that gift, please, do so.

Thanks again to the St. Louis SQL Server Users Group for allowing me to present. It’s really appreciated and I hope, next time, I can get out there in person.

Sep 15 2011

Probe Residual on Hash Match

I have to say, I only recently noticed this on a tool tip:

image

and this in the property sheet:

image

The bad news is, I noticed them while presenting. The worse news is, I said them out loud and then, inevitably, someone in the audience, I forget who it was, might have been Neil Hambly (blog|twitter) said, “What’s that?” Which left me standing there with a slack-jawed expression (one that comes naturally from years & years of practice). I didn’t know. I couldn’t remember having seen one before.

Here’s a query that you can run in AdventureWorks2008R2 to get a look at this critter:

[sourcecode language=”sql”]SELECT  soh.PurchaseOrderNumber,
soh.AccountNumber,
p.Name,
sod.OrderQty,
sod.LineTotal,
cc.CardNumber
FROM    Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product AS p
ON sod.ProductID = p.ProductID
JOIN Sales.CreditCard AS cc
ON soh.CreditCardID = cc.CreditCardID;[/sourcecode]
The entire execution plan looks like this:

image

We’re focused on the Hash Match join at the top. For logical processing it would be the first operation. For physical operations it would be the last.

The way a Hash Match works is by creating a hash table (in tempdb by the way) and making a hash value. Then, it makes a hash value of the stuff being compared and tries to find matches in the hash table. That’s it.

The residual is if there are additional predicates that also must be matched in order to fully satisfy the query. That’s all. It’s actually quite simple.

What are the implications? Well, this is where it gets fun. You see, the first match, in the hash has to take place, and then, it also has to do the residual probe. The first match is part of the process. The second match is additional work. That’s not good. You can see it in this example plan because the Hash Match operation is estimated as the most costly and that’s probably true.

The key is, drill down to understand what your execution plans are up to.

Sep 06 2011

Meme Monday: Favorite PASS Memory

Have to complete my assignment from the Rockstar this month since I’ve missed the last several (he gets all weepy, it’s not pretty). The question this time is to describe your favorite PASS moment… Wow! And this is Tom’s idea of an easy assignment. How do you choose? Lots of stuff flashes through my head, a football helmet with antlers & the song Funky Cold Medina, learning Policy Based Management from Buck Woody (blog|twitter) while simultaneously laughing my ass off, a pile of neatly folded clothes in a bush, friends old & new, speaking for the first time and running out of material 15 minutes early, sessions with great information from really smart and helpful people, Dr. Dewitt nuff said, sitting down to breakfast with people that wrote books I’d read, a couch in the Men’s room, kilts, showing my boss a solution that I picked up at the Summit… I could keep going. I love the PASS Summit. It’s fun and educational and inspirational.

My favorite memory is rather long & involved. I went to the PASS Summit for the first time in 2005 in Dallas. I was not any kind of insider. I didn’t know anyone there except the guy from my company that attended with me. We did the usual for the first two days, hit the sessions, hit the vendors, collect swag, head back to the hotel room (and no, you should not do this). But, we scored an invite to a vendor party, my first. It was OK. Free food and free beer was the reason I went, but then I saw these people that all knew each other. They were laughing and clearly having a good time. I asked who they were, thinking a company or MVP’s or something, and got the word PASS volunteers. Hmmm…

I volunteered.

I spent the next year on phone calls and exchanging emails in my first volunteer assignment with PASS on the Book Review Committee. It was some extra work for me during the year, but nothing horrible. Plus, I got to get a couple of free books as long as I wrote reviews. It was good and I made friends with a couple of other volunteers. Then, time came around for the Summit. I went. But this year, it was different. I started getting introduced to people because of the contacts I had already made. I didn’t go back to my hotel right after the last session the first night. Instead I was out with the volunteers and I was meeting other people, MVP’s, vendors, authors, speakers. And it kept going. It was the best conference I had ever been to. Yes, the sessions were great, but so was all the time spent talking to people.

I made contacts that I still treasure to this day. I made friends that I treasure even more. I’ve gone on to volunteer multiple times with PASS in several different roles (although I’ve been off the volunteer grid mostly this year, I need to change that next year). I’ve spoken at  the Summit the last three years and I’m speaking again this year. I helped organize a local user group. I got involved.

That involvement, that’s my favorite memory from PASS. I learned a lot, kind of late in life let’s be honest, and gained a lot just by staying out of my hotel room for one evening in Dallas.