Click here to monitor SSC
  • Av rating:
  • Total votes: 30
  • Total comments: 17
Grant Fritchey

The DBA Detective: Disturbing Developments

31 March 2014

The DBA Detective: Disturbing Developments

Originally one of the articles in the first DBA Team series, Grant wonders what Raymond Chandler or Dashiell Hammett would have done if asked to write technical articles for Simple-Talk. He came up with the DBA detective, hard-boiled Joe Dee Beay  (see also The DBA Detective: The Case of the Missing Index.)

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.

The phone rang. It was the SAN admin.

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.

I ... pulled the keyboard towards me

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:

SELECT SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,

     (CASE WHEN (deqs.statement_end_offset = -1)

      THEN LEN(dest.text) * 2

      ELSE deqs.statement_end_offset

      END - deqs.statement_start_offset) / 2 + 1), -- the query statement in the batch

        deqs.execution_count,

        deqs.total_elapsed_time,

        deqs.total_logical_reads,

        deqs.total_logical_writes,

        deqs.total_physical_reads,

        deqs.total_worker_time,

        deqs.min_elapsed_time,

        deqs.min_logical_reads,

        deqs.min_logical_writes,

        deqs.min_physical_reads,

        deqs.min_worker_time

 FROM sys.dm_exec_query_stats AS deqs

     CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

 ORDER BY deqs.execution_count DESC;

I pulled out the text of one the queries:

SELECT OrgID 

       ,OfficeID 

       ,StartDate 

       ,EndDate 

       ,OtherID 

FROM SpecialList 

WHERE OfficeID = 42;

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:

SELECT col1 
      ,col2 
      ,col3 
FROM Table1 
INNER JOIN Table2 
ON Table1.ChildID = Table2.OtherID 
WHERE tabl2.ParentID IN (@p1,@p2,@p3...@p99)

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

Her smile was as faint as a fat
 lady's at a fireman's ball

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!"

"The DBA? I thought you quit," he sneered.

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

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."

"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.

Grant Fritchey

Author profile:

Grant Fritchey is a SQL Server MVP with over 20 years’ experience in IT including time spent in support and development. Grant has worked with SQL Server since version 6.0 back in 1995. He has developed in VB, VB.Net, C# and Java. Grant has authored books for Apress and Simple-Talk, and joined Red Gate as a Product Evangelist in January 2011. Find Grant on Twitter @GFritchey or on his blog.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 30 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: What has gone wromh with Simple Talk?
Posted by: Anonymous (not signed in)
Posted on: Wednesday, April 2, 2014 at 5:08 AM
Message: I think the sole purpose of this article is to gather some email address and then send email to buy some Red-Gate Products.

I visit for some quick help rather than ready movie scripts. Need to move to someother site.

Subject: C'mon!
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 3, 2014 at 4:59 AM
Message: Seriously?

Subject: Obviously
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 3, 2014 at 6:02 AM
Message: Obviously. It’s purely a marketing Gimmick by Site Owners.

Subject: Re: Obviously
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 3, 2014 at 3:09 PM
Message: So where on the page do I give them my email address?

Subject: Nothing but Marketing
Posted by: Anonymous (not signed in)
Posted on: Friday, April 4, 2014 at 11:23 AM
Message: Yeah, because the entire story talks about Red Gate product after Red Gate product after Red Gate product. Nothing but one long sales pitch. I mean right there in paragraph.... uhm.... wait... hey, it doesn't mention a single Red Gate product... weird. That's one strange marketing gimmick!

Subject: Great story
Posted by: Ray (not signed in)
Posted on: Saturday, April 5, 2014 at 2:37 PM
Message: Loved it. Great way to teach and entertain at the same time.

Subject: Good Storytelling
Posted by: Jonathan (not signed in)
Posted on: Tuesday, April 8, 2014 at 1:26 PM
Message: Sure beats the typical ads.

Subject: I have to say I really dug it!
Posted by: Boston_DBA (not signed in)
Posted on: Friday, April 11, 2014 at 5:18 AM
Message: Entertaining and true

Subject: I enjoyed the horror story
Posted by: DBA Joan (not signed in)
Posted on: Friday, April 11, 2014 at 7:47 AM
Message: Feels like my days sometimes, just happened today I saw a developer was creating backup to unauthorised location, hehe. Now finally I have a reason to business to remove these people from sysadmin access ;-)

Subject: Re: I enjoyed the horror story
Posted by: Phil Factor (view profile)
Posted on: Friday, April 11, 2014 at 8:33 AM
Message: "...creating backup to unauthorised location"
Woah. Happened to me once. A CRM system. It was on its way to a sales person who was about to hand in his notice and move on to a rival company. Might make a good story in Grant's series!

Subject: Again?
Posted by: Anonymous (not signed in)
Posted on: Friday, April 11, 2014 at 1:17 PM
Message: Read this same story a few years ago

Subject: Re: Again?
Posted by: Andrew Clarke (view profile)
Posted on: Friday, April 11, 2014 at 3:42 PM
Message: It originally was published on the Red Gate site here
http://www.red-gate.com/products/dba/dba-bundle/entrypage/hard-earned-lessons-4 as part of the first series of the DBA team.
I think it was around eighteen months ago, but we enjoyed it so much we decided to republish it, but with the 1930s Crime comic illustrations as originally intended.
Ed.

Subject: This is great...
Posted by: Anonymous (not signed in)
Posted on: Friday, April 11, 2014 at 4:24 PM
Message: Except the Millenium Falcon doesn't have missile launchers

Subject: Re: This is great
Posted by: Andrew Clarke (view profile)
Posted on: Saturday, April 12, 2014 at 1:58 AM
Message: I'll have a word with the Tech reviewer about that.
Ed.

Subject: Re: This is great...
Posted by: Anonymous (not signed in)
Posted on: Monday, April 14, 2014 at 7:24 AM
Message: The Millennium Falcon does have missile launchers but they are internally not externally mounted. They fired the concussion missiles that knocked out the 2nd death star's primary power relay. The poor guy probably snapped off the lower guns... Get with the program dude!

Luckily I don't have the problem in my organisation considering I am the DBA and the lead developer... Next year the world... muahahahahahaaaaa

Subject: I enjoyed it
Posted by: Anonymous (not signed in)
Posted on: Monday, April 14, 2014 at 3:53 PM
Message: Nice little segue into the DBA day - thanks.

Subject: Good written, good described
Posted by: Anonymous (not signed in)
Posted on: Thursday, April 17, 2014 at 1:14 PM
Message: You just made my day. I love it. Best read of the day or week maybe.


 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

Working with SQL Server data in Power BI Desktop
 What's the best way of providing self-service business intelligence (BI) to data that is held in... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.