The DBA Detective: Disturbing Developments

I woke with a start, bathed in cold perspiration. I was in my cubicle, at work. The last thing I remembered, I’d been going through the morning emails. Last night had been busy: patching the servers, plugging the latest security holes with bits of chewed bubble-gum, the usual routine.

OK, I shouldn’t have dozed off, but I suppose that the effect of last night’s cheap bourbon had begun to kick in. I’m only an occasional drinker, the kind of guy who goes out for a beer, and occasionally wakes a few days later in Saigon with a full beard.

As I pulled myself back to full wakefulness, my nerves tingled, sensing some evil presence, perhaps in the cubicle, veiled by the shadows. I glanced at my screen and then I noticed it.

“What’s this? A new database? Where did that come from?”

I recoiled instinctively, as if a great cobra sat coiled on the keyboard. Dark forces seemed to be stirring. I shook my head.

“My nerves must be out of order!” I told myself. After all, what’s the harm in a new database. I’ll just keep a weather eye on the server and it’ll be fine.

The phone rang. It was the SAN Admin, Mildred. She cut short the usual pleasantries, never a good sign. “How’s the boy? What ‘cha doin to my disks?” she hissed, as if I was personally doing her wrong.

Hell, it must be that new database. I played dumb. “I don’t know what you’re talking about Mildred. Which server is having trouble?” I tried to sound nonchalant, but my voice came out as a nervous squeak; the disks were her girls and they weren’t happy.

“There are massive reads and writes going on. My girls are rattling like mice.”

“I don’t know, Mildred. There was a new app released last night, maybe that’s it.”

“Well, ya better find out, and fast. Don’t you guys have any monitoring?”

Mildred knows how to make a guy feel small. “We’re working on it,” I lied, smooth as a grifter running twenties on a barman.

I put my feet on the floor, swabbed the back of my neck, and pulled the keyboard towards me. That poison snake of a database was bugging me. I did what I always do, go and shake down the historic records in the cache to see what falls out:

I pulled out the text of one the queries:

Whoa! What the heck is this app doing? This was lowest-common denominator T-SQL of the worst kind. Who writes queries like that? It’s almost as if a machine was…and then it hit me like a lead slap to the back of the head. Didn’t I hear someone in the lunchroom talking free and loose about the latest ORM?

Used with skill, Object Relational Mapping software is a great way to build the data access layer of an application. In careless hands, though, it’s deadly. If these queries had Thompsons, there would have been blood all over the server room floor.

I wondered if they’d walked into the N+1 trap? I looked at another couple of queries…and there it was, like a great fat spider in the center of its malignant web:

There were more versions of all the queries with varying number of parameters of varying size. This particular query had a minimum run time of 900ms. It had been called 1,138 times in the last hour. What we had here was a bad dose of unparameterized, ad-hoc queries, causing every query to be compiled separately and placing untold CPU and memory stress on Millie’s poor girls. No wonder she wasn’t smiling.

I had better check the tables too. Just as I feared. Clustered indexes and logical constraints were conspicuous only by their absence. If you’re using a relational storage engine, you have to work within its rules, and this object-oriented approach to tables was breaking as many rules as it could find.

I was clearly on a path to the Developers’ Den, but I wasn’t going alone this time. I called Mildred back.

“We’re going to talk to the Dev Lead?” she asked in a voice quieter than I knew her capable of. A strange rattling noise echoed down the line, eerily reminiscent of her beaten-down disk drives. Her teeth were chattering with a nameless dread.

The Developers’ Den

I met Mildred in the corridor, which had a smell of old carpet and furniture oil and the drab anonymity of a thousand shabby lives. Her smile was as faint as a fat lady’s at a fireman’s ball. The only way in to the Developers’ Den was through a little sliding door that let those inside see who was trying to enter their space. It slid open a fraction, revealing a pair of eyes regarding us suspiciously.

