T-SQL Tuesday #32 – A Day in the Life (#tsql2sday)

This month’s T-SQL Tuesday is brought to us by one of the more statistically important data professionals out there, Erin Stellato(b|t).  The assignment is simple enough: Record your day on either Wednesday 7-11 or Thursday 7-12.  Easy enough, but leave it to me to screw it up, right?  Anyway, I was travelling on Thursday (heading down to Albuquerque to present at the local 505 user group), so I cheated and recorded my activities for Tuesday, 7-10.  It was an average enough day, so a good cross section for the series.  So, without further adieu:

• 6:55 AM – 7:10 AM Check on I/O trace – I can work remotely, so commonly when I get up I’ll check in on things just to make sure everything is ok.   This time, I had set a profiler trace to run overnight to give me some info on I/O issues we were having.  All I did here was log in and make sure it ran, I was going to drill in to the detail later.
• 8:15 AM – 8:30 AM Review alerts from previous night – Still at home, but I did a quick glance over the alerts from last night just to make sure there weren’t any fires.  Everything was cool, so I hit the road to get in to the office.
• 9:00 AM – 9:20 AM Arrive in the office, get my day started – This is administrative time, responding to emails and getting my tea (I hate coffee.  There.), settling in for the day.  This bleeds into the next part….
• 9:20 AM – 9:40 AM General maintenance work – This was basically some file clean up and responding to some of the alerts I saw from over night.  Nothing major.
• 9:40 AM – 10:40 AM I/O research – So we’re having an I/O issue in our lower environments.  We’ve got a LUN on one of our instances that is getting slammed.  This was what I was using my trace to research and discovered that a whole lot of work was going through tempdb.  I spent this time reviewing my data and then talking with the relevant developers and QA engineers.  Once I had my info collected, I reported out to the systems team, DBA team, and the dev guys.  Unfortunately, this is a situation where not much can be done.  There really wasn’t any alternatives for spreading out the I/O load (at least none worth pursuing for a lower environment system) and the proper way to fix it was to have the dev team file things away for code fixes.  Still, with the info I collected we could come back to that with a better strategy.
• 10:40 AM – 11:00 AM TempDB cleanup – Got some additional space for one of our dev instances to allow us more tempdb space, so I cleaned that up and arranged the files.
• 11:00 AM – 12:00 PM CLR Research – So I’ve never really done much CLR work.  We had a legacy sproc that we used that was reporting incorrectly, so I was doing some research as to why. Really didn’t have much luck, but since I was used to the WMI in Powershell, I figured I’d try and rewrite the CLR logic using that.
• 12:00 PM – 1:00 PM Lunch – Every Tuesday we go to this awesome thai place down the road.  Basil chicken for the win!
• 1:00 PM – 5:00 PM CLR Research – I basically spent the rest of my day fighting with CLR.  Keep in mind, I’m a DBA with a sys admin background.  I’ve dabbled in .Net code, but I’m very rusty and my code is less than elegant.  However, it was a good learning experience, and taught me several things:
  1. CLR only supports a limited set of libraries.
  2. The System.Management libraries apparently have a lot of dependencies on forms and drawing (I have no idea why).
  3. CLR is a real pain to debug, depending on local security policies.

Honestly, this was one of my lighter days.  Probably because we had just come out of a holiday week where we had locked systems down and allowed minimal change, meaning we also did have much breaking.  This is what makes the job enjoyable: not every day is a fire drill and the ones that aren’t afford me an opportunity to experiment and learn.  Because of this day, I’m a whole lot more comfortable with the concepts of CLR (even though I still haven’t built a successful CLR function) and it’s made me a stronger DBA.

Thanks to Erin for hosting T-SQL Tuesday #32!   Make sure you visit the master post for other great blogs!