Ed Leighton-Dick has renewed his New Blogger Challenge this month. Here are all (I think) the posts for this week after Ed posted his announcement. If I’ve missed any, please let me know and I’ll update.
Spend any time around a 4 year old, and you will inevitably find yourself involved in a conversation which evolves into this:
It’s a simple, but powerful and important question. The trouble is that when it’s a 4 year old asking it, in a lot of cases they can’t understand the answer. More often, they aren’t interested in understanding it.
Fortunately, there aren’t any 4 year olds in the average IT shop (although it may not be too far off).
A while ago, a data issue came to my team. Nothing major, but enough that it caused problems for a user. It’s a small glitch with an application component which pops up maybe once or twice a year, so it’s been decided that it’s better to just fix the data in those rare cases as opposed to spending 20 hours tracking down the root cause & fixing it there (I’m the SME for this component).
The requested correction was to delete the entire record, based on a previous fix to a similar but unrelated data problem. By the time I saw the request, a teammate had picked it up & started working on it.
“Wait! Don’t do it that way!” I said. “All we should be doing here is fixing the one erroneous field on that record.” This had come up in the past, but with it happening so rarely it’s easy to forget about.
I paused to catch my breath, then heard it.
Why?
I had to pause even longer to collect my thoughts. I don’t often get asked questions on things like this but I wish it happened daily.
This is the moment in which knowledge is gained, even by the answerer.
When you live & breathe a system for years on end, it’s easy to take certain aspects of it for granted. You respond without having to think about why things work the way they do – you just know that’s how it is.
The ensuing conversation was productive and I hope informative for my co-workers. While deleting the record would have the desired short-term result (making the application function properly), in the long term it would break the link between the data and a document which is referenced by that data. A net loss. Fixing the one column (setting it to the value which it should have been in the first place) allows the application to function correctly and retain access to that referenced document.
The conversation also forced me to take a closer look at my own understanding of the issue and re-evaluate what I thought I knew about it. It turns out, I had some bad assumptions in there too, which I was able to correct.
Not only did my teammates learn, I learned too. Everyone wins.
So why was the original solution of deleting the whole record requested? The answer isn’t too far removed from the idea of cargo cult programming. That is, someone saw the solution of deleting the whole record used in a similar case years ago, documented it, and it was seen as the One True Answer from that point forward – regardless of its applicability to the situation at hand. A detailed explanation of “why” isn’t usually written for every issue that comes to our team for resolution, for a few reasons:
In hindsight, a carefully-written, more thorough explanation many years ago may have prevented this particular request from being written as it was.
Asking why became the basis for Toyota’s approach to improving their manufacturing processes, and is built into Six Sigma and many other process improvement methodologies. This one word is the gateway to understanding, and once we understand, we can find ways to do things better.
If you’re curious about something, release your inner 4 year old. Just don’t act like a 4 year old when you do it. Keep asking why, get to the answers – and make sure you understand them.
If someone asks you why, embrace the question. This person is interested, they’re engaged, they want to learn! Take advantage of that opportunity to teach and spread that knowledge. Along the way, you just might learn something yourself.
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?
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.
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.
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.
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.
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.
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.
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.
That is the first and last Katy Perry reference you will find on this blog or anywhere else in my life.
Last weekend I spoke at the 4th edition of my “home” SQL Saturday, SQL Saturday #383. This was the end of a path that started four years ago, and the beginning of an exciting new one.
About four years ago, I was introduced to PASS. It didn’t take long for people to start talking to me about public speaking. I went to my first-ever SQL Saturday, and kept thinking to myself “I could never do that.” Then I was given the opportunity to attend PASS Summit 2012 and was hooked on the PASS community – SQL Family. I stepped onto the floor at the convention center and felt comfortable immediately. I think my exact words when I called home that evening were “I’m home. I found my people.” Mid-Summit, in a 10-minute conversation with a chapter leader, I was told “you should speak at one of my user group meetings.”
But I have nothing to talk about. I’m terrified of public speaking. I’ve only ever done it in a classroom, in college or high school and I hated it. It terrified me. And I’m not an expert on anything. Well…maybe. Someday. A long time from now.
Time passed. I got involved with my local PASS chapter, got heavily involved with our annual SQL Saturday events, and got to know (or at least meet) more people in the SQL Server community. And I kept hearing the question “so when are you going to start speaking?”
But I have nothing to talk about. I’m not a speaker. I don’t have the polish that all these people on stage at Summit or in the front of the room at SQL Saturday have. I’m not even a DBA!
In 2014, one of my professional development goals at work was to give at least two presentations. I pretty much didn’t have a choice now, I had to get up in front of a crowd. So I wrote & delivered two sessions:
I was starting to get a bit more relaxed about talking in front of people. But this was a comfortable environment – I knew everyone in the room. That summer, I attended Mark Vaillancourt’s (b | t) session DANGER! The Art and Science of Presenting at SQL Saturday Albany, looking to fill in some gaps and figure out how to put myself at ease in less familiar territory.
Well, maybe I can put together a beginner-level session.
In February 2015, I attended SQL Saturday Cleveland. One of my goals for the day was to catch beginner-level sessions. I wanted to study the type and depth of the material, as well as how it was presented. Late in the day I had my breakthrough moment. The room was completely packed and the crowd was hanging on the presenter’s every word. I finally had a grasp of how to tailor a topic to a “beginner” audience.
I don’t have to put on a flashy show with 20 different advanced features and techniques. There’s room for the basics because there are always people who are new to this stuff and they want sessions too!
That same month, we needed a a speaker for our chapter meeting and rather than find someone to do a remote presentation, I decided to dust off my PowerShell talk from work, retool it for a DBA crowd, and go for it. It went pretty well, and the next week I took the plunge. I wrote up an abstract and submitted for SQL Saturday.
Pressing this button is one of the most nerve-wracking things I’ve done. Deep breath…go.
At the chapter meeting, I’d gone over 90 minutes with my slides and demos. At SQL Saturday, I’d only have 60. I had my work cut out for me. I spent April tweaking and tuning my slide deck, honing my demos. I felt like I had a pretty solid setup. The Sunday before SQL Saturday, I sent myself to the basement and started rehearsing my presentation. I went 48 minutes. Without demos or questions from an audience (proving that cats don’t care about PowerShell).
Hard stop at 60 minutes. What can I cut? Where did I waste time? Am I speaking too slowly?
Every night that week I was in the basement, running through my presentation and demos. I got myself to 55 minutes for the whole package.
That’ll have to do. If I get questions mid-session, I’ll just drop a demo or two to make up the time.
I arrived home from the speaker dinner Friday night and did one last run through my deck. I had just redone one of my big slides Thursday night. Friday was a terrible run, but it was getting late. I had 38 minutes on the slides themselves.
Saturday morning, I awoke at 6 and my brain was already in overdrive; on a scale of one to ten, I was at an eleven. I fired up my Azure VMs so they’d be ready well ahead of time and hit the road for RIT. I found my room (I was speaking in the first slot) and got myself set up. I wanted to check and re-check everything. I was not about to let a technical problem take me down.
That settled, I milled around a bit and as 8:15 arrived, I found myself escalating from 11 to 15. People started filtering into the room and I tried to chat with them a bit as I’d read about doing so in Grant Fritchey’s (b | t) most recent Speaker of the Month post. That helped calm me down a bit.
8:30. Showtime. Breathe.
I feel like I fumbled a little bit on my intro (before I even got off my title slide), but by the time I hit my 3rd slide, a calm fell over me. I got out of my head and cruised through the material. It seemed like it was going smoother than any of my rehearsals. I wasn’t relying on my written notes. I got a couple chuckles out of the audience before I reached my demos. As I returned to the keyboard, I glanced at the clock.
What? 9:00? I burned through my slides in 30 minutes and I’d planned for close to 40. Am I speaking that quickly? Did I stumble that much when I practiced?
Fortunately, I’d set up my demos in preparation for such an event. I had a set of “must do” demos, and then a bunch of alternates which I could bring in to fill some time. I got through my demos, answered the lone question I was asked, and wrapped up right on time.
As people filtered out of the room and I started packing up, an enormous weight was lifted off my shoulders. I was done. I survived. And scanning through the feedback, it looked like I did an OK job. Reading through it later, I saw a few notes that meshed with things I was thinking during the session, and I will definitely take into consideration for the next time.
Yes, the next time. I’m doing this again. I’m hooked.
Slides & demos from my SQL Saturday Rochester presentation “Easing into Scripting with Windows PowerShell” have been posted on the SQL Saturday site.
Thank you to everyone who came out for my session and all of SQL Saturday!
I’ll be presenting my session Easing into Scripting with Windows PowerShell this Saturday, May 16th at SQL Saturday Rochester.
SQL Saturday is a free, all-day event for learning about SQL Server and related technologies, and networking with like-minded professionals in the region.
In addition to speaking, I’ll be tweeting out live updates all day long and posting pictures to both Instagram and Twitter. Watch for the hashtag #sqlsatroc (links to searches on both services).
The event is being held at RIT in Golisano Hall. Registration starts at 7:30 AM and sessions start at 8:30. We even have raffle prizes at the end of the day! If you’re in the area, come check it out!
This week marks the end of Ed Leighton-Dick’s New Blogger Challenge. It’s terrific seeing everyone sticking with the challenge all month and I’m looking forward to catching up with all the posts. Great job, everyone! Keep going!
We hear the common refrain among DBAs all the time. Back up your data! Test your restores! If you can’t restore the backup, it’s worthless. And yes, absolutely, you have to back up your databases – your job, and the company, depend upon it.
But are you backing everything up?
Saturday night was an ordinary night. It was getting late, and I was about to put my computer to sleep so I could do likewise. Suddenly, everything on my screen was replaced with a very nice message telling me that something had gone wrong and my computer needed to be restarted.
Uh oh.
In 7 1/2 years of using OS X, I’ve had something like this happen maybe 4 times.
After waiting whet felt like an eternity, the system finished booting & I got back into my applications. I opened up PowerPoint, as I had it open before the crash so I could work on my SQL Saturday Rochester slide deck whenever inspiration struck. I opened my file, and was greeted by nothingness. I flipped over to Finder and saw zero bytes displayed as the file size.
Uh oh.
“But Andy,” you say, “you use CrashPlan, right? Can’t you just recover the file from there?” Well, you’re half right. I do use CrashPlan. I even have a local, external hard drive (two, actually) that I back up to in addition to CrashPlan’s cloud service. But I couldn’t recover from any of those.
Because Dropbox is already “in the cloud”, I had opted to not back it up with CrashPlan when I first set it up. After all, it’s already a backup right? It’s not my only copy, it’s offsite, it’s all good.
Not so fast. When my system came back up, Dropbox dutifully synced everything that had changed – including my now-empty file.
So, now what? Fortunately, Dropbox allows you to revert to older versions, and I was able to select my last good version and restore it.
I broke The Computer Backup Rule of Three and very nearly regretted it. For my presentation:
Even scarier, if Dropbox didn’t have a version history or it had taken me more than 30 days to realize that this file had been truncated, I’d have lost it completely.
Everything else on my computer was in compliance with the Rule Of Three; I just got lazy with the data in my Dropbox and Google Drive folders. I’ve since updated my CrashPlan settings to include my local Dropbox and Google Drive folders so that my presentation should now be fully protected:
And don’t forget to test those backups before you need to use them. Dropbox, Google Drive and other online file storage/sync solutions are very useful, but you cannot rely upon them as backups. I don’t think you’ll ever regret having “extra” backups of your data, as long as that process is automatic.
Here are the posts collected from week three of the SQL New Blogger Challenge. It’s been compiled the same way previous weeks’ posts were. Everyone’s doing a great job keeping up with the challenge!
The Rest is Just Code 