life is a minestrone . . .

February 24, 2012

• Nashville SQL Server User Group

March 10, 2012

• SQL Saturday #110, Tampa, Florida

March 24, 2012

• SQL Saturday #105, Dublin, Ireland

MARCH 31, 2012

• SQL Bits X, London, England

APRIL 14, 2012

• SQL Saturday #111, Atlanta, Georgia

So today is my first day back in the office after coming home from the PASS Summit last week.  Fortunately, all of my developers are at a training class for the next three days, so I have time to play around with some of the things I learned. 

Having attended Adam Machanic’s (blog | twitter) session on Workspace Memory, I wanted to get a better feel for how many sort and hash warnings I’m seeing in my production environment. To gather this information,  I needed to modify my standard server side trace script to capture the Hash Warning and Sort Warning event classes.  Since I can’t stand repetitive tasks, I wrote a quick query that would create the T-SQL needed to add all the set events for a particular event class, and then I set up the trace to capture just these two event classes.

I thought the folks who have attended my server side trace presentations might appreciate the code, so I’ve posted it below.  To make the code work you need only know the event class ID you wish to include in your trace. You can get a list of all the event class IDs by querying the sys.trace_events table.

/*
Create SET TRACE Event entry for server side trace script
SYNTAX NEEDED: EXEC sp_trace_setevent @TraceID, <EventClass>, <ColumnID>, @on
*/
SELECT 'EXEC sp_trace_setevent @TraceID, '
+ CAST(teb.trace_event_id AS VARCHAR(2))
+ ', '
+ CAST (teb.trace_column_id AS VARCHAR(2))
+ ', @on'
FROM sys.trace_event_bindings AS teb
INNER JOIN sys.trace_columns AS tc
	ON teb.trace_column_id = tc.trace_column_id

/*Substitute the event class IDs that you want to capture*/
WHERE teb.trace_event_id IN (55, 69)

http://bit.ly/ServerSideTraceDemos

http://bit.ly/ServerSideTraceSlideDeck

Normally on Memorial Day weekend, you’ll find me haunting my usual cove on Percy Priest Lake here in Nashville, but this year was different.  This year, I was aboard a much larger boat listening to SQL Cruise Alaska’s guest speaker, Buck Woody [blog|twitter] entertain and enlighten us with the first of his three part series:  Three C’s for the High Seas:  Career, Communication, and Cloud.

During this session, Buck taught us that once you develop a goal, you need to translate that goal into steps, and then find a method to make yourself accountable for measuring those steps, simply because what gets measured gets done.

Additionally, Buck cited lack of knowledge as being one of the road blocks to successfully achieving our goals. To overcome this obstacle, Buck suggested we all add a reading list to our action plans.  By reading, we can easily fill the knowledge gaps that we know exist, and also identify the ones of which we may not be aware.  He further challenged us to make ourselves accountable by sharing the reading list in a blog post and then posting a monthly book review.

I have to admit that I feel really uncomfortable when I have to describe my goals, either professional or personal. It’s not like I am completely directionless, it’s just that the destination is pretty fuzzy for me.  So, my goal over the next twelve months is to make a conscious effort to define my destination.  Many of the books I have chosen reflect that process.  A few I’ve been asked to read by others, and some I’ve chosen because they fill specific knowledge gaps.  The books are listed in no particular reading order.

1. The Five Dysfunctions of a Team
2. What Are Your Goals: Powerful Questions to Discover What You Want Out of Life 
3. Why We Do What We Do: Understanding Self-Motivation
4. Gifts of Imperfection:  Let Go of Who You Think You’re Supposed to Be and Embrace Who You Are
5. The Alchemist:  A Fable About Following Your Dream
6. Never Let Go:  A Philosophy of Lifting, Living, and Learning
7. One Person/Multiple Careers:  An New Model for Work/Life Success
8. Crucial Conversations:  Tools for Talking When the Stakes are High
9. What to Say When You’re Dying on the Platform
10. Sin and Syntax:  How to Create Wickedly Effective Prose
11. Inside Microsoft SQL Server 2008:  T-SQL Querying
12. Refactoring Databases:  Evolutionary Database Design 

So many things in our lives are cyclical:  the seasons, the holiday calendar, the school year. For me, it’s boating season that drives the wheel, and it’s just about the time for me to get really antsy.  For the third year in a row, we are welcoming the new boating season with a flooded marina! Fortunately, I have the upcoming PASS Summit to keep my mind off not being able to get to my boat.

Speaking of cycles, it seems natural to me to view the time between Summits as my professional season.  The event itself ushers in a new year of learning and opportunity, and I’m very much looking forward to this year’s pilgrimage to Seattle. I feel incredibly lucky to be able to work in an industry that lets me learn something new every day and share that passion with a community of individuals who are just as excited about the learning process as I am. 

