Voice of the DBA

Author: way0utwest

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.
```
August 11, 2010
The PASS SQL Rally Logo – I Won!

Well not me. It wasn’t my design, but when I went through the entries, this was the one that caught my eye first and I thought would be great.

I think it’s nice, clean, simple, and most importantly, I think it will look good on shirts, mugs, signage. Always an important thing.

Now hopefully we’ll get a 1, 2, 3, etc. incorporated into this, and perhaps the name of the city or year in there as well so we’ll have momentos over time.

August 10, 2010
Tools Only Help

“Building an easy-to-use tool does not make an amateur more skilled at making applications and more than a $900 table saw makes a plumber better at making cabinets.” – Bill Vaughn

Bill is talking about LightSwitch, a new application development tool from Microsoft. And it’s true. Better tools help, but it still requires some skill from the person doing the work to turn something out that works well.

August 10, 2010
T-SQL Tuesday – Beach Time
I almost forgot about T-SQL Tuesday this time, so I’m scrambling to get an entry done today.

This is a monthly blog party, on a central theme. This month the theme is Beach Time, from Jason Brimhall, and it’s a good one. This is the 9th T-SQL Tuesday, started by Adam Machanic on his blog earlier this year.

Packing Heavy

I’ll admit, when I travel I typically over-pack a bit. I think it’s because I’m a little anal-retentive, a little detail oriented, and I hate missing things when I’m traveling. I know I can usually buy my way out of a jam, but sometimes it’s a pain, and I don’t like it.

What do I need to do before I leave? There’s one big thing here, and that’s communicate. If I have processes that will notify me of problems, or things to watch, I have to be sure that I have someone watching for those notifications, and I have trained them on what to do. I need to let my boss know who will be covering for me (if they haven’t designated someone) and I need to let any customer service people, business analysts, etc. that I typically work with, that I’ll be gone.

All of this leads to my second point: expectations. I haven’t always done a good job of setting expectations in the past, and have been interrupted while on vacation. Part of that is OK if you’re well-paid, you’re the go-to-guy, but you don’t want that to be the default when you’re on vacation. Let your boss know that emergencies mean down systems, not a sales guy trying to close a deal for his month. It means significant loss of revenue, not a report that the boss wants today instead of next week.

This is hard, but it pays to set expectations, and it pays to respond to inappropriate calls with “I’m on vacation, we can talk when I get back.” This is much better than “call me if you have a problem,” which invites people to interrupt you, disturbs your family, and doesn’t teach others to think for themselves. Let them flounder a bit; I can almost guarantee they’ll be fine.

Emergencies

There are true emergencies, and it’s possible that whoever you leave in charge can’t judge them well. They might call you and ask if this is a problem, and you might need to take that call. Ever since cell phones have been common, since around 1996 or so, I’ve had one. It’s typically been used for work, with me billing back some of the charges. Before that, I had a pager.

I’ve had numerous interruptions over the years, but two emergencies that stood out. One was when I worked for a small company and took vacation because I had family in town. I was around the local area, and took a call from my boss near the end of my vacation that a server was down. A RAID controller had failed, and while the sysadmin had replaced it, they couldn’t restore the SQL Server. They kept running this:
```
RESTORE DATABASE XXX FROM DISK = ‘YYYY.bak’

RESTORE LOG XXX FROM DISK = ‘ZZZZZ_1.trn’

RESTORE LOG XXX FROM DISK = ‘ZZZZZ_2.trn’
```
Anyone see an issue? Yep, they weren’t using NORECOVERY. They actually were using the GUI, but I recognized the issue. I was driving nearby, and started walking them through the issue, but realized it would be quicker if I just helped. I stopped by, explained what was happening while starting the restore, and then built a quick script to grab all the log files (about a dozen) and restore them all for them.

My second emergency was for the SQL Slammer worm. It actually hit on a Sunday night as I was driving back from a ski vacation. Work couldn’t reach me since there was no cell service, but had convened an all-hands meeting since the network at a Fortune 1000 company was actually shut down. I got a message when I got home and went to work that night. Vacation ended early, but I made up some time later in the week.

Vacations are Hard

It’s hard to prep for being away. It’s hard to make sure work is done, and that things function without you. I know many people worry that if things go too smooth when they’re gone, the company won’t see them as valuable.

I think that’s wrong. You can bring value to the company, and still be away. The key is to pace yourself, and don’t get too overworked. If you have deadlines, make sure they account for vacation, and perhaps push things back. You don’t want to be unreasonable, but don’t be all-accommodating either. It will upset your balance, and could ruin your vacation.
August 10, 2010