Mar 28 2011

SQL Saturday #67 Wrap-up

Just… Wow. What an event. What a great group of people. I’m just so lucky to be involved with fantastic individuals like these. Thanks for having me out to play everyone, I really appreciated it.

SQL Saturday #67 started for me with my second FreeCon (follow the link for details on the first one). Brent Ozar (blog|twitter) put together another great session where we spent a lot of time talking about blogs and blogging as well as swoops through other topics. We, by the way, is like a who’s who of great SQL Server people. I’m not going to post the list just in case everyone doesn’t want to be outed. However, I found the event extremely useful. I have a ton of notes and action items for myself and I really need to get to work on them. I think you’ll begin to see a few changes around here, and over at my other blog (I can’t resist the occasional shameless plug, please forgive me). I may have to put together a sseparate blog post, just on the Freecon.

After the FreeCon I went to the speaker dinner. It was hosted by SQL Sentry (thanks guys). There were several MVPs and speakers from all over the country (including a contingent from Cleveland, reversing my earlier trip). I had the opportunity to meet a bunch of local people, a few speaking for the first time. If you take nothing else away from my rambling, remember this: Yes, these events are about the exchange of knowledge and skills, but even more, it’s about people.

The day of the event I was presenting in the morning. Which meant that I didn’t attend any of the morning sessions because I have a hard time focusing when I have to present in an hour or two. Instead, I ran through my slides, getting ready for my presentation (details below) and shot a few videos. In the afternoon I had to present again for my vendor slot. Only two people showed up. Getting people into the vendor sessions is something that SQL Saturday events should try harder at, and scheduling them at the same time as other sessions, probably hurts the vendors. Oh, and could the two guys who were there get in touch. I lost your contact info. Sorry.

Finally, I was able to go to a session. I went to see Jess Schultz Borland (blog|twitter) and her talk on “Make Your Voice Heard.” She covered Twitter, blogging, Linked-In and forums, all as a means to build your presence on the internet. It was a great session. She’s an excellent speaker, very engaging, highly energetic (something I strive for in my sessions, but she leaves me in the dust on that), and clearly informed on the topic. She had construction paper and crayons as part of an interactive approach with the audience. It worked really well. Most everyone came out of there with one or two new contacts on Twitter (or an interest in joining Twitter). If you get the chance to see this one (or anything else she does), I’d recommend it.

The second session I attended was unfortunately the final session of the day. But, it was a great session on DMOs from none other than the Man, Tim Ford (blog|twitter). Tim has a delivery style that is very relaxed and conversational. Using it he just puts out reams and reams of info. You really do want to get to his sessions when he talks. I took a bunch of notes and came away with great info. Tim attracted quite a few other speakers, so when questions & comments came from the audience, there was good information there too. Definitely worth going to and a great capper to a great day.

There was an after-party. It did include some SQL Karaoke. But that’s all I’m going to tell you about that. If you didn’t go, you missed out.

My Session:

My session was packed (go here to see a short video of the room) with people sitting in the aisles and around the edges. There were approximately 80 people and I received about 62 feedback forms (thank you, everyone). Here are the results and comments. The ratings were simple. Did the session meet, not meet, or exceed your expectations. Rate the quality of the session, 1-5. Then there were comments. Here are my percentages:

Met Expectations:

12 blank
1 Did Not Exceed – 2% (more on this)
21 Met – 41%
29 Exceeded – 57%


Blank: 3 – 5%
1: 0
2: 0
3: 5 – 8%
4: 16 – 25%
5: 39 – 62%

I’m very happy with the results. I quite happy that a large number of people thought I met expectations. I wouldn’t want there to be, even as many as I have, on Exceeded expectations. Because then it means that expectations of me were low indeed. Met is fine. The one person that wasn’t happy, I’ll talk about in a moment. Ratings on a scale like this are hard to judge, but clearly a majority of the people were happy. That’s good. Here is a selection of comments and some of my commentary about that comment:

“It would have been nice to know this was high-level rather than low level. Demos didn’t work too well.” I’ll take the hit on this one. I could have been more clear in the abstract, but it does say “introduce the query optimizer.” It doesn’t say anywhere, “deep dive,” “detailed,” or “low level.” Sorry. This is also the “Did Not Exceed” rating. I could have been a bit more clear, but it does show the importance of reading the abstract to understand what’s going on. If I’m talking about the optimizer and statistics and constraints and indexes, all in one hour… seriously, how detailed could it get? So while I’m sorry I didn’t reach one guy, I don’t think there’s a darn thing I could do for him beyond a little more clarification in the description.

