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: Blowin' in the Wind
Joe DBA bid his wife a faux-cheery farewell and stumbled out onto the streets of Seattle, the wind whipping at his coat tails. It was 7AM and he'd barely slept all night.
At 2.30AM he'd been staring stared bleary eyed at his laptop screen, under the harsh glare of the kitchen light. He knew he shouldn't have been remoting into his database server at that hour of the morning but what else could he do? He'd already spent sleepless hours, anxiously fretting over whether the SQL Server service would just vanish from the production server again, as it had done, randomly, a few nights before. At first all had seemed well. He'd logged into the server, quickly listed out the active services, and there was MSSQL, happily ticking along. Just past 3AM he'd finished his coffee and was about to slip back into bed when something made him hit refresh on the services list. Boom! The MSSQL service had blown again. Quickly, over to SSMS on the remote server to log into SQL Server. Nothing.
He managed to get SQL Server back on its feet, hopefully well before anyone else noticed its temporary absence, but he knew he couldn't endure too many more nights like the last. He needed help tracking down this bug, and now he had a plan to get it. The PASS Summit was in town all week, literally crammed full of DBAs, and rumors that the DBA Team were going to put in an appearance. His colleagues at work had even joked about how they maybe ought to steer clear of downtown for a few days as there were bound to be a few dangerous high speed car chases...
Joe chuckled to himself. It sounded crazy, but here he was. It was the only plan he had.
Scene 2: The Sound of... Music?
On his way in, Joe took the back route up the registration floor, twisting through lesser-known corridors before arriving at a deserted escalator. With one foot onboard he was suddenly distracted by the faint but definite sound of singing – well, sort of singing – coming from a room down a corridor to his right. He stepped back off.
Scene 3: Just Regular DBAs
The registration area of the Convention Center was abuzz with shouts of surprise and joy as people spotted old friends. There was much shaking of hands, slapping of backs, and not a few manly hugs.
"Stop staring, Chis," muttered Celko. "Relax. Try to blend in. As far as anyone is concerned, we're just regular DBAs enjoying the summit. Nobody must know we're here on business with the Relational Protection Agency..."
"Yeah, well, if we're trying to blend in," retorted Chris, "maybe you shouldn't have accused that experienced DBA of being a 'SQL Newbie' for admitting to using a cursor in one of his maintenance routines!"
Celko sighed wearily. "These people need to learn to think in sets. Always! Otherwise, it's a slippery slope. First a small transgression, next a full blown crime against database normalization."
"Speaking of which, where the heck are the DBA Team? Our sources said they would definitely be here."
Just then Tom LaRock, President of PASS, swung by. "Hey, Celko, Chris, good to see you both! Come to learn a thing or two about T-SQL, eh?" he smiled.
"Ha ha, quite!" shouted Joe with mock gaiety before snarling to Chris, "There's another one to keep our eyes on – I hear he regularly uses the catalog views instead of the ANSI Standard Information Schema... Follow him. I've always suspected he was too pally with the DBA Team. He might just lead us to them."
Scene 4: Reluctant Rehearsal
Deep in the bowels of the Convention Centre, Phil Factor stood waving sheets of lyrics enthusiastically in front of Grant Fritchey and Steve Jones, a.k.a. the DBA Team, who currently looked like large, incredibly reluctant schoolboys.
"Now, come on Grant, Steve... Let's go again! I know you can do better. We need these two songs ready for the PASS Karaoke Party tomorrow evening, and we have so little rehearsal time, honestly I despair!"
"Get me out of this. Please. I'm begging you!" Grant whispered to Steve, frantic.
"Now, Grant," shouted Phil, oblivious. "This time when you do the spoken intro to the DBCC song, I want you to step outside yourself, and let your mind go to a dark and despairing place."
"You mean like LA-X?"
"No! More despairing even than that... Listen to me..."
[in a deep recitation]
"What can this awful error be?
Severity level 23!
There must be something, hopefully,
that I can do immediately
In order to determine the
Extent of the severity...
"Then cue the music!" With a click, the familiar tune of YMCA filled the room and Phil burst into song.
"Young man, when a database dies, or your log file grows a terrible size,
Or your backup gives a nasty surprise; there's no need to be unhappy!
"There is, a command you can use, when event logs give you terrible news,
When your code's slow, whatever you choose there's a way back to the good times.
"Just key the letters in D-B-C-C! Just key the letters in D-B-C-C!
It has everything for the stressed DBA, with an option to save the day...!"
Phil broke off in mid-song and they all swung round as the door crashed noisily behind them. Phil clicked off the music. Joe DBA stood staring at them in open-mouthed disbelief.
"Ah, yes!" shouted Steve suddenly, grabbing Grant. "It's... What's-his-name... You know, Grant, about the... the thing!"
"Of course!" cried Grant. "The important... thing. Sorry Phil, got to take a break for five minutes here!"
Grant and Steve scrambled out of the room with Joe DBA in tow.
Scene 5: The Price of Freedom
"Thank you, thank you, thank you!" gushed Grant as they made a swift exit. "Now wait a second... Haven't we met someplace before?"
"Joe!" cried Steve in sudden recognition. "Long time no see, pardner. How's tricks with ol' Eric 'Bloodaxe' Pedersen?"
Joe was still dumbfounded. "I know you guys get up to some crazy stunts, but this..."
"Yeah, well, we're not exactly willing participants," grumbled Grant. "Steve got us into this after losing a bet with Phil on a ferret race."
Grant paused as Tom LaRock approached.
"Hey guys!" beamed Tom amiably. "I heard a whisper about some strange rehearsals going on down here, and I just couldn't resist swinging by."
"Sorry, Tom. Rehearsals are finished for today."
They were interrupted again by the sound of voices approaching from round the corner.
"Come on, Chris, faster! We'll lose him!"
"It's not my fault, Celko. This place is a warren."
"It can't be them, can it?" hissed Steve in a shocked whisper.
"It is – the Relational Protection Agency! Quick, back in here, before they spot us!" Grant pulled frantically at the door, only to be greeted by a blast of cheesy music and Phil singing along enthusiastically:
"Young man, are you listening to me? I said, young man, just run DBCC!
You can clear cache or can run CheckDB, but you got to know this one thing!"
Grant slammed the door shut again. "OK, not back in there."
"Steve Jones, Grant Fritchey – I arrest you both under article five of the Referential Integrity Act: Incitement to Denormalize!" cried Celko.
"Quick, this way!" cried Joe DBA, seizing his moment and leaving Tom staring in bemusement as they raced off. "I can get you out the way I came in... But I'm afraid this escape route comes at a cost."
"Cost?" gasped Steve as they sprinted down the escalator just ahead of Celko and Chris.
"I've got an unpleasant problem with a production server and I need your help. I work just a few blocks away, at a big firm of solicitors."
"Lead the way!"
Scene 6: A Spot of Monitoring
"OK. So do I understand this right?" Steve stared thoughtfully out of the IT Department window in Joe's impressive office building. "The production system tested out fine in integration test and load test. You simulated a day's trading without any anomalies. Now, just randomly, when it's in service you're getting a problem where SQL crashes so badly that it leaves no error and just quits. It's happened a few times before, always around 3AM. You've been able to restart the service just fine with just a brief rollback/roll-forward but there are no indications in the error log of what caused the outage."
"None," confirmed Joe. "And I did the obvious things, like checking out the default trace, but it's hard to think of any normal SQL Server event that would cause this sort of exception."
"Nah, it's a bug. Unsurprising as you're still using SQL Server 2005. Why not upgrade?"
"We should have. There were some incompatibilities when we attempted it, and no dev time to see what they were, so we left it where it was. It's just been kept up-to-date with the business changes."
"Does it go pop every morning around the same time?"
"No, but it's always in the early hours. It tends to be when we've done rather a lot of trading the previous day, almost as if it was dying of exhaustion, but I can't pinpoint the problem to any one business process."
Grant was flicking through the source code, occasionally giving a low, pained whistle. "Well, this is definitely a bug, but this code does several things that would make any MVP weep. We can start pulling suspicious stuff out by the handful, but each time we'd have to go through the deployment process, and there would still be no guarantee we'd get it. It'd take weeks."
"Did you ever get that server monitoring set up after last time, Joe?" asked Steve.
"Well, I set up a few alerts, but..." Joe trailed off.
Steve and Grant looked at each other. "Aw, Joe... Didn't you learn anything? You've got to set up some proper SQL Server monitoring. Take baselines. Add custom metrics for the start and end of each business process and all report-generation. Let it fester for a week, or until it goes pop again."
"Let it fester?" cried Joe. "Hey, now that's not how this works. I know you guys. You go klop-klop-klop at the keyboard and jabber about locks, latches and whatnot. Then you manage to fix the problem just before you get into a dramatic car-chase. Can't you fix it right now, just like last time?"
"Well, I set up a few alerts, but..." Joe trailed off.
Get useful information on Day One with SQL Monitor
"Nope. Most database problems are reproducible. If it happens randomly, there's no way of repeating it under test. You've got to monitor, get baselines, capture the business process events as well as the database events, and hopefully catch the critter when it happens. Then we'll know the conditions at the point where it all goes wrong."
Joe sighed. "But how long will it take to set all this up?"
"Don't worry, Joe. We'll set things up for you right now, custom metrics and all. We can get baselines and capture business process activity as well as what's happening to CPU, memory, and I/O, so we get a complete picture of what's going on here."
Two cups of coffee later they were done.
"Well so long for now, buddy," said Steve. "It won't be long before Celko and the RPA goons get wind of where we are. Contact us when it next goes pop."
"Going back to rehearsals?" smirked Joe.
Grant went pale. "I'm never going back in that room."
Scene 7: No X-ray vision required
"Come along Chris, we haven't got much time!" shouted Celko impatiently, as they pounded along the streets of downtown Seattle. "One of our tuple goons spotted them heading into a solicitor's office up this way."
"Sorry, Celko. Man, these hills. I knew I should have signed up for #SQLLongRun, got myself a bit fitter."
"Wait – there! That's Steve Jones!" shouted Joe, yanking Chris forward by the arm.
"Where!?" squinted Chris.
"8 blocks east!"
"How on earth can you tell from this distance?"
"Are you kidding? I'd spot one of Steve's shirts through concrete! Come on!"
Scene 8: Duck!
Steve and Grant sauntered gently away from Joe's office.
"Let's head back to the hotel and get in one last practice of our keynote speech for SQL in the City tomorrow."
"I'm right with you bud-- man, these guys just never let up!" cursed Grant loudly at the sight of Celko and Chris heading straight for them.
"Quick, over here!" Steve pulled Grant towards a large white truck parked across the street. Steve jumped into the vacant driver's seat and they pulled away, leaving a line of disgruntled shouting tourists.
"Are you freakin' kidding me?!" cried Grant "We're going to escape in a Seattle Duck Tour truck?"
"Got a better idea?"
The vehicle was empty except for a solitary tour guide quivering in the back, clutching her microphone. They lumbered round the corner, Celko and Chris racing after them.
With a crackle, the microphone burst into life in the rear of the truck. "And on our right we have Seattle's most famous landmark, the Space Needle, built in 1962 for the World's Fair..."
Steve shot a glance at the guide and she stopped dead. "Sorry, force of habit."
"Hey, cool!" shouted Grant enthusiastically. "Can we stop for a photo?"
"'N'other time, pardner. This is supposed to be the high speed chase bit, y'know?"
"Right, gotcha. Carry on, then," said Grant, serenely. "Although, you know, they are gaining on us. On foot."
"Well, this thing ain't exactly built for speed. Hold on, though, this might help!"
They careered wildly down the steep descent of Eagle Street, towards the bay.
"Woo hoo! This is fun!" shouted the guide, wind whistling through her hair. "We're never allowed to go this fast!"
They ploughed through some trees and into Sculpture Park. Ker-rash!
"Oops, there goes the Eagle statue. How ironic."
"Meh, didn't look much like an eagle anyway," shrugged the guide. "Faster!"
"Coming through!" yelled Grant as they veered recklessly across the freeway. "OK, you can stop now Steve, we're heading straight into the Sound... Steve? You can stop now, Steve. We're going in! Duuuuuck!"
The truck hung suspended above the water for a fraction of a second, before dropping with a massive splash into the Puget Sound... and then drifted serenely out into the bay.
Grant unfurled himself from his brace position, looking sheepish. "Oh, right. I forgot these things float."
As they gazed back towards the coastline, Steve raised his hat in a mock salute to Celko, who was waving his gold RPA badge in frustration from the shore.
Scene 9: SQL in Seattle
Joe DBA ran towards the exhibition hall, a fantastic architectural blur of concrete and glass, made slightly more human by the red SQL in the City posters. He waved his entrance badge at the door and looked around frantically for Steve and Grant, spotting them deep in discussion at the side of the hall.
"Steve, Grant... Last night... It blew again!" he struggled out, breathlessly.
Steve and Grant exchanged glances. "We only have a few minutes before we start our keynote, but let's see what we can do."
Steve pulled out his iPad. "We can log in and access the monitoring data right now."
"OK, we can see precisely where the service disappears. Hmm... It looks pretty quiet in the lead up to the crash... Wait, look at that! There are several signs of memory stress building up over a period, it looks like about ten minutes, leading up to the service going down. It's weird since there are few other signs of activity, but this custom metric tells us that the weekly management reports were being calculated. Hmm... Plenty of aggregation queries running, but no real reason to expect that sort of memory stress."
"Was there more trading than usual last week?" asked Grant.
"There was one, a quite hefty aging debtor report..." Joe paused, noticing their blank stares. "Well, all the attempts to get the debtors to settle their accounts go into a report, which runs at the end of a chain of reports within a scheduled job."
Steve pulled out his iPad. "We can log in and access the monitoring data right now."
Keep an eye on your servers wherever you are with SQL Monitor
Grant was interested. "Let's have a look at the stored procedure." A moment later he gave an involuntary gasp.
Joe coughed. "You can see why they were loath to upgrade. This procedure just grew and grew as more and more managers wanted reports and they were all slightly different."
"... and they were sent by email?"
"What form is that report being sent in?"
"As an attachment: HTML, but with an Excel file type."
"Was the aging debtor report one of the last? Should be somewhere near the end here... Hang on..."
There was a stunned silence; then "
spMakeWebTask? Nooooo! They were writing out their reports as HTML tables with
Grant leapt to his feet. "Don't... make... me... ANGRY!" He flushed red, and clutched his temples.
Steve spoke up hurriedly. "Hey, good buddy –
spMakeWebTask was deprecated for good reason: it had a memory leak. If the HTML document it created reached a critical size, it bought down the server. Your debtors report was sufficiently large this week that the server couldn't recover from the memory stress. Nick it out and do the report a different way. It's just a big HTML table. Maybe use an XML query to produce an XHTML fragment that's then saved to disk."
"So that's it? I just replace that one procedure and all this pain is over? Presumably that means I can pull out the monitor too?"
"Well, pull out all usage of
spMakeWebTask, but I'd leave monitoring in place. We've found one good reason for the crash, but who knows? If it happens again, you're now in a very good position to do quick forensic work with very little guessing."
"Gotcha. Thanks guys, I owe you one!"
"No problem. Right, just in time – we've got to head in and give our Keynote."
They shook hands. "Keep monitoring!" called Grant as Joe departed.
Scene 10: Trouble at Reception
"Celko's the name, ma'am." He flashed his gold RPA badge. "My colleague and I are charged with the maintenance of Relational Purity in databases and it is our duty as federal agents to detain the DBA Team. We know they're here at SQL in The City – Grant and Steve are speaking, after all."
"I'm sorry, but unless you have a proper attendee badge, I can't let you in," replied Annabel, unmoved.
"We don't have time for this – we need to get in there right now!" cried Joe, snatching two spare lanyards from the table and dashing towards the Keynote hall just as attendees started to emerge.
Unperturbed, Annabel reached for her special microphone and her voice boomed out across the foyer.
"Annabel here, everyone! It sounds like you all enjoyed the Keynote, and now Joe Celko, the famous SQL expert has just arrived to take any questions you have on maintaining hierarchies in nested sets using the adjacency list model!"
There was a scrabbling sound and DBAs appeared from everywhere. In a flash, the two RPA men were lost in a crowd of enquirers.
"Excellent!" Annabel smiled to a colleague in a Redgate shirt. "Quick, get in there and let Steve and Grant know they've got to leave the back way. The RPA is onto them."
She settled back calmly into her chair. It was all part of the job.
Scene 11: The Show Must Go On
Much later that night, Steve, Grant, and Joe staggered slightly, as they travelled with a group of DBAs between the bars of Seattle.
"Man, what a day!"
"Don't worry. You're in safe hands now," cried Denny Cherry, who was in high spirits. "Just one more bar to go... and you guys are gonna LOVE this place!"
They entered a vast, sweaty, crowded tavern and Steve glanced around nervously. "Wait, I know this place..."
"Of course!" shouted Denny. "It's the SQL Karaoke Bar. Where else?"
"WHAT?! But that means..."
Too late, they saw Phil Factor making a determined beeline for them.
"Grant, Steve – thank goodness you made it! I was getting worried. You missed the DBCC number. It went down a storm! Luckily, you're just in time for 'It's Blown Again'."
He ushered all three of them up onto the stage.
"Steve!" wailed Grant. "This is all your fault. Get us out of this!"
"I can't, but maybe our old friends over there can," replied Steve, drolly indicating Celko and Chris approaching the stage, menace in their eyes. "Persistent little varmints, ain't they? I'm kind of warming to them."
"No contest. Hey Celko!" cried Grant. "No way out for us this time. We'll come quietly!"
Celko and Chris climbed onto stage, gold RPA badge flashing in the stage spotlights. Steve threw his microphone impulsively at Joe, who caught it and stopped dead, stunned, as the crowd started to cheer wildly.
The familiar tune of "It's Raining Men" burst forth, and Phil started singing with gusto.
"Hi hi! We're the DBA Team - uh-huh -
And have we got news for you! You better listen:
Get ready, all you DBAs,
'Cause you're on standby tonight – alright!
"CPU is rising, disk space getting low,
According to all sources, the database will blow!"
Celko, Chris, and Joe found themselves joining in:
"Cause tonight when you get home,
Just about half-past ten,
Just about when you begin your meal,
The phone will ring again:
It's blown again! Hallelujah! It's blown again! Amen!
"You're gonna run back to the server room
'Cause that server's done gone boom!
"It's blown again! Hallelujah!
It's blown again! Comprehensively!
Don't remote, you'll never win,
It's gone so bad you can't log in..."
Meanwhile, not for the first time that day, Steve and Grant had slipped quietly to the back door. They glanced back. The speakers boomed, Phil, Joe and the RPA men waving their arms wildly, while smoke and red strobe lights gave the impression of the stage burning in a hellish inferno.
"They seem to be having fun, but it's getting a little too warm in here for me. Let's leave them to it."
"Amen, pardner. I love it when a plan comes together."
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: