Jan 31 2012

Tech on Tap v1.1 Wrap-up

DSC06558The first ever Tech on Tap event was held on Saturday, January 27th, 2012 at the Stone Cellar Brewpub in Appleton, WI. If you weren’t there, well, you missed it. Notice that keyword in that earlier sentence, Brewpub. Yes, the Tech on Tap events (and there will be more) are specifically designed to mix Technology and Beer in a learning and networking event with a single focus. This first event was all about virtualization.

The space where the event was held was the back bar of the Brewpub in a private room. The layout of the tables wasn’t perfect for presenting, but they were very conducive to networking. Other than that, the space was just excellent.

The first presenter was Brian Lewis (blog) from Microsoft. He spoke about virtualization in general and Hyperv in particular. It was a good overview of the product and only occasionally strayed into marketing hype (sorry, it did, but only a little). He had good questions and good audience interaction.

Then we had lunch (and some beer). The food was good (the beer was better, try the IPA). Then it was on to the next presentation.

This one was a more general overview of the topic of virtualization with a heavy emphasis on VMWare given by Bob Plankers (blog|twitter). Wow! All I can is Wow. Bob is a great presenter who put out excellent information AND he was very funny. I’m pretty sure he had the most one-liners retweeted during the entire day, but he was still churning out really excellent material.

Then we had more beer including several flights of samples of all the beer made at Stone Cellar. Wow! Almost as good as Bob’s presentation.

Then it was time for Jes Schultz Borland (blog|twitter) and I to present. Our topic was “Virtually Monitoring SQL Server.” We chose as our theme for the presentation the Dropkick Murphy’s and we used their songs as slide titles along with the graphics (we’re in a brewpub for crying out loud, we need to have a little fun here). She and I did an unrehearsed back & forth on the slides, tossing pieces of information back and forth on how to keep an eye on your servers that running virtually. I think it went really well. We need to rehearse it more so we’re more natural on transitions. A couple of times I tossed the topic to her and she wasn’t ready. I know I stood there slack-jawed several times when she did the same to me. But in general, it seemed really natural. I think we’ll polish the slides and the deliver to submit it for the PASS Summit.

We finished the day with a general QA for about 1/2 hour and then another hour of networking (with more beer). It was a really excellent event.

The organizers (pictured above) did a good job putting it together. The technical information was excellent. The speakers (not talking about myself here) were top notch and really delivered on their material. I had a great time and learned some things at the same time. You can’t ask for more.

The next event will be on Sharepoint. If you’re anywhere even close to Wisconsin and a Sharepoint geek, start making plans.

Jan 30 2012

Execution Plans, What Do I Look At?

lookThe question came up, what are the top 3-5 things that you look at in a query. I realized then that I hadn’t really written them down. There are some areas that let me know pretty much immediately where problems lie within a plan. You don’t necessarily know the answer from looking at these things, but you know you have a problem. Being me, I couldn’t stick to 5, so here are 6:

  1. Immediately check the properties on the first operator (SELECT/DELETE/INSERT). There’s tons of information in here and people frequently just ignore it. You can see if the plan is based on full optimization or not. That immediately tells me if I’m working on the optimizer’s best estimate at a plan or I’m looking at a timeout. If it’s a timeout, I know I can’t count on this plan being good. Also I get the parameter compile time & run time values to help determine parameter sniffing issues in the properties.
  2. Warnings. If you see no join predicate warnings, that should jump up and poke you in the eye like some jumping eye-poking little monster. Same goes with missing statistics. The new warnings in plans in 2012 are equally important to know about. These are quick pieces of information that should immediately point you in a direction of inquiry within the plan.
  3. The most costly operations. Yes, I know you can’t trust these values because they are just estimates. Yes, the estimated operator cost is the same in both estimated and actual plans. No measurements of actual cost are taken by an execution plan. But these are the numbers available, so I use them. They’re accurate more often than not and quickly lead you to the possible source of the problem.
  4. Fat pipes. Now really, these are usually just an indication of volume and knowing that you’re moving lots of rows helps you read a plan (umpty-million rows joining umpty-million rows through a Loop might be an issue). But the real alarm bells go off when you see big fat pipes going to little skinny ones or skinny ones to big fat ones or even skinny-fat-skinny. That’s a huge indicator of something
  5. Extra operators. This is like that old statement about pornography “I can’t give you a precise definition, but I know it when I see it.” It’s looking for stuff that doesn’t belong. For example, you don’t have a single ORDER BY statement, but there sits a Sort operation. Why? That’s my “extra operator” indicator telling me to dig deeper.
  6. Scans. Scans are not necessarily bad and Seeks are not necessarily good. In general terms, with smaller data sets, you usually would expect to see a Seek over a Scan. Scans can be the right, good, and best choice, especially for very large data sets and in other situations, but they are an indicator of potential issues.