“Sidekick in the corner was distracting” Actually this is my favorite comment. Tom LaRock (blog|twitter) was sitting in the corner at the front because there just wasn’t any room. He did make a few comments, but not that many. I didn’t see an issue with it, and still don’t. I’m just excited because, now, Tom is my sidekick. However, it is important to be aware that banter with the audience doe upset people. But, no, again this isn’t anything I’m taking action on beyond keeping myself aware. And of course starting Tom in the sidekick training program.

“Very good session. Please hide “Object Explorer” in SSMS for better code views” Crud. I usually do, but my head appears to have been elsewhere on this one. Thanks for the feedback. Something to correct going forward.

“Great information on the optimizer. Got some resources for further research”

“Very engaging and fun to listen to”

“Obvious enthusiasm & knowledge. Lots of pointers to useful things.” It’s like I paid this person. “Useful” is one of my target words for my brand, so I love seeing it in feedback. There’s a small indication I’m doing something right.

“Very informative.” Again, a key word. Your check is in the mail.

It was a great event. I’m happy I got to attend. Thanks to Wendy Pastrick (blog|twitter) and all her volunteers.

Mar 21 2011


It sure seems like there’s a lot of miscommunication between developers and database specialists. In fact, the communication can become so poor that outright hostility between the groups is common. At the end of the day we are all working towards a common goal, to add value to whatever organization we are working for. It’s a shame that we all lose sight of this commonality and create such a false dichotomy between the groups. I think there are some ways that we, as database specialists, can use to attempt to cross that gap.

Prior to being suborned to the dark side, I was a developer. I had a little over 10 years experience working in VB, Java & C#. I remember, distinctly, cursing our database team for being so problematic about how they did things. They slowed me down. They got in the way. When I had problems they were slow to respond, unless the problems were on production. I know I even instigated a few fights with them in an attempt to get them to move the way I wanted (hard to believe, I know). Then came the day when I shifted over to all database work.

Suddenly, I’m responsible for making sure the production system stays online and that the data is readily available to the business. Now I’m slowing down development, because I want a chance to review their design and validate their code to ensure it’ll work well and not affect production. Now I’m acting as a gatekeeper to prevent unauthorized access to the systems or at least keep people from making any of the 10,001 simple errors that could impact production. Now when a developer wants something fixed in dev, I’m the guy telling them they have to wait because something in production is wonky. And yeah, I’ve instigated fights from this side as I tried to get devs to understand that simply delivering code is not enough and that data persistence is there for a reason (again, shocking I’m sure).

Remember, both of these groups are more right than wrong, and both are working towards that common goal, value for the business. But they really don’t get along. What’s more, what they work on and how they work with it is frequently at odds. Ever heard of the object-relational impedance mismatch? How about the concept that you don’t have a database, but a persistence layer? What about managing data integrity within the application (one of my abiding favorites)? Never heard of those terms or concepts? Then you’re probably a database specialist and you’re probably not talking to your developers. If they haven’t already, they’ll soon be introducing an Object Relational Mapping tool to your enterprise. Best of luck.

A lot of these communication issues probably can’t be solved, but I know of one place where most database specialists are not communicating well with their dev teams, and database guys, it’s your fault. Source Control. Do you think of the structures and procedures within your database as code? You should, because, to a large degree, it is. The Data Definition Language (DDL) calls that make up your tables, views and procedures are code. That code needs to be checked into a source control management system. There, the individual objects can be versioned and managed. There you can create labeled releases of your code. There you can branch your code to create alternate development or support streams that contain variations of your database. There you can merge changes from multiple users and branches into a single main source for deployment to production. There you can keep your database directly in sync with application developers code.

Did you catch that last one? You can become more tightly coupled with your development team. Best of all, you can do this using their tools and their language. This is the communication problem I want you, the database professional to solve. Very few of us database types are using source control these days. This, despite the fact that there are fantastic tools and methods under development from different vendors that directly address the issue of getting and keeping database code within a source control system.

Years ago, when I first made the jump to databases, I was appalled that I couldn’t keep my code in source control. Then, as I worked more and more with databases, despite the problems, I abandoned the idea of managing the code in source because, frankly, it was way too hard. But several years ago new tools appeared on the market to make it possible (if still somewhat painful) to get the database into source control. I’ve been working that way for years now. It has completely eliminated one of the many problems I used to have with developers. They now know that my code is stored with theirs. That my versions are their versions. That their labels are my labels. That we branch the code together. It’s taken completely for granted, and we share a common language about change and deployment.

This has not solved every problem or conflict with database teams I’ve worked with. It has eliminated a source of friction. It has increased communication. It’s something that I could do, and you can do, to get a little closer to your development team. Not to mention the fact that you will now have your databases in a known state, all the time, that you’ll be deploying from a single location, that you can manage access to your code, and all the other things that having your databases in source control will bring.

