Mar 19 2014

Query Tuning Near You

It really is so much easier to just throw hardware at badly performing databases. Just buy a bigger, faster server with more and faster disks and you can put off doing tuning work for another 6-9 months, easily. But, for most of us, sooner or later, our performance problems get so big or, we just don’t have any more money to spend, and we’re stuck. We have to tune the queries. And frankly, query tuning is a pain in the nether regions.

But, after you’ve tuned queries 20 or 30 times, you start to recognize the patterns and it gets easier (never easy, just not as hard). But, if you haven’t done it 20 or 30 times, what do you do? My suggestion, talk to someone who has done it 30 times (or even a couple of hundred times), like me for example.

I have an all day session on tuning queries. It goes from understanding how the optimizer works (which will automatically lead you to write better queries), to how to gather performance metrics (so you know where the pain points are located), to reading execution plans (you need to know what has gone wrong with the query) to various mechanisms for fixing the query. This information is applicable to systems from SQL Server 2005 to SQL Server 2014 (sorry everyone still on 2000, it’s time to upgrade). The session is based on the books I’ve written about query tuning and execution plans, plus years and years of doing lots of query tuning.

Right now I’ve got two events scheduled. Before SQL Saturday #286 in Louisville, KY, I’ll be putting on this precon. We’re limited to seating, so don’t wait. You can go here to register. Then we can get together the next day at the SQL Saturday event to get some more education from all the great speakers there. Next, before SQL Saturday #302 in Albany, NY (their first one, ever), I’ll be hosting this. You can register by clicking here. Don’t miss the early bird special. Again, the next day will be filled with learning at the SQL Saturday event.

I’m working on taking this to other locations and venues. If you’re interested, please get in touch. I’ll do what I can to come to you.

If you have a particularly thorny query, bring it along with an actual execution plan. If we have time at the end of the day, I’ll take a look and makes suggestions, live (uh, please, no sensitive patient data or anything like that).

Let’s get together and talk query tuning.

Feb 26 2014

SQL Intersection, Spring 2014

I am terribly jazzed to be involved with this amazing event, SQL Intersection. It’s featuring some truly amazing speakers presenting on important topics. It’s being held here on the East Coast, right near the Mouse, the Duck and Dog. This is one of those conferences you need to get to. Check out the lineup. That is some of the smartest, most capable people I know. I’m quite humbled to be on the list with them, so I’ll do my level best to deliver good content. Look at the sessions. While I don’t know precisely when SQL Server 2014 is coming out, I’m sure it’s real soon, so this will be a great place to get a leg-up on understanding what this new set of technology offers, or just learn more about SQL Server in general, Azure, SSRS and SSIS.

Click here now to register for this special event.

Oct 22 2013

SQL in the City, US Tour 2013, Recap

WP_20131009_004Red Gate visited three cities this year with our SQL in the City event; Pasadena, Atlanta and Charlotte. I just wanted to give you a quick assessment of how the events went from my point of view.

Overall, each and every one of these events was awesome. I can safely say that because each and every one of these events provided something special, the opportunity to network with our peers and with the developers and project managers at Red Gate (who are also our peers, but not usually available to us). I both took part in the networking and stood back and watched it happen. I love seeing a bunch of data pro’s sitting (or standing) in a circle exchanging war stories, ideas, questions, thoughts or suggestions. It means you are building your skill set by extending your network. I saw cards, email and web addresses exchanged. That alone made these events a raging success.

But, it got even better. I also took part in  lots of discussions about how people are using Red Gate software to make their jobs easier. I saw lots of people talking to the developers and project managers. This means you’re very likely to see new and better solutions coming out of Red Gate to make your lives a little easier. The opportunity to provide feedback is one more reason to attend an event like this. The ability to interact with people that are building the tools you use in order to supply them with the information they need on how to make that tool better serve your needs is the kind of opportunity that should never be ignored. It’s one of the reasons I encourage people to go to the Microsoft booth at PASS or TechEd and to visit the SQL Clinic at PASS. You don’t get that many chances to talk to the guys who can directly help you. But we provided that at SQL in the City. Another huge success.