After that, you have a whole slew of things you can get worked up about. Table Spools in SELECT statements are usually not good. Look for indications of multi-statement UDF’s (Scan’s with zero cost). Loop joins when a Merge makes more sense, Merges where you ought to see a Hash, missing index information, mismatch between estimated & actual, blah, blah, blah… You get the point. There’s just tons & tons of information within execution plans. But that list of six are usually the first things I look for.

Jan 23 2012

Deadlock Monitoring

There are four different ways you can get information about deadlocks in your system. These are:

  • traceflag 1204
  • traceflag 1222
  • trace events
  • extended events

For years I’ve been pushing traceflag 1222 as the best of the lot. Well, that’s over. I’ve been learning more and more about extended events and I’m currently in love with xml_deadlock_report event. Why? Simple, it has everything that traceflag 1222 has, but there are two glorious things about it.

First, it’s not going to be filling up my error log with, for the error log, noise. Seriously. As much as I liked the information displayed from traceflag 1222, I didn’t like what it did to the log, but I saw it as a necessary evil.

Second, it’s XML baby! That means you can set up queries to pull information out if you need to. Yeah, I know you can hop through hoops to set up the text from the error log for querying, but, that’s nothing like pointing to the file and referencing the nodes. Way too cool.

On top of that, for simple deadlocks, the graphical version of the deadlock graph gives you most of what you need to quickly identify the issue. You absolutely don’t get that with the traceflag.

Nope. I’m sold more than ever on extended events. If you haven’t started exploring them, I strongly recommend you do. Especially with SQL Server 2012 just around the corner. The functionality around extended events there completely makes these things accessible in ways they weren’t before.

Jan 18 2012


I’m not going to black out my blog, because, let’s face it, who’s going to notice? But for all six of you who read this (and, of course, you Mom), I do want to point out that these are horrific bad bills. They are sponsored by Democrats and Republicans, so party affiliation shouldn’t enter into this. In this case, they’re all bad guys. And why? Greed, pure & simple. Hollywood donates millions & millions to politicians and they expect these people to stay bought and deliver on their “anit-piracy” legislation.

Don’t know anything about this? Here’s an article. Just don’t focus on SOPA. It’s the bill sponsored by the Republicans (which for some reason is the only one called out as evil…grrr). There’s also PIPA, which is the Senate equivalent sponsored by Democrat Patrick Leahy (who clearly doesn’t get it).It’s absolutely as messed up as SOPA.

Anyway, blogs all over are blacking out today (by the way, this won’t work after 1/18/2012, so don’t bother):

My favorite
The Oatmeal

Please take five minutes out of your day today to voice your opinion on these bills. I don’t care if you’re in favor of them (although if you are, I think you’re nuts), but you need to contact your representatives and make your voice heard.

No more politics. Back to your regularly scheduled SQL Server nerdery.

Jan 13 2012

Friday SQL Nugget #1

polishGee thanks Jes (blog|twitter). Just what I wanted, a little extra work on a Friday afternoon. I used to like you.

