Hello again. The PASS Organization has once more allowed me to sit at the bloggers table for the key note. I’ll be posting updates as the information unfolds.

[8:06] The crowd is filling in. this looks like one of the biggest crowds I’ve ever seen at the summit. The organizers have done a great job so far. Thanks for all the hard work guys.

[8:10] SQUEEEEE! I just met Dr. David Dewitt and got a picture with him. I’ll post it after I get home and find the cable.

[8:22] Watching the videos of different people on the big screen talking about what they like about the PASS Summit is really cool.

[8:26] Rushahb Mehta is introducing the board and the Partners of the PASS board. We now have a new additional structure which is three people from different regions of the world, Rob Farley Raoul Illyes and James Rowland-Jones.

[8:28] The organization is up to 430k training hours this year if you count up all the various ways the organization reaches out to people through the Summit, 24 Hours of PASS, Rally, Virtual Chapters, SQL Saturday and the PASS Chapters. The organization really has grown a lot. Plus, they’re working with SQLBits and SQL Relay in the UK.

[8:32] They have a bunch of the bloggers and twitterers up on the screen. Nice to see everyone. It’s also nice to say that I know most of them and I’m friends with most of them. This is an amazing community.

[8:36] The different ways that we have for networking are excellent. The First Timers method for introducing people to each other has really taken off from last year. Then there are all the receptions and the Women in Technology lunch. There are just some great ways to make connections within this community and those connections really will help you in your job and your live.

[8:41] Yes, please visit the vendors and thank them, even if you don’t buy anything. And fill out your eval forms, it’s the one give you can give back to your speaker. If they did something wrong, let them know what it was. If you didn’t like their presentation, let them know why.

[8:42] Key note speaker is Ted Kummert from Microsoft. His topic is The New World of Data

[8:44] No other way to describe this, but we’re going down the marketing path now. The appliance choices in SQL Server are expanding and becoming more and more attractive to the enterprise, no question. SQL Server has a number of versions and service packs released over the last year. Azure is still a choice that Microsoft is making. Whether or not others are making that choice is still very much up in the air. Marketing or not, Microsoft is clearly committing 100% to this. Reporting and cubes are being set out there too.

[8:49]Denali: It’s coming. There are lots of new mechanisms and structures, especially supporting BI and knowledge management.

[8:52]First bit of news, Project Crescent will be released as Power View.

[8:53] We have a new name people, no longer referred to as Denali, but now referred to as SQL Server 2012. It’s official. Further, the release date is the “first half of 2012”

[8:55]Microsoft’s plan is all about “any data, any size, anywhere.” Concept is fine. Implementation is the question though isn’t it. Microsoft is starting to talk about Big Data. No definition yet on what that means. Ah, they’re going to support Hadoop as part of the product suite. Cool.

[9:00] Announcements, they’re going to support hadoop, odb driver & add-in in Excel for Apache Hive and a JavaScript framework for Hadoop. They’re going to partner up with HortonWorks as part of getting Apache Hadoop going.

[9:02] Now talking Eric Baldeschwieler, the CEO of hortonworks.

[9:04] Denny Lee, Microsoft developer is now coming out to show us how to move hundreds of millions of rows using Hadoop and Azure. Now, I’m excited. Denny Lee is a good, excited, informed presenter. This is what we like. Nerds excited about technology sharing the knowledge and passion.

He’s starting with the Hive ODBC driver in PowerPivot to connect to over 300 files. The ODBC driver will be available next month, so PowerPivot and Hadoop are connecting.

He’s pulling from a multi-node hadoop cluster to an excel workbook. He’s showing reports through Excel into hadoop data. It’s nice. No question.

[9:16] They’re working on capturing everyone’s data that is going to allow people explore everyone’s data. It’s called Data Explorer and will be part of Azure labs later.

[9:18]Introducing Contoso Frozen Yogurts, the self-service yogurt company… Anyway, it’s a sample set of data showing locations & performance scores of the various stores. They’re using that to relate to other global data available. So the machine is going to start learning what data might be interesting to you.

[9:28]Unfortunately, these demos, while somewhat interesting, were pretty dull, that, and the fact that I’m sitting with a bunch of witty people with ADD, we’re suddenly paying no attention.

Look, it’s interesting, but there is a TON of code behind the scenes to make this stuff happen. And, all that code has to be on Azure, and once it’s put together, you can pull it down to Excel.

[9:34] Power View is all about getting data out to the business people. That’s good. Amir Netz, Technical Fellow at Microsoft, is up to do a demo.

[9:36] I’m taking off a little early today. There’s a big announcement down in the expo hall from Red Gate Software at 10:05 this morning. please keep an eye out for it. It’s going to be HUGE!

