facility9

Honestly, I was inspired by the latest ‘issue’ of Capt. Varchar(MAX) and the Pagelatch Posse to write about how I manage my workload.

I used to scribble tasks down on sticky notes and stick them to the bottom of my monitor. This worked when I was only working on one project/task at a time and could break things down into linear tasks. As my workload and responsibilities increased, this method fell apart pretty quickly. Before long, my monitor looked like a sunflower made of sticky notes. Changing the priority of a task meant rearranging the sticky notes and heaven forbid that I have to take them all off and rearrange them and drop them on the floor in the process.

From the mess of sticky notes, I switched to using a pad of paper and making to do lists. This rapidly turned into a “wish I could do” list and I stopped using this technique, in favor of trusting my exceptionally rusty memory.

A few major mishaps later it was determined by everyone involved that my memory couldn’t be trusted. This is around the time that I found out about Getting Things Done. I attempted to work the GTD magic fully and it didn’t really work well for me. Something about it never clicked, maybe because I’m not an email person for tracking tasks - most people will send requests for 4 or 5 things in a single email and that makes it difficult to track my work load.

These days I use a 5×8 spiral bound notebook. At the beginning of each week, I start a new page and write the date at the top. Every to do item gets an empty box drawn next to it. When I finish that item I check it off. I carry this notebook everywhere I go, so this method works well for me.

Just to make sure I hold myself accountable to the people who request work from me, I use outlook tasks. When a request comes in via email, I immediately mark that email for follow up today. Tomorrow it will turn red in my in box and I can readily see which tasks I should have already finished. I don’t mark the task as finished until I’ve actually emailed the original person and told them that everything is complete. This works particularly well for tracking stored procedure/database change requests.

It’s not the best system on earth, but it works for the moment.

Any better ideas out there? Let me know in the comments.

Just a quick post with two T-SQL functions to compute the first and last day of the month for any DATETIME. These have been incredibly useful on a daily basis and I figured that I would pass them along.

Keep in mind that these are inline scalar-value functions and will be evaluated for every row in a result set. This isn’t so bad when you only have a few rows, but when you have a million rows, it’s expensive. It’s much better to use the inline SQL computation if you need to work on a large number of results.

The third function will compute the first instance of the longest month between two dates. This is often used in utility billing to determine which month the bill should be applied to. An example would be a bill from January 1 to February 28. This bill would fall into January. If the bill were from January 15th to August 28th, the bill would be for March. It’s a more specific case than the first two, but still very useful in context.