After last year’s Summit, I made a committment to myself to become more involved in presenting.  I see it as a way to pay forward all the intellectual good will that I’ve scooped up from the community at large. So, I’ve offered two sessions for Summit consideration.  You can see all the abstracts that have been submitted here.  The two I have submitted are below:

SQL Server Internals:  It’s what’s for Dinner!

Ever wonder what happens behind the scenes when you query your SQL Server database? Well, believe it or not, it is somewhat like what happens in the kitchen when a chef prepares a meal. During this session, I will use this analogy to describe the life cycle of a query and the journey it takes through SQL Server’s working parts. By comparing these sometimes intimidating concepts to a familiar idea, you will have a clear understanding of the underlying processes involved in returning data from a SELECT query.  Once we’ve established a frame of reference for a simple read operation, we’ll look at an UPDATE statement and consider the additional factors associated with a write operation. By the end of this session you should have a clear overview of what occurs behind the scenes when a query is presented to your server.  Furthermore, connections will be drawn that will help you understand the basis for many of the best practices associated with configuring and querying your SQL Server. 

This session covers what is normally considered a fairly advanced topic, but the material is presented in such a way as to make it accessible to beginning level attendees. The goal is to convey how SQL Server works as a system rather than diving deeply into the details of an individual component.  Understanding this big picture will make you a better diagnostician when you have performance issues with your own servers.  Additionally, you will be better equipped to explain your rationale when requesting hardware upgrades, configuration changes, or database design changes.  Lastly, you’ll know exactly where you want to focus your learning when you are ready to dig deeper.

Expected Learning Outcomes:

Session attendees will be able to

• identify the main components of SQL Server’s architecture
• explain, in general terms, how each component functions during the query life cycle
• describe how the query optimizer and the plan cache work together
• understand the relationship between system memory and the buffer pool
• describe what happens during checkpoints and lazy writes
• explain write-ahead logging and transaction log’s role in maintaining data durability
• list specific methods for monitoring key behaviors of the main components

Capturing and Analyzing Profiler Trace Data

“Hey, the CPU usage just went through the roof! What’s hitting that server?” Sound familiar?  If you have been a database professional for more than three days, then you have probably heard multiple variations on this theme.  So, when you find yourself in this situation, where do you start?

You may have read that you should always use a server-side trace to capture profiler data.  You might even already know how to use SQL Server Profiler to help you generate a T-SQL script to do just that. But, do you have a good understanding of what the script actually does, and what you can tweak to make it work better?  Furthermore, do you know what tools are available to help you process the data once you’ve captured it?  If not, then this session is for you.

First, I will take you through the anatomy of a profiler trace script. You will learn about the different parameters that can be customized to capture just the data you need. Then, we’ll talk about different ways to stop and start the trace, how to know what traces are already running, and ways to schedule a trace to start under certain server conditions. Lastly, we will look at the different methods available to us for analyzing the data we’ve gathered. The list of free tools we will examine includes: the SQL Server Profiler client application, “home-grown” T-SQL queries, ClearTrace, and the ReadTrace component of the RML Utilities.

When you get back to your desk, you will be ready to gather profiler data like a pro, and you will know exactly how to set about analyzing the data to pinpoint your performance bottlenecks. 

Expected Learning Outcomes:

Session attendees will be able to                                                                     

• create and modify T-SQL scripts to capture SQL Server Profiler information
• explain the different options for starting and stopping the tracing process
• compare the different tools available for processing trace data
• walk away with a basic set of scripts to begin their own capture and analysis process

Baselines first and when things go – spotlight’s on what doesn’t belong. 

for Tom LaRock’s Meme Monday 

So, this blog post has been banging around in my head for a couple of days now, and I finally just had to type it all out and give up on brevity.  This was kind of a big deal for me, and I wanted to record all the things that led to its fruition. 

So here’s my SQL Saturday #67 recap:

SQL Saturday Chicago 2010 was my first PASS event.  I had already been attending my local, Nashville SQL Server User Group meetings, but I’d not been to any other SQL community events.  Since that time, I became a part of the Nashville SSUG leadership committee and we hosted our own SQL Saturday in Nashville this past August.  I also attended SQLBits 8 in York, England (which was awesome!) and wrapped up the year with PASS Summit (also awesome) in Seattle.  You could say I’ve gotten the bug.  I love attending training events, not just for the opportunity to learn, but to catch up with friends that I only get to see at events like these. 

After the PASS Summit, I was really inspired to do some of my own presentations.  One doesn’t get a degree in Special Education without having the heart of a teacher, and I knew I had a lot of good knowledge to share.  I saw it as an opportunity to pay forward all the mentoring I’ve received.  So I got to thinking about what my first presentation might be.