For more details on the concept of putting your database in source control, and working better within teams in general, I’d recommend reading the SQL Server Team-based Development book. It’s a free download.

Addendum (3/27/2011): If you got this link through an email, could you post a comment below as to which distribution list it’s from? Thanks.

Mar 17 2011

Where does slow performance come from?

TrinityHallLibraryI have my opinions and experience, and I’ve no doubt you have yours. Paul Randal (blog|twitter) has put up another one of his interesting surveys to try to collect our opinions and our experience. You should run right over to here and vote immediately.

Now that you’ve completed that, I’ll tell you what I think.

Based on my experience (neither particularly broad, nor particularly deep, but there has been quite a bit of it), I’ve mostly seen problems in code. When talking about databases, the T-SQL code. When talking about apps, the application code. This is followed not too far back by really poor database structures and poor indexing strategies. I’m sure other people have seen other things, but these really are the areas where I’ve seen the most problems. The one thing common across all of them? Knowledge. It really has been my experience that people make mistakes because they just don’t know any better. As with most things in life, knowledge is the key. Go get some.

Mar 14 2011

ANSI Connection Settings

It’s reasonably well known that you can get different execution plans if you change the ANSI connection settings. But the question comes up, fairly often, how do you know what the settings are. It’s actually surprisingly simple. They’re stored right inside the execution plan, but they’re in one of the operators that most people ignore. Heck, I used to ignore this operator. Which operator is it you ask? Let’s find out.

Let’s use AdventureWorks2008R2 (because I’m lazy). We’ll call one of the stored procedures there like so:

[sourcecode language=”sql”]EXEC dbo.uspGetBillOfMaterials @StartProductID = 0, — int
    @CheckDate = ‘2011-03-10 02:31:39’ – datetime[/sourcecode]

If you execute this with “Include Actual Execution Plan” enabled you’re likely to end up with the following execution plan:


Don’t worry about the fact that you can’t really read this plan, we’re only focused on one operator this time. All the way on the left, right at the beginning of the plan (and plans are laid out logically from left to right), you see the lonely little SELECT operator. This operator is actually chock full of all kinds of Execution Plan Goodness, but it’s frequently ignored.If you right click on the operator and bring up the Properties sheet for it, scrolling down near the bottom (everything is in alphabetical order) you’ll find the property, “Set Options” with a little plus sign next to it. Expanding that out, you can see all the ANSI settings that were used when this query was executed:


If you use a trace to capture actual execution plans or you capture them through SSMS, you can get this information.

Mar 07 2011

Execution Plan Stability

Execution PlanI’ve talked before about one of the primary things that the Query Optimizer team at Microsoft tries to avoid, regressions. Basically, they want a plan that worked well in SQL Server 2005 to work well in SQL Server 2008 R2. What’s more, they want everything to work well between service packs, updates and cumulative updates. Ever wonder how they do it? Well, they cheat. OK, that’s mean and not entirely accurate, but it gets the idea across.

No, what they do is, identify when they have a breaking change, when they’ve got a special cumulative update or service pack that fixes some bad behavior, but that can cause plans to “break,” they wall it off. Note, in most cases, this “breakage” is actually a question of plans working correctly, but if it makes your query run slowly, you don’t care. They do this by putting it behind a bit of code (none that I 4could tell you about, not because I know and can’t tell you, but because I don’t have a clue) and putting a traceflag in front of it.

What’s this mean for you and me? It means, it’s possible, that you are not running the latest and (possibly) greatest version of the optimizer. By default, this traceflag is set off, and that’s a good thing. It means your plans are more stable, that the plan created prior to the latest CU is the same as the plan after the CU.

But, what if you want to try your system out on the latest & (potentially) greatest optimizer? In case all my weasel words have not been explicit enough, let me come right out and say this. Yes, you might have one of the problems that is fixed by the latest update…. and you might not. You may see improvements in your execution plans… or you might see them crash. In short, this is absolutely a moment where extensive and careful testing is called for. In fact, you might just be better off reading through the hotfix/CU/Service Pack documentation and determining if you even have a problem that might be fixed by this. If you don’t, you really might be better off not trying it. But hey, you know you want to see what happens, right?

Set TRACEFLAG 4199 to the On state when you start your server. That’s it. You’ll then be running the latest version of the optimizer, raw, with your chance to see if it’s the greatest, or abject evil. Microsoft has this well documented (with appropriate warnings and cautions) so you can try to understand for yourself whether or not this is applicable to your system.