We have a tagging theme started by Ted Krueger (blog|twitter) who I also used to like.

The theme is: Deciding that I need to delete and start all over

Lordy I hate this one. See, I find it easy to decide that I need to delete and start all over. My challenging task is persevering. But… here’s the rub. Because my challenge is persevering, I have a tendency to try to persevere when I really should be throwing in the towel.

I don’t have a technical example of this ready at hand (I did mention it was the afternoon on a Friday, right?), but I do have a presentation example. One of my presentations from last year… let’s say the topic wasn’t one I wanted to do. But, I had to. So I busted my hump on the slide deck. When it was done. I knew it was a steaming pile. But I sent it out for review anyway. Guess what. It was a steaming pile. They really didn’t like it, but suggested changes.

So I went back to work on the pile. Basically trying to take the substance, brown & smelling, and rearrange it to something resembling a pleasing shape. I worked at it. Hard. I tried different slides, jokes, anything I could think of to try to make this stuff resemble something of value. When I finally thought I had the pile in as pretty a shape as it was going to get, I sent it off to a second person for review. This person, a friend, didn’t pull a single punch. “Throw this out and try something completely different.”

Now that’s what I had wanted to do ever since I struggled through the first four or five versions of this slide deck, but I fought against my common sense and tried to do the hard work rather than take the “easy” way out and chuck it all and start all over.

Sometimes, you need another person’s perspective to realize what you’re vigorously polishing is not actually a diamond, but something entirely different.

I’ll tag… Gail Shaw (blog|twitter) cause it’s her turn.

Jan 11 2012

Database Backups: Things You Need To Do Now

BackupI want to say a few things about database backups that you need to know.

Wait a minute, haven’t you written about backups before?

Why, yes. Yes I have.

Aaand… you’re doing it again because?

Have you noticed the shocking number of questions that come up on SQL Server Central and #sqlhelp regarding backups? Have you noticed the incredibly huge number of people who don’t have backups at all? That’s why. To get the word out.

Oh, good point. Carry on.

Because backups are so easy, people tend to discount them. That is, until they need them. Then, suddenly, they become extremely important. Here’s a suggestion: Make databases important now. Learn how SQL Server backup works. Make sure you have backups on your systems. Make sure you have the appropriate backups on your systems. Spend time on backups prior to the emergency where you will suddenly wish you had spent more time on it. Tuning that query? It can wait. Adding a new piece of functionality? Right after the backups are set up. Please, give them the appropriate level of urgency that they deserve right now. I say this because later, when your server has crashed or someone has deleted the boss’s pay check, that level of urgency on that backup is going to shoot through the roof.

I mentioned it before, but it bears repeating, make sure you’re doing your backups correctly. Far, far, too many system administrators think that simply copying the files that define a database means that they have a backup. That’s just not true. Again, right now, take the time to learn how to backup SQL Server. Learn the differences between Full, Differential, Log, File/FileGroup and Snapshot. Learn how to automate these tasks so that your databases are protected. No, RAID is not a backup solution. I know you have a SAN, but it can still go wrong (ask me to tell you about the time our SAN admin switched it off by accident, that was exciting) and you’re going to need backups to recover.

Finally, and this is really the toughest part, you need to learn how to restore the database. That old saying, your data is only as good as your last backup, has a corollary from Kimberly Tripp (blog|twitter), your backup is only as good as your last restore. You actually need to try to restore your databases in order to know that your backups and your backup plan are good. You need to take the time, now, to test and practice restoring your database. There’s no other way to be sure about your backups.

Look, this is all repetition. I’ve said all this before. But unfortunately, for whatever reason, the word is not getting out, so, I feel the need to repeat myself. I’m trying to help you here. Backups are an urgent need. You should absolutely treat them that way.

For those interested in more detail on all this here are a few links:

Why Don’t People Run Backups
SQL Server 2005 Backups
Backup Verification
7 Preventable Backup Errors
SQL Server Backup and Restore for the Accidental DBA
Why is my log file full?