life is a minestrone . . .

Presentation download links:

May 3, 2013: Why APPLY?

Presentation download links:

April 18:  What’s Buried in the Plan Cache?

April 19:  Why APPLY?

In an effort to squeeze at least another year’s use out of my Dell Vostro 3750, I purchased an SSD to replace my existing hard drive. Of course, installing a new drive means you get to spend a good deal of time reinstalling operating systems, applications, etc. Fun, right? Well, the SSD is smoking fast, so that has made the entire process slightly less tedious, and since I needed to start from scratch, I decided I might as well upgrade Windows and Office, thus adding to the fun. Turns out I like Windows 8 more than I thought I would during the first five minutes of using it, and I really like the minimalist color scheme in Office 2013, even though it’s likely I’m in the minority opinion here.

But, I digress . . .

All my installs were going along swimmingly until I got to SQL Server. I kept getting this weird error that made me think I was having permissions issues with the account I set up to run the SQL Services. I ended up spending my day Googling what I thought was the issue and trying several different things all to no avail. Long story short, I finally Googled the *exact* error message I was getting:  [Configuration error description: '' is not a valid login or you do not have permission.]

Turns out, my complete lack of creativity when choosing a name for my computer during Windows setup came back to haunt me. By naming my computer Christina and also setting up my user account as Christina, it caused the SQL install to blow up when I tried to add myself as a SQL Administrator. DOH!

Lessons Learned:

1. Do yourself a favor, and don’t give your computer the same name as your user account if you expect to be able to add that user as a system administrator when you install SQL Server.
2. Search for the exact error message text. I’d have solved my problem three installs earlier had I followed troubleshooting 101. 

Now, go play with Windows 8 and Office 2013!

How about the Big Apple?! New York City, that is. I’ll be in town for SQL Saturday #158 talking about What’s Buried in the Plan Cache during the last session of the day. I have to say that I am seriously excited about this event. It’s been way too long since my last visit to my dad’s home town, and I’m really looking forward to all the sights, sounds, and gastronomical delights that are always on offer. I’ve managed to wrangle a few extra days in town and expect to fill them chock full of museums, restaurants, and general wandering.

But, first things first! Have you seen the line up of speakers? There’s a little something for everyone, and a good mix of local, national, and international speakers. I’m personally pretty excited about getting to hear from Hilary Cotter, Linchi Shea, and Leonard Lobel, all folks that I’ve not heard speak before.

Finally, I would be remiss if I didn’t give a shout-out to my fellow Idera Ace, Tim Radney, who will be presenting Know Backups and Know Recovery. We are grateful for Idera’s continuing commitment to invest in the SQL Community, and thank them for sponsoring our travel to this event.

So, fellow SQL Server professionals, if you are anywhere close to New York City on August 4th, register now for this day full of SQL goodness! You do not want to miss this amazing, free learning opportunity.

Hope to see you there!

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