CREATE FUNCTION dbo.GetFirstDayOfMonth (@Day DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(mm,DATEDIFF(mm,0,@Day),0);
END;


CREATE FUNCTION [dbo].[GetLastDayOfMonth] (@Day DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(ss,-1,DATEADD(mm,1,DATEADD(mm,DATEDIFF(mm,0,@Day),0)));
END;


CREATE FUNCTION [dbo].[ComputeFirstLongestMonth]
(
    @StartDate DATETIME,
    @EndDate   DATETIME
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @FirstDate         DATETIME;
    DECLARE @LastDate          DATETIME;
    DECLARE @FirstLongestMonth DATETIME;
    DECLARE @MonthDiff         INT;
    DECLARE @counter           INT;
    DECLARE @DateDiff          INT;
    DECLARE @PrevDateDiff      INT;

    SET @counter      = 0;
    SET @DateDiff     = 0;
    SET @PrevDateDiff = 0;

    SET @FirstDate = @StartDate;

    SELECT @MonthDiff = DATEDIFF(mm, @StartDate, @EndDate);

    WHILE @counter <= @MonthDiff
    BEGIN
        SET @LastDate = dbo.GetLastDayOfMonth(@FirstDate);

        SET @DateDiff = DATEDIFF(dd, @FirstDate, @LastDate);

        IF @DateDiff > @PrevDateDiff
        BEGIN
            SET @FirstLongestMonth = @FirstDate;
            SET @PrevDateDiff = @DateDiff;
        END
        ELSE
            BREAK;

        SET @FirstDate = dbo.GetFirstDayOfMonth(DATEADD(mm, 1, @FirstDate));
        SET @counter = @counter + 1;
    END

    RETURN @FirstLongestMonth;
END

As a chapter leader (chapter leader of CBusPASS, the Columbus chapter of PASS), I was asked to volunteer at the upcoming Summit. I happily volunteered from 10:00AM to 1:30PM on Wednesday, so if you see my smiling face wearing a bright red vest stop by and say hi. I’ll be more than happy to direct you somewhere if you need me to and I’ll definitely be more than happy to shoot the breeze, too. Basically, ambassadors help people find their way around and locate various events, parties, booths, et cetera.

If you’re going to be at PASS and would like to help out, contact Sanjeet Gandham to volunteer to be an ambassador.

PASS also needs data entry volunteers to help entering speaker evaluation data. If you can help out for an hour or two here and there, send an email to Marcella Santoso at PASS HQ for more information. There are plenty of time slots available.

Make sure to include your full name and cell phone number so that PASS can get a hold of you.

Things I found interesting this week (only two days late).

General

The Leonardo da Vinci Guide To Being A Renaissance Man - Dumb Little Man is always good for helpful tips, but this is a great guide to tapping into your natural curiosity and drive and taking it to the next level. I have to admit that’s a good part of how I’ve gone from a Bachelor’s degree in English to where I am now in only 8 years.

Brent Ozar put together a great post on SQLServerPedia about getting ahead by saying no. This is definitely great advice that I could have used at the beginning of my career and could still do a better job of paying attention to.

Development

Zones of Quality - Oren Eini (probably better known as Ayende Rahien) outlines his approach to creating layers of software and isolating the ones where quality matters. It’s an interesting approach, and one that I haven’t seen before, however I definitely appreciate the approach of creating quick and sloppy implementations of the unimportant code knowing that you will return to fix it later.

PDC Session list - Robert Cain thoughtful collected links to all PDC sessions and wrote a batch file to rename them all to something more useful than AA12345.WMV.

Web platform installer - Getting up and running with ASP.NET on Vista/Win2008 just got easier with this new tool from Microsoft.

SQL Server

How long is left after killing a process? - How to get a progress report from SQL Server when you’ve KILLed a process.

Scalar functions are evil - Just in case you needed a little reminder about just how they’re evil. I forget sometimes and have to remind myself.

Social computing for the DBA - Us data folks aren’t always the most social lot on the planet, so it’s always nice to get a reminder of how to get out there and use new technologies. Jason isn’t presenting until December 3rd, so there’s plenty of time to put this one off.

Whoops: the DBA Quiz - database administrators blog about their mistakes. Chris Shaw started this, Brent Ozar called me out to admit my mistakes. Other notables involved include Jason Massie, David Stein, and SQLBatman.

Brent Ozar has tagged me to share two mistakes I’ve made as a DBA throughout my career. (Apparently this was started by Chris Shaw.)

Mistake the first: DELETE without WHERE. Like Brent (and every other person on the planet), I ran a delete without a where clause.

A few years ago, I had a consulting gig at a local utility company doing application development. This app was a relatively sophisticated homegrown CRM application that handled all customer interaction. The call center reps logged into it every morning when they sat down and logged out every night.

It was still very early in my career as a data mongoose and I was exceptionally inexperienced in the ways of properly executing SQL jobs (I was putting BEGIN TRANSACTION … ROLLBACK TRANSACTION around my SELECT statements until someone told me different). Some bad/duplicate customer data had made its way into the database and it had to be deleted right now. Being the good little developer that I am, I immediate whipped up a delete script, on the dev system and properly hidden inside a BEGIN TRANSACTION … ROLLBACK TRANSACTION block, and wrote a select statement to verify that my SQL did exactly what I wanted it to do. The first time around it didn’t, so I deleted a few lines and didn’t bother cleaning up my code formatting because I was in a hurry. After a few iterations, the delete was working perfectly, my manager verified it and told me to go ahead and run it.

So far so good, right? Well, a few days earlier, another developer had showed me that you could highlight text in the query window and press F5 to run the query. This way I didn’t have to keep changing my code to say COMMIT TRANSACTION instead of ROLLBACK TRANSACTION. I switched my connection over to the production machine, highlighted what I thought was my code, and hit F5. I walked away to grab a drink from the fridge.

When I got back to my cube the query was still running. It turns out I failed to highlight my entire query due my laziness in cleaning up my formatting and my overconfidence that my query was going to work correctly. Instead of deleting a few customers from production, I was deleting all customers from production. On Monday. An hour before lunch.

Anyone who has ever worked in a call center can tell you that Mondays are very busy days.

Long(ish) story short, we managed to restore from that morning’s back up, but not before all of the people on the call center floor knew that I was responsible for the outage and I was the reason they would have to write down all of the customer complaints by hand and then enter everything in the system as fast as they could during their idle time.

Moral of the story: Check everything you do at least 7 times. Paranoia is key to not deleting all customer records on a Monday.

Mistake the second: Not verifying data. I’m really not sure what to call this apart from just plain stupid.

We’re currently waiting for a data load process to be finalized so that replication can be turned on. In the meantime, I’m developing against partial, stale, data. My manager and I decided, in our infinite wisdom, to script the database, drop it, rebuild it, and then import the data from the publisher database. It was a fairly sane idea, relatively speaking. We went ahead and did this, everything was successful, and we went home, content in the knowledge that we had done a good job.

The next day, I start working on stored procedures again and I notice that conditions are occurring that shouldn’t ever happen in this data. Bills aren’t lining up with the services on them, accounts are reporting incorrect data, the associations were just completely incorrect. It looks like, somewhere along the line, when selecting the tables for import one of us didn’t check the “Enable Identity Insert” box. All of the records inserted correctly, but the keys didn’t line up.

The solution: drop the existing database, detach the publisher, copy the 16.5 GB file across the network, reattach both databases, and then rebuild the extra, non-replicated, tables from scripts (which I thankfully kept).

The unfortunate side effect of this is that all three members of my team, including myself, lost the ability to work for about 8 hours and I had to troubleshoot the queries I had been working to prove to myself and my manager that the queries were right but the data was wrong.

The moral of the story: I’m not sure if there is one apart from ‘Seriously, why didn’t you check everything 7 times like you just said we should always do?’

I’m supposed to pass this on to other DBAs, so I’m going to tag Rick Kierner. He might not be a DBA, but I know he’s broken a few things in his day.

Some other participants in this quiz bowl of DBA embarrassment include Tom LaRock and Jason Massie.