“We need to talk about the data access layer for your new application.”

“Who are you, the DBA? I thought you quit. Or maybe that was the guy before. It’s a pity what happened to him…after he complained about our last application.”

How could I forget? “Look, we just need to talk to the Dev Lead for a few minutes. I’m sure we can sort this out.”

“He’s busy. Just do what you usually do. Put a few indexes in the database.”

I sighed. Why do devs think an index is the answer to all their problems?

“An index ain’t going to cut it this time…” He was already sliding the door closed. “I hear you guys also have a few Access apps that need a lot of healing. Good luck with that!”

It was desperate but it got him where he lived. The door slid back open to reveal a face with as much expression as a cut of round steak and a similar complexion. The smell of old pizza and Nerf washed over us. Keyboards clattered away on a hundred laptops and desktops. There was a lot of forking, pulling, and merging going on in here, interrupted by an occasional piercing scream, as someone attempted a rebase. He led us to one of the Dev Lead’s assistants.

“The DBA? I thought you quit,” he sneered.

“Rumors of my demise seem to be exaggerated. Listen, your new app is beating on our database like a heavyweight contender smacking a palooka around the ring in a fixed fight.”

“That’s a real pity. Sounds like you need a few sticking plasters, or indexes as you like to call them.” This guy had all the charisma of a plastic cup.

I counted ceiling tiles for a minute then leaned over his desk, knocking a Lego Millennium Falcon off its stand.

“No. An index won’t help this time. This is a real code problem. And unless you want to spend the rest of your very short career putting in daily security requests for access to your dev database, get me to the Dev Lead! Now!”

Keyboards quieted all around the room. I heard mumbling and pointing and the phrase “…thought he quit…”.

The developer fumbled with the Millennium Falcon, accidentally breaking off a missile launcher, before giving up and looking at me again. I hadn’t known him long but I could tell that thinking was always going to be a bother to him.

“OK. Your funeral.”

He grabbed his phone. The response was quick. “He’s down in Conference Room C. It’s just…”. We didn’t wait to find out more.

Conference Room C

Anger and frustration can carry a man far, but on nearing the Dev Lead’s inner sanctum I took stock. Maybe the devs were right; an index or three might prop things up well enough. Forced parameterization might just help too. I glanced at Mildred. The look in her eyes told me I couldn’t let her girls down. It was time to stop compromising.

I shouldered on into Conference Room C. Around the room, little notes colored green and blue and pink fluttered faintly in the gentle breeze of the air conditioner. Don’t let the pretty colors fool you; each of those pretty notes will become real code that can crush a database with one gentle waft of its bat-like wings.

We walked in, as inconspicuous as a tarantula on a slice of angel food. And there sat the Dev Lead, fingers flying across the keyboard while one of his flunkies watched.

“…and that will fix the object inheritance problem you were hitting. Now get out before I put you in charge of maintaining the source control and bug-tracking systems for the next project.”

The flunky stumbled into us as he tried to leave. The Dev Lead glanced up. He smiled, as stiff as a frozen fish, and glanced to either side of the door.

That’s when I noticed the code-droppers standing there, hulking, menacing developers, wearing Star Wars t-shirts and each with a paw full of pizza. I tried to ignore them as the Dev Lead started to speak.

“Ah, Joe Deebeeay. I heard you quit. Who is that with you? Mildred from the SAN team. Darling, come in and sit here next to me.”

Mildred edged nervously around the conference table and perched herself right next to the Dev Lead. Suddenly my side of the table felt like the loneliest place in the world.

“What can I do for the database team?” He beamed magnanimously at Mildred, but his eyes were like steel when they turned on me, and his voice as cool as a cafeteria dinner.

Gulp. “The application that was installed last night, it’s causing major problems with the database and the disks. I did a little investigation and it looks like you guys introduced an ORM for the data access layer.”

At the mention of the ORM, his brow furrowed. Suddenly, I was a particularly large rattlesnake and he had his gun ready.

