Hello GETDATE() My Old Friend…

So you’ve decided that your new web application needs to record some page load time metrics so you can keep tabs on performance. Terrific!  You set up a couple page load/complete functions to write to a logging table when a page request comes in, and then update the record when it finishes loading.

INSERT INTO PageLogs (
    RequestTime
   ,ResponseTime
   ,RemoteIP
   ,UserName
   ,PageId
   ,Parameters
   ,SessionId
) VALUES (
    GETDATE() 
    ,NULL
    ,127.0.0.1
    ,'Dave'
    ,'Home'
    ,'Pd=2015Q2'
    ,'883666b1-99be-48c8-bf59-5a5739bc7d1d'
);
UPDATE PageLogs
    SET ResponseTime = GETDATE()
    WHERE SessionId = '883666b1-99be-48c8-bf59-5a5739bc7d1d';

You set up an hourly job to delete any logs older than 2 weeks (just to prevent information overload) and you call it a day. Each morning, you run a report to look at the previous day’s performance, watch the trends over the past week or so, and you’re pretty happy with things. Pages are loading in a fraction of a second, according to the logs. People find the application useful, word spreads around the office, and adoption takes off. The project is a success!

Then the support calls start rolling in. Users say it’s taking “forever” to load pages (they don’t have exact numbers, but it’s just too slow). This can’t be possible. The report says everything’s running just as fast as it did in test!

You walk down the hall and visit your friendly Senior DBA. He pulls up his monitoring tools and shows you that the hourly maintenance that keeps the PageLogs table fit & trim is causing a bunch of blocking while it does lots of DELETEs. And your INSERT queries are the victims.

Here’s the thing: GETDATE() (like any other function) doesn’t get evaluated until that query executes. Not when you call ExecuteNonQuery(), not even when SQL Server receives the query. So even if your INSERT isn’t holding up the execution of your page (because you’ve executed it asynchronously), it won’t accurately represent when the page load started. Instead, it tells you when your query executed. In this context that can be misleading because it won’t tell you how long it really took for your page to load.

If you need to log the time an event transpired accurately, GETDATE() isn’t your friend. You need to explicitly set the time in the query.

INSERT INTO PageLogs (
    RequestTime
   ,ResponseTime
   ,RemoteIP
   ,UserName
   ,PageId
   ,Parameters
   ,SessionId
) VALUES (
    '2015-05-15T09:45:00Z' 
    ,NULL
    ,127.0.0.1
    ,'Dave'
    ,'Home'
    ,'Pd=2015Q2'
    ,'883666b1-99be-48c8-bf59-5a5739bc7d1d'
);
UPDATE PageLogs
    SET ResponseTime = '2015-05-15T09:45:02Z'
    WHERE SessionId = '883666b1-99be-48c8-bf59-5a5739bc7d1d';

If you aren’t used to seeing significant blocking in your databases, you may not have run into this. But get into this habit anyway. At some point you probably will see blocking on a table like this, and logging with GETDATE() will make the data you attempted to write during that blocking invalid. If you can’t trust all of your data, can you trust any of it?

Getting Over It or: How I Learned to Stop Worrying and Love Speaking

Consider this the outtakes from my previous post about speaking at SQL Saturday.

It took a while for me to build up the courage to finally get up in the front of a room at SQL Saturday. As I mentioned in my prior post, I did quite a bit of studying of other peoples’ sessions, read peoples’ studies of other peoples’ sessions (Grant Fritchey’s “Speaker of the Month” series) and talked to a few people at the speakers’ dinner. Here are a few of the key things I learned which put me more at ease.

Everyone gets a little nervous

Feeling a little twinge of nerves is completely normal, even for seasoned speakers. Those feelings are what keep you on your toes. Get “comfortable”, get complacent, and you’ll probably overlook something.

Your audience is there for you

If you’ve only ever spoken previously in a classroom setting or making a pitch at work, SQL Saturday is very different. In those other scenarios, you have a mandated audience. People are there because they have to be there. They don’t really care much about you or what you have to say. At SQL Saturday, your audience is has opted into your session. They’re there because you have something they want. They’re receptive. They’re giving you their time and attention.

It’s OK to unwind after you speak

I don’t mean you should run out of the room as soon as you’ve finished the last slide. People may have questions they want to ask you. But if you need to go to the speaker’s room for a bit to decompress and unwind afterwards, it’s OK.

You’ll never finish the slide deck

I completely redid one slide on Thursday night, and was still fiddling with a few others Saturday morning. Just don’t tell people that you were working on it right up until the last moment; as long as what you say matches up with what’s on the screen, they don’t have to know.

It’s only SQL Saturday

That’s not meant to diminish SQL Saturday at all. But you’re not hosting the Oscars. If something goes wrong, it’s not happening live on TV with 50 million people (including your parents and kids) watching. You aren’t a paid professional speaker – you’re just there to share with people. People will give you some slack if you aren’t perfect.

It’s important to look at the feedback you get (attendees: please fill out those evals!). Reflecting on what went well is just as important as looking for areas of improvement.

What went well

  • I hit all but one of the points I wanted to hit. The one I missed wasn’t critical.
  • I didn’t run short on time. I think I paced myself pretty well, and took a sip of water when I felt I needed to slow myself down.
  • All my demos & equipment worked. My demos depended upon Azure, and RIT (our hosts) made major improvements to their guest network since last year.
  • I picked up a Logitech R400 remote so I wasn’t tethered to the podium for changing slides. I’m a “fidgety” kind of person, so in addition to achieving that goal, it gave my hands something to do without attracting attention
  • I didn’t spontaneously combust

What I need to work on

  • People want demos, not talk and slides. I’m already working on trimming the deck down to give myself more time to show and explain code.
  • I had trouble reading the audience. This is something I have trouble with elsewhere as well. Maybe I need to pick up a book on body language.
  • Most of my attempts at levity fell flat. I knew I was rolling the dice and while I didn’t roll snake eyes, I didn’t roll a 7 or 11 either. I also had one obscure reference which I knew only one person in the building would be likely to pick up on, but he wasn’t in the room. That one didn’t hurt me, but had I been able to find the image I really wanted, it would have worked better.
  • I didn’t move around as much as I wanted or expected to. I thought I was going to make more use of the notes I’d written in PowerPoint but to read them, I would have had to stay too close to the podium. Next time, fewer notes & more moving around.

I’m looking forward to working on that last point. I was disappointed with how few demos I had for my session, and that feeling was backed up by some of the feedback I got. Next time, it’ll be better.

I’m not sure when the next time will be. Unfortunately, the SQL Saturdays that are close enough for me to get to interfere with other obligations on my calendar.