As I was going through my rather dog-eared copy of Professional SQL Server 2008 Internals and Troubleshooting, I flipped back to the very first chapter, an overview of SQL Server internals, and an idea was born.  I had this crazy analogy floating around my head that tied all the working parts of SQL Server together with preparing a meal.  I made a few rough notes, so I wouldn’t forget the gist of my idea, and then life took over again, and I didn’t really go back to it.

Fast forward to February of this year.   I had read Scott Berkun’s Confessions of a Public Speaker, and decided that maybe this whole public speaking thing wasn’t so impossible after all.  Then, the call for speakers for 24 Hours of PASS came open about the same time as SQL Saturday Chicago.  So I typed up an abstract for my SQL Server Internals overview and submitted to both, fully expecting to be rejected.

In the meantime, I started training for my RKC workshop in April (think black belt test for kettlebells).  Now, all of a sudden my life was consumed with training, so session submissions quickly evaporated to the periphery.  As a result, I was almost relieved when I didn’t make the cut for 24HOP.  Granted, I was disappointed, but I felt over committed, as it was, and didn’t need to add developing a presentation to the list.  Then, I get an email from Ted Krueger [blog|twitter] saying I’d been selected to speak at SQL Saturday Chicago. 

I kind of freaked out.  Not really in a bad way, just in a “Can I really do this?” sort of way.  Well, I felt really honored to be selected, so I thought I better figure out a way to make this work, and I accepted the invitation.  I quickly discovered that lots of folks believed I could do this, and with that support underpinning the effort I began to write a presentation.

Once I got my material together, I practiced quite a bit, mostly on the dog.  I also gave the presentation to my co-workers at a “lunch and learn”.  I’m really glad I did that, because it gave me the opportunity to field questions and get feedback from a friendly, yet unbiased, audience.  My dog, of course, thinks I hung the moon, so his opinion isn’t terribly objective.  =)

At this point I knew I had a few things to tweak, so I made some changes and dug a little deeper into some of the topics that might require more technical answers.  I have to stop here and thank Paul White [blog|twitter] and Benjamin Nevarez [blog|twitter] two super smart guys who helped me clarify some of the finer details of my presentation.

Armed with the knowledge that I had done my due diligence in preparing for this presentation, I headed off to Chicago.  As usual, there was a lot to do to help the SQL Saturday team get ready for the big day, so I jumped right in and started working.  It wasn’t really until I walked into the speaker prep room on Saturday that it hit me:  This SQL Saturday is different.  I remember feeling a bit like an imposter and had to shake off the idea that I didn’t belong in there.  I went through my slide deck one last time and decided that I was ready to go.  

I had the last session of the day, so I had some time to kill.  So, Erin Stellato [blog|twitter] and I sat in on Aaron Bertand’s [blog|twitter] session on what’s new with SQL Denali, and then I listened to Erin talk about baselining for performance tuning.  We enjoyed our lunch while listening to the WIT panel talk about encouraging the next generation of IT professionals, and after lunch, I went to Jes Borland’s [blog|twitter] session on capitalizing on social media outlets to build your professional network.  It was her first SQL Saturday session also, and, as expected, she did a stellar job.  Way to go grrl_geek!

So now it was my turn!  There were a few butterflies, but not as many as I thought there would be.  I had a little issue with the projector.  The display was slightly crooked and I couldn’t get it to straighten out, so I had to ignore my OCD tendencies and roll with it. 

Wow, the room was almost full!  There were several other really good sessions running at the same time as mine, so I was excited to have a pretty good sized audience.  There were a few faces I knew, but lots I didn’t.  Then, all of a sudden it was 4:00, so I reminded myself that no one was there to see me fail, and started the show.

I’m cruising along, feeling pretty good about the delivery.  I set up my analogy with a story, and now we’re digging into the heart of the working parts.  We get to the section where I start talking about the buffer pool and how its size is determined by your minimum and maximum server memory settings, and I get my first question.  A fellow asked me to explain what the memory settings meant.  It took me a second to formulate an answer, but it felt like hours and I let it fluster me a little.  Note to self:  you are allowed time to think! You don’t have to spit the answer out immediately.

So, I answer his question and then he asks me what his minimum memory setting should be.  Well, there’s a classic “It Depends” answer if ever there was one.  So, instead of asking him to talk to me after the session, I made the rookie mistake of trying to fully answer his question.  The problem was that I never saw the light bulb go off for him, so I kept trying to answer his question. Another note to self:  You might not see the light bulb, it’s okay.

Here I have to give a big shout out to Nicholas Cain [blog|twitter] who was in the audience.  He saw me glance his way with a “How the heck do I handle this look” and he jumped in to redirect the conversation.  I’m still not sure the guy had the answer he was looking for, but we certainly tried. 

