T-SQL Tuesday #032 – A Day in the Life of a freelance Development DBA

by data-centric on July 17, 2012

TSQL Tuesday 17-Jul-2012

This month’s T-SQL Tuesday is hosted by Erin Stellato (blog | twitter) with a theme of “A Day in The Life”. Erin talks about the fact that our job title is often not a real reflection of everything we do, sometimes not even anything we do.  So for this month’s blog party, Erin asks us to describe a typical working day.

My Job Title

My job title “Development DBA” is what I call myself, although sometimes it still doesn’t cover everything I actually do.   The role description on my current contract refers to “Senior SQL Developer”.  I prefer the term “Development DBA” over “SQL Developer” because, in my mind, a SQL Developer is someone who just writes code. They may well write very good code, and do some database design, documentation maybe even a bit of query tuning – but their primary focus is writing SQL. As a Development DBA, I see my role as being broader than that. Yes I do all those things but I also expect to be the go-to guy in a wide variety of performance issues or when higher level design decisions regarding data stores need to be made. This could include archiving strategies, SDLC and change management processes or coaching and mentoring other members of the team.

So the question is how does a typical day shape up when compared to my perception of what I do.

The Start of My Day

My day typically starts earlier than it needs to so I can spend an hour working on a blog post or writing some other article before I leave for work. I find I’m at my most creative first thing in the morning – whether writing or coding.  I work for myself, and although I often work on long term projects with just one company, my online profile is important – especially when I need to start finding the next gig.  Today (Thur 12th July) is no different and I’m currently working on another post in my tSQLt Tutorial series which I haven’t added to in a while.  It’ll be nice to get this finished today as tomorrow’s SQL In The City is giving me some focus.  Red-Gate’s SQL Test uses tSQLt as its unit testing framework.

Yesterday, I started working on refactoring a business-critical piece of ETL code that needed some changes. My usual process when refactoring legacy code is to start adding unit tests to prove the existing code so that when I start refactoring I can quickly identify when I’ve broken something. Then, before making any actual logic changes I also like to run my version of the code against the original using a full set of live-like data as an integration test to compare the results of both versions of the code.  All was going well yesterday until I suddenly started getting some weird test failures.  The corollary of being at my best in the mornings is that I’m less insightful or intuitive later in the day.   Time to call it quits, I’ll look at this again tomorrow with fresh eyes (and brain).

A Tricky Problem

I’m lucky enough to live very close to the River Thames in South East London and I can get a fast catamaran service into work.  It’s only a half-hour journey, much nicer than train or tube and very relaxing – although this morning I spent the time thinking about why my tests were failing!

I don’t want to go in to specific details about the code but suffice to say that it was written based on some (perfectly valid at the time) assumptions about the cardinality of links between entities.  Imagine an UPDATE statement that is written in the belief that there is only ever a one-to-one relationship between entities but in a small number of perfectly valid cases the association turns out to be one-to-many. Under such circumstances, there is no guarantee of which of those one-to-many links will be the winner when updating a single destination row.  I know that the results in this scenario will be somewhat unpredictable but I would have expected any clustered indexes to play a part in the order of processing.

So after grabbing my morning espresso from the best coffee shop in Canary Wharf and getting to my desk around 08:30, I started to tackle the problem left over from last night.

After running several more tests I discovered that even if I ran the old code twice (with a DROP and CREATE in between) I was still getting different results.  First, this invalidated any attempt at comparing the old and new results and second, it disproved my assumption that any clustered indexes might give some kind of ordering. The only thing I could think of was that the recompile in between the two test runs was resulting in two slightly different execution plans so my next step was to compare those.

Learning Something New

A quick by-eye comparison of the graphical plans yielded no obvious differences, although as the plans were quite complex there was always the possibility that I was missing something. So the next thing I tried was to compare the plans as raw text (just by saving off the plans as xml).  Again, there were no significant differences in the overall plan but what I did notice is that there were differences in the number of rows processed by each thread of the parallel elements of the plan and also that the order in which the thread row counts were reported were different.

This makes perfect sense when thinking about parallel execution, in fact one of the reasons why parallel processing can generate lots of CX_PACKET waits is because the engine often has to wait for the last thread to finish before moving on to the next step. This isn’t necessarily a bad thing, CX_PACKET waits aren’t always an indicator of a problem.

Coming back to the problem in hand, if my UPDATE statement is trying to update one row from two possible source rows, and those rows are split between two threads then the outcome is going to depend on which thread finishes first – not how the clustered index is ordered.  To prove this, I modified the original version of the code to use MAXDOP 1. Several, somewhat slower runs later and I was at least getting consistent results each time.  This is no real help since it isn’t how it it works in production but at least I’d managed to prove I wasn’t going insane 🙂

It was nice to find the most likely reason for the inconsistent results and it felt great to learn something new.  That’s one of the things I love about what I do, I never stop learning and any day I discover something fresh is a brilliant day. Unfortunately it didn’t help my plans for creating a valid integration test, as it’s impossible to compare something that should be immutable with a moving target.

The Rest of My Day

The rest of my day was productive but typical. I continued working on that piece of code to make the required changes, supported by unit tests of course.  I also spent some time working on a database change management review document. This is a topic that I am passionate about and something I end up doing for most of my clients in some form or other. They don’t always accept all my recommendations or implement everything I suggest but I like to think that I normally leave a company’s database SDLC in a better state than when I arrived.  My day in the office finished around 6pm, followed by another relaxing boat ride home and an hour or so after dinner to finish off that blog post.  No work tomorrow as I’m off to SQL In The City.

Previous post:

Next post: