I’ve put together a new Curah! of links to some of the better articles on SQL Server Statistics, specifically those for SQL Server 2014. The changes in the cardinality estimator can’t be ignored. If you want to talk about statistics, I’ll be presenting at Live360 in November on that topic and several others. Go here to register for this event.
I’m learning how to speak German. Interestingly enough, you don’t start off reading dissertations. Instead, you begin by learning the names of things, Teller for plate, Buch for book. The fundamentals. I’m a third degree black belt in Ken Ryu Kenpo. But you don’t start that, or continue it, by learning complex kata. Instead, you start with how to make a fist, how to hold your hands up in a defensive stance. The fundamentals. I’ve been doing crossfit and Olympic weightlifting for a couple of years now. I’ve been working hard on my clean, standing up tall during the lift, getting my elbows around quick. The fundamentals. Situation after situation, skill set after skill set, you have to get the fundamentals right. And, if you don’t get the fundamentals right, you’re going to find progression extremely difficult.
Which brings us to IT. Let me pick on developers for a moment. You know that ORM tool you want to use? It’s actually a pretty amazing piece of software. Too bad you glossed by all the documentation on how to use it properly, skipped the reams of best practice documentation, and simply took the “Hello World” example to deploy it to production. Because now, that great piece of software is creating serious pain for the company. You skipped the fundamentals. Data pro’s, seriously, you’re asking how to turn off transactions in SQL Server because maintaining the transaction log appropriately is too hard? You have completely skipped the fundamentals. You’re of the opinion that foreign keys are a major performance bottleneck? Fundamentals. You think you can do a scale-out ID/Value data collection scheme in a relational database? Fundamentals.
But none of that is the issue. You want to know what the issue is? PowerPoint templates. Oh my Freya’s Girdle is on fire flipping gods in Valhalla, do you have any idea what a pain in the bottom it is when you have 100+ slides, in a single presentation, that you have to convert to a new template for ConferenceX and the template was constructed wrong? Your fonts go insane, colors dance across the screen like the Bifrost Bridge on an acid trip. And then, when you attempt to fix things by reapplying the template, you find out that oh, noes, we didn’t use the background, we just pasted our graphics on the screen (which takes up 1/2 the real estate forcing your fonts to be so tiny that Buck Woody can’t hear you) so now you’ve got to carefully navigate around extra stinking graphics everywhere, oh, AND, your presentation is now 19 times the size it was before, so your machine is running out of memory to run your demos before you even get the VM started. And then you have to touch each and every slide and adjust them, one at a time. Aren’t we data pros all about set-based processing?
Please, if you’re responsible for a conference, whether you’re running a SQL Saturday event, or you’re the chair for a track at the largest of international venues, do all your speakers a favor. Please, take a set of slides from online somewhere and try to get them to work with your template. If you can’t (assuming those slides aren’t already a hack because of ConferenceY down the street who is even worse than you are), work with your artists or whoever put the templates together to get those templates to work well. Otherwise, you might as well just come out and say that you hate speakers and you’re getting a mighty giggle from the work they’re going to have to do to try to make your hag-ridden nightmare of a template work with the beautiful slides they spent weeks putting together.
This month I’m very grateful because I was given the opportunity to present at DevLink in Chattanooga. I got to meet a lot of new people and see presentations by people that just don’t hang around SQL Server specific events. It was great. I’m going to apply next year (depending on scheduling of course) and I’m applying for a lot more development conferences. I still get to see friends present, Louis Davidson, Kevin Boles and Kevin Kline were all there. But I get to see new people. Speaking of which, speaker of the month for September 2014 is Josh Lane (b|t) and his presentation AWS vs. Azure, Which One Is Right for You.
The thing that I found the most amazing about this presentation was how even-handed it was. Josh Lane really went out of his way to find positive (and negative) things to say about both platforms. Because of this, I think anyone really trying to understand which platform to pick, and, more importantly, why they should pick it, walked away with good data. I’m working with Azure regularly and I picked up a few things that I didn’t know. I also liked how he identified lots of commonality between the two platforms. The slides were very simple and clean and well structured. Mr. Lane’s delivery was excellent. He was engaging, handled questions well, and kept things moving. I was really impressed.
The one issue I have with the presentation is that there was really way too much material. He didn’t just rush through the last of it. He skipped right by sections. And those sections looked interesting. I’m pretty sure this was put together for a venue that had more than an hour of time, but I don’t think he would have made it through on a 75 minute schedule either. So, as much as I hate to say it, I’d suggest trimming some material. Going quickly through some doesn’t usually make people too angry, but skipping stuff entirely makes them feel ripped off. But, what a great problem to have. Too much awesome, well delivered material.
Thanks for the session Mr. Lane. I’ll keep an eye out for other material from you in the future.
No, this is not about politics. It’s about your WHERE clause… and your JOIN criteria… and your HAVING clause. It’s about a canard that still makes the rounds occasionally. Please, help me put this statement to sleep for once and all:
A function on the left side of the equals sign can lead to performance problems
Well, you know, it’s sort of true. But then, a function on the right side of the equals sign can also lead to performance problems. In short, it’s not the placement of the function that causes issues, it’s the function that causes issues. Let’s take a look at a really simple example:
SELECT a.AddressID, a.AddressLine1, AddressLine2 FROM Person.Address AS a WHERE a.AddressLine1 = 'Downshire Way';
This simple query results in an equally simple execution plan:
Now, if we decide that we want to do something like look for all results that have ‘Way’ in them. It’s a different result set, but our index could be used for the new result set. The query will get modified to this:
SELECT a.AddressID, a.AddressLine1, AddressLine2 FROM Person.Address AS a WHERE RIGHT(a.AddressLine1, 3) = 'Way';
That’s a function on the left side of the equals sign. OMG!!1! The execution plan isn’t as nice any more:
So, if we change the query to this:
SELECT a.AddressID, a.AddressLine1, AddressLine2 FROM Person.Address AS a WHERE 'Way' = RIGHT(a.AddressLine1, 3);
Whew, dodged a bullet since we have the function on the right side of the equals sign. And so we get a better execution plan now:
Uhm, wait. That’s still a bad plan isn’t it? Why yes, yes it is. That’s because the problem isn’t which side of the equals sign we have a function, but the fact that we have a function on the column at all. There are a number of permutations we can get into around this. For example, what if, instead of putting the function on the column, we put it on the string, to only match to ‘Way’ instead of ‘Downshire Way.’ Well, that would fix the function issue, but then, we’d have to use a LIKE command and add a wild card to the beginning of the string, resulting in scans again. But the fundamental concern remains, we’re not talking about the left or right of the comparison operator, we’re talking about the existence of the function on the column.
Please, don’t repeat this one any more. OK? Thanks.
I don’t mind saying that amyotrophic lateral sclerosis (ALS), also known as Lou Gehrig’s disease, scares me. A degenerative disease that takes away the minds ability to control the body, leaving the mind intact… words fail me. Add to that the fact that there is no known cure, and that there are at least 30,000 people suffering from this, just in the US, something has to be done. You can give money over at the ALS Association. Please do.
Please donate for this important cause.
As companies go, Red Gate is a little different. That is readily apparent in our tools and the philosophy behind them, ingeniously simple. But, we do a lot of other things too. There’s the Simple-Talk web site where we publish serious articles on all aspects of development and database administration across platforms and programming languages. There’s SQL Server Central, the single largest SQL Server community on the planet. There’s Ask SQL Server where you can get direct answers to your direct questions about SQL Server. If all that’s not enough, there are all the books, which we give away for free, on, again, all aspects of programming and database administration. But, we like to do more, so we also bring you training, the Red Gate way, at the SQL in the City events.
We’ve got two more SQL in the City events coming up soon. First, we’re back in London again on Friday, October 24, 2014. This event is one of my favorites, every year. We’re bringing in MVPs like Steve Jones, Ike Ellis, Brian Randell and others, all to teach you about SQL Server, but we’re doing it the Red Gate way. So please, register for this event. I’ll see you there and we can share a frothy beverage (it’s Red Gate).
Next, I’m thrilled to say that we’re going to be in Seattle on Monday, November 3, 2014. That’s right, just before the PASS Summit. If you wanted a reason to get out to Seattle early, here it is. We’re bringing a lot of the same crew from the London event over to Seattle. You’ll be able to experience what the London people did and more. This is SQL Server training done right, that is the Red Gate Way. Let’s get together and talk and share a frothy beverage in the States. It’s a free event, but there’s limited room, so please register now.
These are unique and popular events. We pull out all the stops to make them fun, special, educational, useful, helpful, doggone it, good. Please, come out, talk to me, talk to the Red Gate team, help influence the tools that you use every day, and learn about SQL Server.
A discussion that I’ve frequently had with organizers of SQL Saturday events, our own people here at Red Gate, authors, MVPs, pretty much anyone interested enough to listen for a few minutes, is summed up by “How do we get the word out about the opportunities that the SQL Server community offers?” The question always comes down to, how do we reach people? We tweet. There’s a Facebook page. Discussions are hosted on LinkedIn. Emails are sent out to various distribution lists. Advertising is done on SQL Server Central (with over one million registrants, what else do you have to do?). And yet, at events, I’ll ask, who has heard of PASS and will only get a 50% positive response. Heck, I’ll never forget that at the Charlotte SQL in the City event last fall, when I asked the audience, about 200 people, who was not aware that a few blocks away there were 3,000 people gathered to learn about SQL Server, about 1/3 of the audience did NOT know about the event occurring in their flipping back yard. In short, the communication means we’ve been using, don’t work. What to do? How do we reach those people who just aren’t getting the word?
Here’s a suggestion. Talk to people. No, not online. In person. Here’s a survey from an event, DevOpDays MSP, that asked people, how did you get here. The overwhelming answer is word of mouth. So, how do we get the word out? We need to talk to people. I know you’ve mentioned the community to your co-worker or your friend, but it’s time to do it again. Talk to them. Let them know about the upcoming SQL Saturday or user group meeting. Tell them about Connections and how great your learning opportunities are there. And, here’s the new wrinkle, ask them to pass the word on to some people that they know as well. Look at that survey. Over 50% get the good news by word of mouth. So let’s start leveraging that.
I’m excited to able to say that I’ve been given the opportunity to put on a full day workshop at SQL Connections on Friday, September 19th, 2014. The title is “Query Performance Tuning in SQL Server 2014″, but I assure you we’re going to cover things that are applicable if you’re still working on SQL Server 2005. We’ll start the day covering the different mechanisms you have to capture query metrics. We’ll go over dynamic management objects and extended events that are incredibly important to you in understanding which queries you need to tune. We’ll get an introduction into how the optimizer works and the importance that statistics, indexes and constraints play in helping the optimizer make the choices it makes. I promise, execution plans will be covered throughout the day, in great detail, because they are a fundamental part of how you’re going to understand the choices the optimizer made. Then, we’re going to go through common problems, how you can identify them, troubleshoot them, and solve them. We’ll get rid of a lot of myths and just easily fixed issues. Throughout the day we’ll be covering both older versions of SQL Server as well as SQL Server 2014. Then, to finish out the day, we’ll go over some of the new opportunities that are unique to SQL Server 2014, their use and their shortcomings.
In short, I’m trying to take you from not knowing which queries you need to tune, to identifying those problematic queries, understanding what the problems are and how to solve them. You’re going to know where to go to get started reading execution plans. You’re going to walk away with a love and fascination for extended events. You’re going to get tools and methods that you can apply to your own code, your own applications, your own servers. And, this all takes place after an amazing week of learning at the IT/Dev Connections event in Vegas. Please click here now to register.
I was approached by a man at SQL Bits who only wanted to thank me for blogging. He said, “I know you probably never hear from anyone, but you should. Thanks for what you do.” I’m not mentioning his name because he promised me he’d write an editorial for publication, so I’m going to wait on him to get that done so he gets full credit then, not on my silly blog post.
But, the idea stuck with me.
I realized, that I don’t do it. Oh yeah, I have an “Interesting Reading” set of links that I post to Twitter, Facebook and Linkedin. Sharing is “thanking” in our modern vernacular, right? No, of course not. It’s not the same. I really do appreciate the help that I get on the internet. Some of you are youngsters and don’t remember what it was like before the World Wide Web, even before America Online, before CompuServe, before the Bulletin Board Systems. We used to have to learn everything from books. EVERYTHING. We either knew someone who knew answers, or we suffered in ignorance. We walked to school and back home through 8 feet of snow, uphill, both ways… wait, where was I? Seriously, we have it good today. Better still, the SQL Server community is awesome for it’s sharing (oversharing even). There is almost limitless, free, good, information out there. The people who are generating it deserve your thanks.
So, if you read a blog post, and it was useful, take the time to post a quick thank you. Just do it.
For myself, I’m trying to think of a new blog series for thanking people.
In the mean time, real fast, in no order, no links, but very serious and heartfelt thanks to:
Kalen, Paul, Itzik, Brent, Tom, Tim, Adam, Wes, Benjamin, Kim, Don, Denny, Allan, Allen, Erin, Steve, Andy, Karen, Gail, Johnathan, and a bunch of people I’m not thinking of at the moment.
I’ve learned so much from you guys, for free. You’ve made a positive impact in my career. Thank you. I promise to try to do more to show that appreciation in the future.
Speaker of the Month is now officially one year old.
I went back and reread my first post. The goals were for a relentlessly positive experience. Reading back through the twelve posts, I feel like I hit that mark pretty well. Yes, I’ve always pointed out places where improvements can be made, but I think I’ve done it in a constructive and positive manner. Plus, I’m picking your session (if you get picked), as the best session that I saw that month, which is pretty darned positive in and of itself. I also promised it would be random and arbitrary. Mission accomplished.
I’ve considered wrapping this up. I did it for a year, just to see what the response would be. I didn’t have much more of a goal in mind than trying to help out both attendees and speakers by pointing out what I thought were some awesome sessions. Maybe pointing out some places where people could improve would be helpful. Since I’ve been blessed with getting to present a lot, the implication is, I know what I’m doing and maybe I could share some of that knowledge with others. Done. Does it really need to continue? The feedback you get on most blogs and most blog posts is largely a null set. You toss the information out there, it sinks into the pond, the ripples quickly fade. You don’t have a clue if the blog and the posts are helping or not. I’ve received some feedback on this, but, it’s actually been mixed. I’ve had people tell me because I’m pointing out areas of improvement that I’m not being positive, that I’m in fact being mean. Which… I really don’t know where to go on that. If I just say, “It’s awesome” and walk away, is this useful? Is it helpful? Is it constructive? Will it make a positive difference to the person I’m praising? Heck, will it do anything for anyone except the person I’m praising? I’d say no.
I’m going to go with the thought that this matters, even if it is only to the people who get picked. But I think that hearing from someone, anyone, what works in a session and what doesn’t could be useful to those who are just getting started. So, assuming it makes a small, positive, difference, I’ll keep the Speaker of the Month award going for one more year.
Speaker of the Month for August 2014 is someone I just recently had the chance to meet. We had exchanged a number of emails on a topic that I knew nothing about, so I was utterly unhelpful to them. But, by explaining the problem to me, they figured out their own solution, and then turned that into a presentation. Wow! That alone is amazing. Then I saw the presentation and was blown away. My Speaker of the Month is Derek Stanley and his presentation Remove the Linked Server 2 Hop Limitation by Implementing Kerberos from SQL Saturday #302, Albany.
This is absolutely not a topic I’m familiar with or good at. I know I’ve run into the situation a couple of times. Explanations for exactly what was happening kind of pinged off my head. After sitting through Derek’s session, I have a better understanding of the problem and, I have a bunch of great solutions (that I couldn’t implement if you threatened me with torture, I just don’t know systems that well). Derek presented everything in a very clear fashion. He walked us through the problem space so we could understand what it was he was fixing, then he walked us through a bunch of different solutions. His explanations were good, but his demos really made the presentation shine. They were great. Demos can frequently be summed up as “look, I can make this work” but they don’t always teach. Derek’s were so clear and pointed that even I could understand how his solutions were working (still couldn’t replicate them). He put the presentation together really well, told a clear, concise, useful story in a way that made it easy to understand. The presentation was absolutely a win.
Areas I’d like to see improved are fairly slim. I think, my opinion, the slides were too wordy. I get the “slides are documentation” school of thought. I just don’t agree with it. That one may be a throwaway. The one area that sometimes got a little confusing was caused by naming the servers VM1, VM2, etc. I think some clearer names that somehow help illustrate the points might help the demos along some. One more word: Zoomit.
In short, it was a good presentation with great demos. If you get the chance to see this one, I strongly recommend it. Derek, submit this to more events. If you’re organizing an event, accept this session. It’s good, you’ll be happy.