Oh yeah, and there were learning opportunities through presentations in sessions. In addition to talks from Steve Jones, Red Gate developers and me, we had guest speakers helping us out. Mickey Steuwe (b|t) did a session in Pasadena and Charlotte on “Improve Your T-SQL by Changing Your Writing Habits.” Oh, and somehow you attendees got real confused and more of you went to Mickey’s session than to mine. Let’s keep an eye on that (ha! Kidding). Also in Pasadena Ike Ellis (b|t) gave a session called “SQL Server Tips and Tricks.” In addition to that, Ike sat down and walked me through his version of my talk on database deployments. I think Ike’s version of the talk was better than mine in a lot of ways. We may need to get Ike in to give that session. Trust me, you’ll like it. Finally in Pasadena, Ernest Hwang (b) gave a session called “Database Build and Release.” I’m sorry to say I missed the opportunity to meet Ernest.

Atlanta saw a different group of speakers. The touring speakers were the same, but we also brought in Tim Radney (b|t) for a session called “Life as a DBA.” Stuart Ainsworth (b|t) was also in Atlanta doing a session, “Simplifying SQL Development.” Both these guys are good friends and I loved hanging out with them. I have to say, I really like how Tim has his kids cut their hair. You should see the girls (kidding again).

In Charlotte we were able to pull in more speakers. Mickey again (although it’s unclear who had more people this time) with her session. I have to watch this session at some point because it sounds interesting and Mickey is a very good presenter (although, having spent a year as her mentor, I’m not in any way, shape, or form, prejudiced). We got one of my favorite speakers, and writers, Louis Davidson (b|t). I’ve followed his stuff for years and can’t recommend him enough. His session was “Database Design Fundamentals.” Kevin Boles (t), Ex-Navy Nuke O-ganger but still a good guy, did a session called “Windowing Functions: The Reason to Upgrade to 2012.” We also got to have Nigel Sammy (b|t) do a session on logs and backups which I actually got see. It was great. So if you get the chance to check out “Understanding and Controlling Transaction Logs” I can recommend that. Finally Kevin Hazzard (b|t) did a session called “Schema Change Management.”

At the end of each day we had an extended networking session including some networking lubricant (frothy beverages) which is always good.

I’d be remiss if I didn’t point out a few things that didn’t go well (although I’ll take a beating for this later). I hosted my first panel discussion in Pasadena and I really messed it up. I won’t be at all surprised to find it at the bottom of the ratings. I’ll prep better next time and will control the experience more directly. I should have had my own canned questions instead of constantly berating the audience to ask us stuff. Huge error.  I didn’t think lunch went all that well in Atlanta, but it was because the venue was kind of awkward to navigate creating some long lines. These things sometimes happen, but you try to avoid them. I also think my session on automating DBA tasks had just too much material in it, so I was skipping by information that people may have wanted to see. Better to offer less and deliver it than offer more and dance on by. I’ll work on that one if we decide to give it again.

IMG_7124In short, except for some small issues, I thought all three events were simply amazing. I do think the Charlotte event stood out. The venue was simply incredible. The audience, well-sprinkled with experienced PASS Summit attendees (although, only about 1/2 the audience was even going to attend the Summit and, about 1/4 of the audience wasn’t aware that the Summit was going on down the street), was very prepared to engage the speakers directly (which, at least in my opinion, makes sessions much better) with questions, comments and suggestions. Plus, I was already working on a very good SQL Family high that had been building up all week, so maybe I was a little prejudiced.

Thank you to each and every attendee at all three events. I know the organizers practically killed themselves in putting together the event. The speakers worked extremely hard prepping their material. But you make or break an event. You absolutely brought your A-game making these events wonderful.

You’ll be able to track down slides, possibly videos and audio, all at the SQL in the City web site. Thanks again. Pretty sure we’ll be at a new set of cities next year. See you then.

Sep 10 2013

New Windows Azure Virtual Labs

Getting started with new technologies can be a pain. That makes all the new labs that Microsoft just posted extremely useful. There are several that are going to be immediately applicable to your average data pro; Introduction to SQL Database, Connecting a PaaS Application to an IaaS Application with a Virtual Network, Web Sites and Virtual Machines using ASP.NET and SQL Server. The beauty of these things is that you don’t need to do anything to get started. These are virtual machines hosted out on the cloud that you get access to. Further, you’ll get a lesson plan to follow so you’re not left floundering. You will have to either connect your MSDN account to set up an Azure account or, if you don’t have MSDN, sign up for an Azure account. But, get that done and you have things to learn from.

Aug 22 2013

Get Started with Windows Azure SQL Database

