Not long ago, a crack team of SQL Server experts was flamed for a crime against database normalization they didn't commit. These men promptly escaped from the taunts of academic relational theorists. Today, they survive as website editors and software evangelists. If you have a problem, if no one else can help, and if you can find them, maybe you can hire the DBA Team.
Scene 1: The Relational Protection Agency on the Road to Hell
The highway stretches out over the desolate dry landscape. Far away, an old sedan heads slowly towards us through the heat-haze, bobbing up and down with the ruts in the road. Tumbleweed blows across the scene.
Gritty voice-over: "Good intentions. That's what they had. Yeah, good intentions and just enough knowledge to be dangerous. (Pause) But I'm getting ahead of myself. Let me take you back to the beginning, before the car chases and the explosions, before the horrific production database problems, when things were calm and cool and Joan was just an ordinary DBA working for an unremarkable mid-sized company somewhere in Middle America. She was a good DBA who tested her backups, monitored her servers, and even worked well with the development teams. She fought hard and somehow managed to maintain an atmosphere of order and sanity around her database servers. A phone call was about to change all that…"
Pan into the driver of the car. It bounces along the hot tarmac. She squints at the road, humming to herself contentedly. Her mobile rings.
"Howdy, this is Joan De Beay, your friendly local DBA."
"De Beay, this is Earl Herald over in Sales. What the heck is up with the database?"
"I'm on the road at the moment, but when I checked before I left, nothing was up. Everything was online. I've had no alerts from our monitoring system…"
It was multiple alerts from the monitoring software on the server.
"OK, there does seem to be something up Mr. Herald, I'll call you back."
Joan pulled in and heaved out her huge laptop. Time to remote in.
She leaned back on the car-seat and whistled softly through her teeth. There was a long running query on the server. It was causing all sorts of blocking, resource-use was starting to spike, and the server was in serious pain. The user running the query was… EHerald, running with sysadmin rights. She cursed silently.
She leaned back on the car-seat and whistled softly through her teeth. There was a long running query on the server.
Keep an eye on your servers wherever you are, with SQL Monitor
She called Earl Herald back.
"Hi Mr. Herald - Joan De Beay again. You seem to have a query running that's blocking a lot of other users. Can we stop that query for the moment to see what's wrong?"
"Sure, Joan, but I'll need to run it soon. I did a trial run yesterday and after a bit of fiddling it seemed to run successfully. It's the monthly sales report. We write bonus checks based on this report, including yours, and I have to run it today."
"I'm on the case already, Mr. Herald."
She checked the process again to see if it was doing writes or just reads. It seemed to be doing just reads, so she took a chance and killed the process. It closed immediately and all the blocked processes cleared. Opening the procedure that contained the query, she was not at all surprised when she had to scroll down the page… and scroll… and scroll… and oh my god, how big was this thing?
"Hey, sister!" The sudden voice jolted her. A car had freewheeled quietly next to hers. The driver looked strangely like Ming the Merciless, except for the sunglasses and blue uniform. "Need help with the motor? This road ain't no place to be stuck on". The grizzled face peered through the side-window at her with a penetrating gaze.
"No, I'm fine. I'm a DBA; just remoting in to fix a sick database. It's a problem with some SQL," she said, indicating her laptop.
"Huh, SQL eh? You may be able to help us." He flashed an official-looking ID. "Chris and I are from the RPA: Relational Protection Agency. Celko's the name, SQL's the game." He gave a smile but the eyes weren't laughing. "We're looking for a couple of desperado DBAs called Fritchey and Jones, ma'am. Call themselves the DBA Team: Goddam Newbie Denormalizers, we call 'em." Celko spat into the dust.
"No. I had no idea they were real people. Seen the irritating adverts though. You could lock 'em up just for that, I reckon; especially that shirt."
"Yeah. And for using
NULLs," said Celko's companion Chris. "Careful how you go - those two are around here somewhere. We were hot on their trail a few minutes ago, but we lost them."
"If you see them, call us," added Celko, handing her a card through the window. "Don't try messing with them. If they get their hands on an entity-relational diagramming tool, these people are dangerous. Avoid them if you value your relational integrity."
With that, Celko drove slowly off, scanning the roadside carefully.
Scene 2: The air-con repair-man visits
Joan drove her sedan into the parking lot, tyres screeching, and ran up the stairs to the IT department. Clutching a plastic cup, she sank down in front of her terminal in the server room. Although she'd cleared the immediate blocking issue, Earl Herald was a man who liked to share bad news quickly; it wouldn't be long before others in the company heard about yet another "database issue" affecting the sales report.
As if on cue, Julie Bindcross, the manager of IT, burst into the room. Julie was tall, beautiful, and quite upset.
"Joan, we have to run that report right now."
"Love to Julie, but the darn thing attacked the server like a werewolf. I barely killed it in time, before it took everything down."
"But it was running fine for the bonus calculations last month. Earl checked it yesterday. Did something change?"
"By itself? No. Did one of our users change something? Yes. I've already checked against the baselines on the monitoring system. For last month's run everything was normal; that sales report is a beast and always eats resources, but last month's run was no different than average. Today, though... CPU, disk I/O, and memory use for the same period were through the roof. I checked the date on the procedure that generates the sale report, and guess what - Earl modified the procedure late last night. But then, he always does that to feed in all the changed rules to the bonus system. He says he changed nothing else. Someone else must have done it."
"I've already checked against the baselines on the monitoring system... Last month's run was no different than average."
Is your server behaving normally? See at a glance with SQL Monitor
"Because the entire company is allowed, by corporate dictat, to access the database with sysadmin privileges, anyone could have done it. It'll take ages to track it down, but it'll be some time in the past month."
Joan sighed. This was the deep, dark secret at the heart of the company. Not only did the developers like to write reports that run under sysadmin privileges - the CEO actively encouraged everyone, the developers, the QA people, heck, even the sales team, to make database changes as they saw fit. Apparently, the modern database had to be immediately responsive to the needs of the business, and therefore as easy to update as a web page.
Of course, she was constantly fighting one monstrous problem after another due to the endless stream of uncoordinated changes to the server, none of which were put in source control.
They both fell silent, brooding on the sheer injustice of their predicament.
At that point, a slightly-disheveled bearded guy in a boiler suit, toolbox, cowboy hat and dark glasses knocked politely on the door.
"Come in. You here to fix the air-con?"
"Er, sure. Lead me to it, ma'am."
"It's that dead box over there."
"Where were we? Oh yes, how could I forget. I'm in trouble here, Julie. This thing has been changed, but without yet knowing what changed or who changed it, and why, it's not a quick fix. I just can't see how I can get this gigantic nightmare of a query tuned and running again within the hour."
"Well, it's your responsibility as DBA to get it fixed." With that, Julie flounced out of the room.
Something about that curt and unreasonable statement made Joan snap. She went to the door and yelled down the corridor, "Well it's my responsibility as DBA to remind you that allowing everyone sysadmin rights is just plain wrong!"
"You bet! Hellfire, haven't you people heard of the principle of least necessary privilege? Oops, blown my cover."
Joan stared in amazement at the air-con repair man, who was clasping his hand over his mouth guiltily. Her amazement turned to alarm as he then began pulling his boiler suit zipper down his chest to reveal a bright, loud Friday shirt. No, it couldn't be - but it was… Steve Jones. "Excuse me, but I'm Steve from the DBA Team. Good to see you, pardner," he said.
"And you, Steve. But how the heck did you get here, if you don't mind me asking?"
"In your car, Joan. I'm on the run from a mean-looking guy called Celko, from the RPA. I managed to crawl into the back while you were distracted, just before Celko and his RPA crony arrived on the scene. Hey Joan, where am I gonna run to now, where am I gonna run to now?"
"Way down to Mexico way, yeah, you could go way down south, way down south to Mexico way! Alright! Ain't no one gonna find you! But before you head off, we just have a problem you could help us fix. I'm in deep trouble with a monster reporting query, and I've got just an hour to fix it."
"Yeah, well, first things first. I escaped but Grant wasn't so lucky. The RPA arrested him and are taking him for questioning down at the RPA Detention Center. Before we fix your problem, I need you to help me go spring him."
"You're kidding. The Detention Center is twenty minutes drive away."
"Nah. Just bring your laptop. We can remote in, check the monitoring system and discuss it on the way."
"Makes sense to me. Let's go get him." With that Joan reached under her desk and pulled out an enormous laptop. "I call this baby 'Abomination'. She has 32 GB of RAM, 18 inch screen, three SSDs, and is all tooled up: the whole damn SQL Toolbelt."
"Does it come with a chiropractor too? I'd rather muck out the stables three times a day than carry that thing around."
"She's a beast, ain't she?"
Steve ran his hands over the huge machine, thoughtfully. "Some of the old time DBAs never even carried a laptop, y'know. A lotta folks find that hard to believe. Jim Scarborough'd never carry one - that's the younger Jim. Gaston Borkins wouldn't take one up in Comanche County…"
Steve broke off from his reverie. "OK, fine, but if that thing breaks my horse's back, you'll be sorry."
"Horse? That hasn't even got a USB port. We'll borrow Julie's jeep. That's got a mains power socket to keep this baby refreshed."
"We don't have the best luck with jeeps or trucks. I'm just saying."
"It'll be fine."
Scene 3: NoSQL or…
Grant stretched his legs moodily as he climbed out the car. He stood with his hands cuffed in front of him, surrounded by a group of goons, all dressed in crumpled black suits and dark glasses. They motioned him towards a concrete block of a building, with bars across most of the windows.
"You guys are making a huge mistake. I never would have denormalized a database like that. It was a setup."
"I didn't believe that story the first time you told it, and I don't believe it now."
Grant immediately recognized the mild-sounding voice of the head of the RPA. He spun around to watch him approach. The bald head. The narrow, spade-like goatee.
"PhoneNumber1, PhoneNumber2, PhoneNumber3, and if all that wasn't bad enough, an artificial key: a surrogate." He said the word slowly, emphasizing every syllable. The heavies winced and shook their heads in disbelief. "Oh yes, Mr. Fritchey. Newbie stuff. You have much to answer for. Every true-hearted database developer knows that the value of each attribute contains only a single value from the domain."
"I never did that. And besides, there's nothing wrong with artificial keys as long as you enforce the natural constraint as well, and… hey, why am I defending this? I didn't do it. I follow the rules!"
"If the rules you followed brought you to this, of what use were the rules, Mr. Fritchey? You'll be doing lessons on normalization from my books for a very long time before we let you anywhere near a database again."
Suddenly they all looked round. What had started as the distant sound of a car had become a mighty engine roar. A jeep flew over the brow of the hill, landed with a crashing thud, and spun through 180 degrees right in front of them. The RPA thugs dived to avoid being hit.
"Howdy, Grant!" yelled Steve. "Care for a ride out of here?"
Grant didn't hesitate a second. He leapt into the jeep, and yelled in agony as he bounced off the laptop propped up on the back seat.
"What the heck is this? Since when do you put lead ballast in jeeps? Let's go."
The jeep tore away at high speed, but the RPA thugs were back on their feet, spitting dust and yanking out pump-action rifles. As the bullets began to fly, Grant grabbed the only thing to hand that might act as a shield. The bullets bounced off the hardened case of the Abomination laptop.
"OK, maybe I want one of these."
Joan violently threw the jeep over the dirt tracks, and began ascending the mountain pass, two large black SUVs barreling up behind in hot pursuit. Grant made to throw Abomination into the path one of the trucks.
"Oh no you don't! You throw that out of the jeep, and you're going to follow it!" yelled Joan, with steel in her voice.
"Whoah, easy there," shouted Grant, dropping Abomination like a hot brick. "It's just that it worked last time, and how else are we going to make their trucks explode?"
Steve silently regarded his beloved cowboy hat, as if with deep regret. Suddenly he spun and threw it out the back of the jeep. It landed smack on the windshield of the nearest SUV, right on their tail.
Blinded, the driver swerved wildly to the right, struck the other SUV, and both trucks careered over the edge of the pass. Joan slammed on the brakes as they listened to their crashing descent of the SUVs, followed by an explosion and a giant fireball of flames.
"Nice work, Steve!" Joan yelled enthusiastically, as they stared down at burning wreckage. Steve looked crestfallen.
"My hat went down with those trucks."
"That was a big sacrifice you made there Steve, pardner, and we won't forget it," Grant consoled him. "But at least your hat didn't die in vain. We're free forever from the clutches of Celko and the RPA!"
"Yeah, I guess, but… I loved that hat. Had that hat since I was knee high to a mule," replied Steve sulkily, kicking the dust.
Joan put a consoling arm over his shoulder and led him back to the jeep. "OK, well, never mind old buddy. We'll buy you an ice cream on the way back."
Scene 4: Fighting monsters
Back at the office, Joan quickly got Grant up to speed on the situation.
"No deployment process at all? Everyone has sysadmin privileges…? Getting angry…" Fortunately, at that moment, Steve brushed past and his shirt distracted Grant for long enough to defuse the impending rant.
"Fine. Whatever. Well, we have two problems. First, we have to figure out what's happened to this query so we can get it running today. Second, we have to find out who made the change."
Steve jumped in, "Well, you have backups right? And you also have a schema comparison tool, right? SQL Compare? Nice. Then we can compare the current procedure directly to the one in the backup."
Joan started nodding and jumped immediately to action. "With just the stuff that's changed, we don't have to try to troubleshoot and rewrite the entire procedure."
"OK," said Steve. "But how do we work out who made the change? Hmm. As long as
Default Trace is enabled, we can get enough from the
Object Altered event…"
Joan shook her head.
Grant snapped his fingers. "Got it! We can take a look at the system health extended events. This query was running long yesterday too, right? I'd be willing to bet that if was causing blocking and if we find out who was being blocked, we'll find out who likely went in and 'fixed' the sales report."
Grant started typing.
"SQL Compare? Nice. Then we can compare the current procedure directly to the one in the backup."
Compare schemas straight from your backups with SQL Compare Pro
"You know," said Steve, "you'll be plagued with problems like this forever until you convince these people that you need to lock down the servers and put some proper change processes in place."
"Let me tell you, there is no way you're taking away our production privileges." They spun around to see Earl Herald standing at the door. "We need constant access to the database. How else can we get things done that the development teams and DBAs are too slow to fix? Speaking of which, where's my sales report?!"
Joan sighed. "We're just figuring out what went wrong now, Mr. Herald…" She scanned the comparison result displayed on Abomination, which if anything seemed to be running even faster now it had some bullet holes.
"Hey, lookie here," interrupted Grant. "Here's a blocked session from last night, just before the change to the sales procedure. Lock waits exceeded 30 seconds. We have the call stack, session identifier and T-SQL, all stored right there. Blocked session belongs to one EHerald…"
Joan shook her head in disbelief. "And look - later that night, 'someone' added a bunch of query hints to the sales report procedure…"
At the mention of a query hint, Grant shot to his feet and advanced menacingly towards Earl. "So you were blocked by your own crummy report, tried to fix it with hints, made it worse, and then tried to pin the whole problem on the DBA?! That makes me so... angry!"
"Hold on there, pardner," Steve intercepted Grant. "This guy may be dumber than a Missouri mule, but he didn't mean nothing by it. Besides, we don't hurt the nice clients, right?"
"Right, right. Not supposed to break the clients again. I remember," mumbled Grant. "But he put query hints everywhere. It's like a rash in there!"
Earl, sweating profusely, stuttered, "Hey now, Steve's right. I didn't do it on purpose. I was trying to enter some new customer information but that darned sales report was running a long time and blocking my updates. Back when I was a programmer we got some queries to run faster by using a query hint called
FAST 1. I just put it into the queries. I thought it would help performance!"
Joan glanced back at the SQL Compare report. "It's not a problem. We can put yesterday's code back in place. We should be able to run the query then. It'll still be slow, but it should finish today."
Scene 5: DBA Team to the rescue
Everyone is gathered around a table in a meeting room. The DBA Team and Joan are at one end, with several department heads from the company scattered around the rest of the area. Earl Herald is also there. Steve is speaking.
"…and while his intentions were absolutely good, the lack of adequate knowledge about just how difficult these systems can be to work with led him to make a poor choice on the query. That's what caused it to effectively stop working. We've got it back online now."
Grant chimed in, "And we've identified several tuning opportunities that we're going to explore. We're going to take our time though so that we don't accidently introduce another problem, or worse yet, affect the data coming out of the query."
(cue 'sincerity' music in the background)
"But," Steve spoke up, "that's not the main problem here. This type of query problem is something we can track down and fix reasonably easily once we understand what went wrong. But the real issue is that your DBA team, despite setting up good backups, backup testing, monitoring and all the rest of the best practices, are being undermined by your own organization. Again, we're not suggesting ill-intent, but the best of intentions don't make up for the fact that allowing everyone sysadmin access to the server means that they will always be completely exposed to these kinds of problems."
"And we would have worked on tuning those reporting queries earlier, but we're spending most of every day trying to figure out what got broke, and what broke it. Fixing the code and structures and data several times a day is just slowing us down," added Joan.
"So here's what we're proposing. Let's treat your databases the same way you treat your code. You're using source control and several automation techniques so that you know what application code is going out the door. We can do the same thing with the database, but we have to take away the sysadmin privileges or it's all a waste of time."
Earl Herald, having looked extremely uncomfortable throughout the proceedings suddenly spoke up, "Now you're talking about slowing down database changes and cutting off our ability to query the data directly. Things are going to be worse for the business. SQL was designed as an end-user query language. We've got to be able to maintain our own queries, especially in the case of the bonus system which always ends up so complicated that only the Sales Manager understands it!"
Grant responded, "I think you'll find that putting proper change control procedures in place, as well as smoothing out deployments will actually make them faster. Slow is smooth and smooth is fast."
Everyone in the room cocked their heads to one side trying to figure that one out.
Steve broke in, "And that's why we'll set up a dedicated reporting server that allows read access to the data for anyone who needs it. You'll still be able to run queries, but it won't be against the production system. We can even give you sufficient rights in your own default schema to create, maintain and run your own procedures or tables, without any danger of inadvertently damaging the system itself."
This brought a smile to Earl's face which opened up the rest of the room to smiles, backslaps, and handshakes.
(cue more 'sincerity' music)
Scene 6: Some monsters don't die
In the desert, on a lonely road at the foot of a cliff, a hand suddenly shoots up from a crater in the ground. A figure emerges slowly, a face much like Ming the Merciless, dirty, smoky, sweating, but very much alive, and clutching a book in one hand, and a battered and smoldering cowboy hat in the other. He dons the cowboy hat, looks up at the cliff face, and raises the RPA handbook in his right hand. His companion, Chris, stumbles out of the crater and dusts himself down.
"I'm not done with you yet, DBA Team! When you mess with relational integrity, there are no clean getaways!"
"Or for anyone who introduces
NULLs into relational logic!"
"Aw hell, Chris.
NULLs are OK, you know that.
NULL marks values which are missing because the value is unknown."
"Don't be ridiculous, Joe. You know that a type that contains a
NULL can't be a type, because
NULL isn't a value - it's a marker."
"Cut it Chris. I'm hot and I need a beer."
"..and so logically, a relation that contains a
NULL isn't a relation."
"SQL allows you to cast
NULL as a type with
'CAST (NULL AS <data type>)', so problem solved."
"It's still a
NULLs have no place in the relational model."
"Oh, shut the functional dependency up, Chris. Don't you ever give up?"
"You and your blooming SQL! It's a deeply flawed attempt at a relational query language. You should be concentrating on that, rather than worrying about trivia like surrogate keys."
Joe took an irritated swing at Chris that, more by accident than intent, connected square on his jaw. Chris stood motionless in surprise for a moment before plunging forward in fury, and grasped Joe by the throat. In a few seconds, the two senior officials of the Relational Protection Agency were rolling in the dust, flailing their fists.
Whist distracted by their effort to resolve their academic differences on the place of
NULLs in relational theory, the RPA executives are in no position to pursue the two fugitives as they set off across the border to Mexico, leaving behind a successful run of the bonus report and subsequent peace, goodwill, and understanding within the IT department.
(end lyrics as the credits roll to the music of 'Hey Joe')
Hey Joan, where you goin' with that laptop in your hand?
Hey Joan, I said where you goin' with that laptop in your hand?
Alright, I'm goin' down to kill that old SPID,
You know I caught it doing table scans yeah I did.
Yeah, I'm goin' down to to kill that old SPID,
You know I caught it messin' up concurrency so its time it was undid.
Huh! And that ain't too cool.
Do you want to always know what's going on with your SQL Server?
SQL Monitor offers performance monitoring and alerting through a web-based UI, so you can keep track of SQL Server wherever you are.
It shows you server performance in real time, so you can spot problems before anyone else notices. And you have the data you need to fix the problem right there in the tool.
SQL Monitor is simple to install, and gives you useful information on Day One. Try it now and see for yourself:
Free eBook: SQL Server Transaction Log Management
As a DBA, your reputation rests on how you react when SQL Server goes wrong. Often, the key to fixing a SQL Server crisis – and fixing it fast – is to understand the transaction log. In this free 208-page eBook, Tony Davis & MVP Gail Shaw give you the background you need to fix a full log, truncation, backup problems and more. Get your free copy now:
Plus, receive emails about new episodes of the DBA Team and other helpful DBA updates.