I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge.

Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are. Which is why most anyone who wants to learn about extended events should plan on starting at one place, Jonathan Kehayias’ blog. Yeah, the Books Online help get you started, but Jonathan really makes it all take off.

But why is this huge? I’m not going into a low-level discussion here, because frankly, I’m incapable, for that go back to Jonathan. What I will say is that you have a mechanism that captures events at their source instead of after the fact. That makes it faster and causes less impact on your system. Further, you can put filters in place at the event level that make it so that you only capture the information you want, when you want it. In short, it’s about control, reduced impact and increased knowledge.

So why write about it now? Because Denali has finally provided a GUI that simplifies the process of setting up extended events. There’s a wizard for building extended events sessions, and a session management GUI that you can use to manage them on your own. Basically, you have a way to get past the confusing and odd TSQL implementation and start using these things.

Check it out:

It really does provide everything you need to get going with extended events… or does it? There’s still the issue of knowing how these things work and what they do. The GUI above enables you, but it doesn’t provide much guidance. For example, there’s a very enticing little action (stuff collected from events are called actions or fields) called sql_text. When setting up my first, simple, session (the one you see above), I saw that action and thought, ah-ha, there’s my collection point for getting the sql batch and the procedure call. But then, on testing, it didn’t work. After a plaintive call for help on Twitter (hash tag #sqlhelp for those who don’t know), Jonathan rode to my rescue and informed me that despite the enticing name, that’s not what I wanted.

Instead, each event has event fields that are simply a part of the event. Global Fields (actions, whatever, told you extended events were confusing), have an added overhead, so you have to think twice about using them. Plus, sql_text didn’t return any data for sql batch calls. No, instead, for the rpc_completed, I needed to read the field, statement. Excellent! All set. Well, no. There’s still another wrinkle. For sql_batch_completed there is no statement field. Instead, I need to collect sql_batch_text. Ah, good…. but wait, now I have two fields that get returned and I have to figure out which of these I use instead of just going to a single source like I used to with trace…grrr… Why does Microsoft hate me? And you?

Anyway, I’ll be weaving extended event sessions throughout the book. You can use these things for looking at waits, deadlocks, ooh, all kinds of stuff. I did say powerful and huge right. Keep an eye out for a few more blog posts.

Also, if you want to talk to me about gathering knowledge for your performance tuning efforts, then you should make your way to SQL In the City: Los Angeles. I’m going to be there with a slew of other MVPs on October 28th. It’s free. Click here now to register. My session is called Performance Tuning With Knowledge, and it’s all about gathering the information you need to make your performance tuning decisions.

No, I’m not talking about hubs and switches. I’m talking about people. Networking is a major component of an event like the PASS Summit. Whether you’re networking with individuals or with organizations, this is an opportunity to build direct, personal connections with people that can, and will, help you in your professional career. With the economy behaving as it is, having a better network gives you an edge over your peers. You’re going to be better and faster at solving the tough problems, not because you’re smarter, but because you have contacts that have already solved that problem (which, actually, means you are smarter) and you can go to them for the solution.

I’ve been “that guy.” (I can’t be be “that gal” even if I tried). You know the person. They show up having flown in the morning of the event, they hit sessions all day, sit in a corner at lunch not making eye contact, repeat for 3 full days with time off in the hotel room, then fly home. Afterwards, they talk about, “Well, it was OK, but I didn’t get that much out of it.” Really? No kidding? Maybe it’s because you didn’t put anything into it. Just a guess.

Maybe you’re like me and you’ve spent a lot more time trying to figure out how to work on computer than you have people. Not a problem. I’ve got the solution for you. Don Gabor (blog) is back at the PASS Summit for his third year in a row. He has a two hour mini-seminar called Networking to Build Business Contacts. If that doesn’t sound perfect for the Summit, then you’re not paying attention.

I’ve taken the class with Don two years ago and it was really worth it to me. Further, I’ve read one of Don’s books and I was privileged to publish an article he wrote for our short-lived attempt at reviving the SQL Standard. His subject matter is serious and important, but Don’s approach is light, fun, approachable and incredibly useful.

If you’re that guy/gal, stop. You don’t have to be. If you need that leg-up on how to get involved, then you’re absolutely in luck. Take Don’s session. You won’t be disappointed. Then network your behind off at the Summit and get that extra edge you need for your career.

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.

I 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.

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.

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:

SELECT p.BusinessEntityID,
p.FirstName
FROM Person.Person AS p
WHERE p.FirstName LIKE 'Toni%';

This query generates this execution plan:

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:

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.

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.

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

and this in the property sheet:

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:

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;

The entire execution plan looks like this:

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.

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.