CommentsLearning new things can be daunting. First, you have to come up with the spare time. Then you have track down resources. For computers, computing and programming, this is both extremely easy and extremely difficult. That difficulty is especially true when it comes to gathering resources for learning things that, while you learn, are potentially going to cost you money. It’s a difficult decision to make to risk cash on exploring a new technology. Here’s the good news, for several reasons, you don’t need to sweat this to get going with Windows Azure SQL Database (WASD).

A couple of years ago Jamie Thompson (b|t) set up an account on Azure, all on his own, that allowed people to connect up to it and play with a copy of the AdventureWorks database. This year, the company I work for, Red Gate Software, took over maintaining that database as a community service (meaning, it’s free). You can go here to learn all about how to connect up to AdventureWorks in WASD. You can connect right through SQL Server Management Studio from your own PC. Try running a few queries. See what happens when you generate an execution plan. See which of the Dynamic Management Objects you might have access to. In short, start learning. It won’t cost you a dime. But please, leave a message by running an INSERT statement to the dbo.SqlFamily table. I posted some of the recent comments in the graphic.

Pain and cost free. You’re not only able to tool around and begin to understand what is offered by WASD, but you’re immediately seeing the hybrid nature of managing data in this new paradigm. That data is out in Azure, but you’re querying it from SSMS on your desktop.

Let’s say you already have an Azure account but you just don’t feel like setting up a database, all that work… I get it. Good news. Scott Klein (b|t) has created a copy of AdventureWorks just for use with Azure. He’s published it out on Codeplex, so it’s easy to track down. Check it out and see if it doesn’t help you get started with WASD.

Did that get you hungry for more? Cool, let’s stick to free(ish) opportunities. Do you, or your employer, already pay for an MSDN license? Then, depending on the license level you have up to $150 in credit available to you in an Azure account that is limited to that credit amount and no more. That’s right, it’s free (as long as you’ve already paid for MSDN) and you can’t rack up charges because it stops all service when you hit your credit limit. Further, you don’t have to enter a credit card either. It’s a perfect way to start learning WASD. Not only WASD, but all the rest of Azure, especially Azure VMs. Did you know that you can spin up an Azure VM that will run SQL Server 2014 on a SQL Server 2012 R2 server? What’s that? Those aren’t released yet? You’re right of course, but you can get access to VMs with these running on them to start learning the changes that are coming your way and it’ll only take about 5 minutes to get set up. I detail how to do it here.

To link your MSDN account and your Azure account, follow this link. It will also enable you to get a chance to win a rather snazzy car. Win a car and expand your skill set, all at no additional cost to you. That’s a deal.

With all the money I just saved you, you can register for my all day pre-conference seminar at the 2013 PASS Summit where I’ll be teaching WASD, Azure VMs, Failover, Virtual Networks, Deployments, Troubleshooting, anything and everything about managing data within the relational offerings available through Azure. Go here to register.

May 09 2013

Book Review: Business in the Trenches

I’m trying to improve.

That’s at just about everything too. I know I don’t know enough or have enough skills to always get things done in an efficient manner, so I’m trying to learn more. One way is by reading, a lot. I’ve read a number of management and leadership books, many of them reviewed on this web site. I just finished the book Business in the Trenches. I really enjoyed it. It combined two of my passions, self-improvement and history, specifically history of World War I. Now, this is a tech, community and business blog, so I won’t go on & on about the Great War (although I could if you wanted). Instead, I simply want to provide you a link to my review of this book. It really is a worthy read that teaches important lessons in a fun and interesting way. That made me want to be sure that you all had the opportunity to learn about it.

Dec 03 2012

HDInsight, Finally

See this:

That’s right. The install worked. All I had to do was get a completely clean server set up. No domain controller. Now to get my learn on.

Microsoft has a web site with a number of introductory samples. I’ll start there and work through them. The very first example gets me set up with some data that it builds by running a Powershell script, importdata.ps1. But I’m not going to just blindly follow along. I want to see what the heck is happening so I can start understanding this stuff. By the way, thank you Microsoft for making the samples in PowerShell and not forcing me to relearn Python or something else. That would have been frustrating.

The script is really simple. It has two scenarios you can pass it, w3c or RunTraffic. They just change directory and run another PowerShell script, import.ps1, from two different directories. I’ll be the scripts are different. I’m running the w3c scenario, so let’s see what that script is doing.

Ah, now things are getting interesting. There are two functions, one for data generation which uses an executable to make up test data. The other a mechanism for calling Hadoop. Basically it uses two objects, System.Diagnostics.ProcessStartInfo and System.Diagnostics.Process. The ProcessStartInfo is for defining startup information for a process that you then define using the Process command. In this case it’s setting the location of hadoop:

