Voice of the DBA

Category: Blog

  • Saved By a Spare

    I started traveling with two laptops after I had a laptop fail during a trip in 2009 the day of a presentation. I bought a netbook as an emergency, but it didn’t work well and ended up as the bedroom spare for my wife.

    In it’s place, I’ve started carrying an 11” tablet along with my 15” laptop. They both fit in my bag, and in the course of 5 or 6 trips, I haven’t needed the spare.

    Until SQL Saturday #28

    I had some family things come up this past week and didn’t get all my work for Database Weekly done before I left. Since I had some free time on Friday, I was planning on working at the Marriot when I arrived. Everything went fine until I plugged my laptop into the wired connection at the hotel.

    Nothing.

    No lights, not LAN indicator, no connection. I got a spare cable from the front desk, rebooted everything and no connection. I even called tech support before hanging up after being on hold for 10 minutes. Somewhere in the back of my mind it occurred to me to rule out my wired connector in the laptop. I think it used to work, but wireless is so ubiquitous that perhaps I hadn’t.

    Actually I noticed the spare tablet in my bag and decided to plug it in. I’d need to sync it anyway, so I plugged it in and it worked.

    I was late to the speaker dinner, and left early, coming back to work, but I was able to get all my work done. Or at least what was important for the weekend.

    The last few times I’ve been in a hotel, I’ve had issues with my network connection, so I should have known better than to count on it, but this time, having that spare really saved me.

  • Travel Day – SQL Saturday #28 Baton Rouge

    On my way to New Orleans today where I’ll pick up a car and then drive to Baton Rouge for SQL Saturday #28 tomorrow. This is my second time heading to Baton Rouge for SQL Saturday and I’m looking forward to it.

    Last year I flew in early, thinking I’d get to the New Orleans Saints training camp in the afternoon, but a thunderstorm cancelled the event and I missed it. This year Friday is a day off, as is Sunday, so I won’t go again, :(, but I am likely meeting Tim Mitchell (blog | Twitter) for lunch as he’s coming in from Dallas about the same time.

    I’m looking forward to a little New Orleans food and then a great speaker’s dinner tonight.

    Tomorrow I have an early session, Beginning SQL Server, to try and get people introduced to what SQL Server can do and a few basics. If you know about SQL Server, but haven’t worked with SSIS, Tim Mitchell has a basic session on Building Your First Package early in the morning as well.

    I also have a seat on the Ask the Experts table during the afternoon, but before that I’m doing my Modern Resume presentation at 10:15. I’ll be done early, and hanging out until the raffle. Then I’ll go run around University Lake before heading to the after party.

  • PASS Advice – Marketing Pre/Post Conference Seminars

    Last week I attended the PASS Performance Virtual Chapter’s presentation by Adam Machanic on Parallelism and Performance. It looked interesting, so I logged on and listened to Adam while doing a little busy work. I had gotten an announcement over at SQLServerCentral for it, and had run it, but hadn’t thought much of it.
    In doing so, I missed this short sentence at the end of the description: “This session is a small preview of some of the material that will be covered in Adam Machanic’s full-day PASS Summit post-con, “A Day of Doing Many Things at Once.”
    I don’t know if this has been done in the past, but this is a great way to market the pre and post conference seminars. Prior to sitting in for part of one a couple years ago, I had questioned whether these seminars were really with the cost, and the time, of attending an extra day at the PASS seminar. I have to say that I think they are definitely worth it, at least this one is.
    This is also a great marketing technique. I checked Adam’s post con seminar page, and there’s no mention of this presentation. To me, this ought to be linked on the page, on the page with all pre/post cons, and in marketing messages, including the ROI page.
    In fact, I’d suggest that every pre/post con speaker actually do a 30 minute preview of their session in an online presentation. It’s great practice, good exposure, and a nice marketing message for the Summit. It helps out the VCs as well, as they are always looking for good speakers.
    If I get elected to the board this year, I might even make this a requirement for the pre/post con speakers for 2011.

  • Sharing a Temporary Table

    One of the things that I’ve often seen people do is build temp tables to hold data for each connection in a unique way. Back in older versions of SQL Server this was bad since large temp tables could lock sysobjects in tempdb and cause contention. That’s been fixed, but I still tend to avoid temp tables unless it’s truly something that is session dependent.

    I’ve seen some loading routines that needed temporary or staging data. We used to have some of this in a financial services firm where we had dedicated machines that would pick up files and then load them. However since we weren’t sure which machine would load what data, we wanted a shared data source. The developers didn’t know what to do, or how to share a staging table, so they used temporary tables. This gave us an all-or-nothing approach since the temporary tables disappeared if the connection was lost.

    We also needed a similar shared temporary storage at SQLServerCentral for mail processing. We wanted to pull out rows from a table for a particular sending process, and mark them as used by that process only. Since we had multiple machines that would act as senders, each of them generic, again, we wanted to share some type of “workspace” on SQL Server, but have it unique to each connection.

    The way that I’ve handled both of these in the past is to create a real table containing the structure I need and then add a column to it that identifies the session. If there’s an issue, you can see what rows were in process by a session and either assign them elsewhere, or return them to the pool.

    As an example, let’s say you need to send lots of emails, but you want to store some of this data in SQL Server as it’s processed. I could create a table like this:

    CREATE TABLE EmailSends
    ( EmailAddress varchar(20)
    ,     SUBJECT varchar(200)
    ,     Msg varchar(2000)
    ,     SPID int
    )

    I have the basic data here to send an email. I can load this from other tables, and then add in the SPID. This column allows me to uniquely identify a session, and the process working with this table can determine which rows it should process. I could load this table like this:

    INSERT EmailSends
     SELECT 
            EmailAddress, '', '', @@SPID
          FROM EmailList
    This inserts all the emails, and marks these rows as belonging to my SPID. If another connection ran this same query, they’d have rows marked with a different SPID. I could now do some processing on “my” rows like this:
    UPDATE dbo.EmailSends
      SET Subject = a.SUBJECT
     FROM     EmailSubjects a
     WHERE a.EmailID = 1
     AND EmailSends.SPID = @@SPID

    What happens if there’s data in this table with my SPID that isn’t mine? That can’t happen if you do this both at the beginning and end of your connection:
    DELETE dbo.EmailSends
     WHERE SPID = @@SPID

    It’s a crude technique, and doesn’t necessarily fit a lot of situations, but if you want to use this to manage workloads, maybe redistribute things to different processes, this can help you.