“Look, you know, I’m not going to have this ‘ORMs are awful’ conversation again.”

I took a chance and interrupted, “Of course not. ORMs are extremely useful tools that…” I choked on my own words for a second “…facilitate the speed of software delivery. No question. Keep using the ORM.”

The Dev Lead’s jaw was hanging open and one of his code-droppers dropped his pizza on the floor. It bought me the time I needed.

“The problem is not the ORM; just a few small mistakes in the way it was implemented. The database that it created is a direct copy of the objects in the code. Your guys turned ORM into OOM and that’s the issue.”

He breathed softly, twisted one large hand around his knee and looked down at it. I took it as an excuse to go on.

“There are some places where this direct object storage works well and others where we need to adjust the storage to be a little more…relational. After that, we drop a few clustered indexes in the appropriate places and we’re almost home and dry.”

At the mention of indexes, there was an audible sigh of relief and the tension in the room lifted. Finally, I was acting like a proper DBA again. It seemed like as good a time as any to drop the bombshell.

“Of course, to make this work, we’ll have to adjust some of the code to use stored procedures.”

The final two words came out in a strangled whisper, about as welcome as a dead rat in the punch bowl at a classy dinner party.

The two code-droppers closed in menacingly, “Thanks for the advice. Now, here’s some for you and your stored procedures…”

My time was almost up. “We’d only need to do this in one or two places. We could use your generated code for most of the CREATE, UPDATE, and DELETE queries, and those READ queries where it made sense. That’s what the ORM does best. In other places, we’d just substitute straight T-SQL for the batch-mode processing that T-SQL is good at.”

The Dev Lead lurched to his feet, practically knocking Mildred off her perch. My next words, possibly my last, came out in a frantic rush.

“Look, it’s not as if this is premature optimization. Mildred and her girls, uh, I mean disks, are dying out there! This would eliminate the N+1 problem and many of the other issues with passing in hundreds of parameters.”

By this time, the Dev Lead was round my side of the table. Suddenly, I missed feeling lonely. His outstretched arm appeared to be heading for my throat but at the last second diverted and went around my shoulder.

“You crazy kid!” I was older than he was, but decided not to argue the point.

He turned to a code-dropper, “Can you believe the brass on this kid? He walks in here and tells us how to run our show?” He paused, and then laughed. “I love it!” That was all that the muscle needed to hear and they joined in the laughter.

“OK, let’s see what we can do, together, to save Mildred’s girls.”

Mildred managed a weak smile. We made plans for further meetings to establish mechanisms of communication between the teams and start the process of identifying the worst performing code. We turned to leave, but the Dev Lead had a parting shot.

“Oh, and Joe, I’ll book a time for us to have a quiet word about those Access apps that are giving us trouble. It looks like the Access team might need a new member for a week or two.”

I managed a weak smile too.

The Fix?

Mildred and I finally made it out of the Developers’ Den. I had a full schedule of appointments and a lot to explain to the Fat Man. Mildred was grinning from ear to ear but then noticed the look on my face, “What’s the matter, Joe? Apart from your impending stint in the Access asylum, we got everything we wanted.”

“I dunno, Mildred. Time eats away at all good things, and some time real soon the devs are going to update that app, and their ORM will start messing again with the database. Your girls ain’t safe yet.”

The smile vanished from her face.

“Plus, once there’s data in place, we can’t just let the ORM create, drop, and alter tables, right in production. The Fat Man won’t like it. He’s funny that way.”

I scratched my head, contemplating my future. “We need to get the database and its objects into source control and then lock down production. We can still let the ORM tool auto-generate certain objects, directly into the development databases, but we could capture those changes by comparing between the new database and what was in source control. That way, we can generate safe update scripts for deployments to production.”

One way or another, I knew I would be spending a lot more time in the Developers’ Den.

Maybe I should have quit.