$pinfo.FileName = $env:HADOOP_HOME + "\bin\hadoop.cmd";

Then it sets up arguments, if any. The actual calls to this from the code use a command, dfs, which has different settings -mkdir and -copyFromLocal. From what I can tell, it’s creating a storage location within Hadoop and then moving the data generated over. I’m good with all the scripts I can see except knowing where this dfs thing comes from.

Data load ran just fine:

Data loaded, time to test out a Map/Reduce job. Again there’s a powershell script included for running a simple job, so I check it out. First run, fails. Great. More stuff to try to troubleshoot in order to be able to see this work. This is not going to be easy.

Stepping through and running the scripts might not be the best way to learn this. So, I’m going to now start reading the Big Data Jumpstart Guide. I’ll post more as I learn it.


Nov 21 2012

Final 2012 Learning Opportunity

There’s still a little time left in the year. I know some businesses have allocated training budgets and if they don’t spend the money, they lose it from their budget. So a few of you might still be looking for opportunities to get your learn on. I’ve got one more big one before the end of the year. You can try heading down to Florida for the Live 360 conference. I’ll be there along with several friends (love my #sqlfamily). I’m presenting two sessions, one on setting up an environment for performance testing using the new distributed playback capabilities from SQL Server 2012 and one on introducing the accidental DBA to backups. You can still register here to attend these sessions and all the other great ones being presented. Last chance to use up that training budget for the year, so don’t miss it.

Sep 24 2012

Interviewing a DBA

I’m not a fan of trivia style interview questions. Yes, I ask a few because you have to in order to immediately eliminate the completely unqualified applicants. Even those types of questions, in my opinion, need to be focused on concepts and not syntax. The reason we have the Books Online with SQL Server is because you shouldn’t have to memorize every possible command along with all their parameters. Want to know how to write a MERGE query? Look it up. What does a MERGE query do? That you ought to know. I think concepts are important. Questions about the recovery models within SQL Server aren’t trivia about the system, they’re trying to get to your understanding of how point in time recovery works.

I don’t really like posting interview questions. And most of the time when I’ve seen interview questions posted (even mine), they’re pretty trivial stuff that doesn’t really get to whether or not the person you’re trying to hire is a good fit for the position and your team. I also don’t like posting interview questions because some people will try to use them to study up and attempt to BS their way into a position they frankly don’t deserve and haven’t earned. SQL Server knowledge and experience comes from using it to solve problems out in the world and protecting the information generated by a business.

That’s why I love this question. And I don’t mind sharing it with you because you can’t really memorize an answer to it:

You get a call from one of the business people. They tell you that the database is running slow. What do you do?

This is completely and utterly open-ended. It can go anywhere. In fact, it’s going to go where you lead it. For example, you could say “I first look at the Windows server error logs.” OK, that’s fine (several people I’ve interviewed started there). What indications would you find there that the server is running slow or what would you find there to show why the server is running slow? Suddenly, maybe you don’t want to look at the error logs for the server any more, or maybe you do. But you get the idea. There is no single correct answer here. There are however, lots of very problematic paths, and I’m going to let you go down them. I had one guy insisting that the very first thing he needed to do after the phone call was take a look at the application code to see the method used to make the call to the database. We spent quite a bit of time exploring why this seemed to be the best approach to him. Was it? I’m not saying. No hints on this one. Your answer for this question, is your answer, and that’s why I love it.

Further, as we explore this question, and I’ve spent anywhere from 10 minutes up to an hour working on it as part of an interview, I’m also getting to see how you deal with problematic situations, what your logic chain looks like, what your understanding of SQL Server is, and, most importantly, how you fit into the team. Because with an open-ended question like this, we get to talk. We’re way beyond silly trivia contests now.

Before you think this is unfair to people who aren’t performance experts, fine, let’s talk about what happens when you get an alert that the server is offline. Not a systems person? OK, we just got an alert that a database consistency check failed, now what? See, the point is to go on an adventure where we explore your knowledge and approach. I just have to work hard to make sure we stay somewhat on topic so that I can assess your knowledge and skill level.

Now, if I approach any of these questions and your response is to reject them out of hand, something I’ve run into, then we’re done. I’m not going to focus on trivia, which is how lots of people prep for interviews. I expect you to have concepts, process, logic, and methods available from your time studying and learning. So if we interview, be ready for this exploration, not a trivia contest. And the only way to really prepare is to get experience and knowledge by actually working with SQL Server.

Oh, and sometimes, I ask questions or make statements that are wrong. Sometimes it’s on purpose. Other times, it’s because I screwed up or was ignorant. But you can’t sit there agreeing with me. You better be paying attention because I might be testing you further.

This type of question is just too perfect for understanding how much you know about SQL Server.

Want to start to prepare for answering this kind of question? I’ve got an opportunity for you. At the PASS Summit 2012 this year, I’ll be running an all-day pre-conference seminar called Query Performance Tuning: Start to Finish. In it, I’ll cover quite a bit of what might make it possible for you to answer this question should you be presented it in an interview. No, I’m not guaranteeing you’ll answer it correctly. I’m just offering a chance to prepare. Sign up for the Summit today. There’s still a discount in place that can help you offset the cost of the seminar until the 30th of September.

Jul 16 2012

SQL In The City: London 2012, Recap

Presenting on Ring BuffersWow!

How’s that for a recap?

The concept for the SQL in the City events is pretty simple. Put on a free event that instructs people on SQL Server, Azure, and related technologies along with a healthy smattering of Red Gate tools. All teaching is done by some of the best people in the business (and me).

This was the second event in London. The concept was launched there last year and succeeded quite well. This year the event filled it’s registrations so quickly that Red Gate felt obligated to have a second day, which almost completely filled up too. There were more than 350 people in attendance on Friday, and then, on Saturday, a day off, another 250+ people showed up. That’s well over 600 attendees over the two days. And what people! The UK audience is just excellent. These people really pay attention to what you’re saying. They don’t make many comments while the presentation is going on, but oh my gosh the detailed questions you get afterwards. It’s just wonderful. Plus, these guys are part of my #sqlfamily. I got to meet several people that I’ve met before in both the US and the UK. I love spending time with Tobiasz, Dave, Kev, Annette, Jonathan, Thomas, Neil. I also got to meet people that I had interacted with only online and they’re wonderful in person. Thanks to everyone who sought me out, especially Colin and Stephanie. It was a real privilege to meet you two (although neither of you knows the other). It’s the interpersonal aspects of these events that makes them great.

It’s so nice to be able to relax and show people something like how to get a sandbox environment set up, but all the ways that using Red Gate tools to do it can help you make the job easier, faster and cheaper. Normally you can’t give an open answer when someone asks how to do something better or easier during a session. This venue makes that possible.

I put on three sessions during the day. One on how to improve performance, yours and your code, in T-SQL. I got to regale the crowd on all the evils of ddltbl (not a typo, you had to be there) as well as common, simple, mistakes made all the time in T-SQL code. I also did a session on sandbox deployments. While I’m personally against giving everyone & their brother a copy of the production database for development, I acknowledge that it is a good set of data to develop against. So, if you have to do it, you may as well use Virtual Restore to save some space. Finally, my last session was on some of the lesser used performance metrics that are actually more useful than people give them credit for.

I sat in on some of the other presentations and they were great. I really liked Steve Jones (blog|twitter) session on handling disasters. I also liked watching a new speaker, Annette Allen (twitter), stretch her legs for the first time. She was good. UK user groups take note. You have another resource available.

I had a blast presenting all these sessions, twice, and the crowds seemed receptive. I really appreciate everyone who attended and the excellent feedback that they politely (but firmly) provided. Then we had beer.

Yeah, you heard me. Wonderful, glorious, Red Gate beer served right there at the event. It was a great batch of Select * Ale. Highly recommended at the end of a hard day of T-SQL learning and networking.

It was a magnificently run event. The only complaint I heard was that we had an inadequate number of bathrooms for the men (which, I learned, are not called stalls in the UK, some humor doesn’t translate well). Thanks to Annabel Bradford and all the team at Red Gate who put the event together (even if I do work for them, it was a really well run event). You guys are magnificent.

If you missed a session while you were there or you want to see a session again, keep an eye on the SQL In The City web site. Videos of the sessions will be uploaded. If you weren’t there, you missed it. But, I have good news.

We’re taking the show on the road. We’re going to be hitting five cities in the US in September and October and then Seattle in (which I think is still in the US) in November. It’s going to be a lot of the same people presenting the same topics, but it’s also going to include a ton of excellent local speakers at each of the cities we hit. This means the excitement and education that SQL In The City represents will be accessible to lots more of you soon.