So I’m finishing up the middle section where we talk about update queries and the added steps involved with write-ahead logging and I’m ready to segue into the last section where I dig a little deeper into the processes that handle data changes like checkpoints and the lazy writer.  I set this section up by reviewing the ACID principles of relational databases. 

Now, I thought this was safe, well agreed upon material.  No real room for opinion here.  Well lo and behold; I strike a nerve with someone in the audience over the definition of consistency.  I had explained that consistency meant that the data had to follow the rules.  For example, foreign key relationships must be maintained, meaning child rows couldn’t be without parent rows.  Constraints couldn’t be violated; if a column is marked NOT NULL you must put a value in, etc.  Well, that’s when things got weird.  A guy sitting in the back row says, quite forcefully, “No! You’re wrong.  That’s not what consistency means.  It means that one user cannot have a different view of the data than another user.”

At this point, I’m shocked by how rude the guy was, and as I’m standing there trying to formulate a response, several of the audience members told the guy that he was mixing up consistency with isolation.  They were right, but he continued to argue, and the audience continued to try to help him understand.

 It wasn’t until afterward that it occurred to me to bring up the READ UNCOMMITTED isolation level to show how dirty reads proved the error in his logic.  But that point aside, I wasn’t really sure how to gracefully agree to disagree with this guy, because he wasn’t going to budge, and I could tell the audience was frustrated and ready to move on.  I must say I was glad to have people I knew in attendance, because it was that little bit of silent encouragement that jabbed me into action. 

I made a joke about how we all at least seemed to agree on the definition of durability, and since the next slides pertain to maintaining this characteristic, perhaps we should move on.  Whew!  I got past that obstacle and the session finished right on time with a few more questions answered and a seemingly satisfied group of attendees.

So what did the audience think of my first presentation?  I got twenty speaker evaluation forms, none of which had any truly negative comments.  There was one person who was bothered by the projector (which I totally understood), but still thought the material was good and another who thought my opening analogy could have been shorter. The rest of the comments were really positive.  There were several “handled difficult audience well” comments, and my two favorites:  “Christina explained some very hard core SQL topics in a way novices could understand” and “Very original! Handled yourself very well.” All evaluations marked that I had met or exceeded their expectations.  So overall, it sounds like I accomplished my mission.  Folks learned something about SQL Internals that hopefully will help them better understand things like why memory pressure is bad and why we’re told that transaction logs should be on their own disks.

After the session, Nic suggested that all first time speakers should have backup, so to speak, at SQL Saturdays.  I agree.  I probably would have muddled through nonetheless, but it was nice having the moral support in the audience, and also a second set of eyes and ears.  He was able to confirm my impression that the grumpy old man was definitely out of line and atypical for most attendees.  It was certainly the first time I had ever seen someone behave rather belligerently in a session and I’ve been to a lot of sessions.

Overall, it was a really rewarding experience, and I will definitely do it again.  Crowd control will get easier, and if I can survive the grumpy old man in the back row, I can handle just about anybody.  

Thanks to the SQLSaturday #67 team for giving me the opportunity to speak, and making it such great event, in general.  I’m already looking forward to next year. 

Lastly, I need to thank Kevin Kline [blog|twitter], Joe Webb [blog|twitter], and Louis Davidson [blog|twitter],  fellow  members of the Nashville SSUG leadership committee,  who have been quietly and not-so-quietly pushing me to step up my game.  Thanks guys!  Hope I made you proud.

SQL Saturday Chicago 2011 was an absolute blast. Last year’s event was my first SQL Saturday, and I was honored to be picked as a speaker for this year’s event.

My recap will be posted soon!

start a blog, that is.  I’m just not that disciplined, and honestly, it’s a little scary.  I’ve run six half marathons, finished two sprint triathlons, and earned my Hardstyle Kettlebell Certification.  None of those things seems as hard as forcing myself to sit down at the keyboard and type out what’s going through my head about SQL Server.  Then I think to myself, “Good grief, Christina!  How hard can it be? Quit making excuses.”  I have also found myself saying, “You should write a blog post about that”, far too often during the past month.  So here I am, casting perfectionism to the wind and entering the blogosphere. 

There will be an odd post or two about kettlebells, especially when my next certification workshop looms closer, and  I’m sure there will be posts about food every now and again.  But mostly, I plan to write about the things I do with SQL Server.  They say that the best way to learn something is to explain it to someone else, and that’s the biggest reason I’m doing this.  The understanding that I will gain far outweighs the perceived risk of putting my thoughts out there for the world to see.  So here I go, and thanks for coming along for the ride!

And now for the scary part:  clicking the PUBLISH button . . .