Episodes / Episode 2 of 9

SQL Server Firefighting: Prove It’s the Database First

Episode 2: SQL Server Firefighting: Prove It's the Database First
Episode 2SQL Server Firefighting: Prove It’s the Database First6:57

Something is broken, the bridge call has fourteen people on it, and everybody is looking at you. How a SQL Server DBA handles production incidents and on-call without making the fire worse.

Watch on YouTubeFull course playlistSubscribe

In this episode

  • Are you sure it’s the data tier? Prove it before you touch anything
  • Why the most common correct action for the DBA is to do nothing
  • When it’s OK to contact a DBA after hours, and how
  • On-call rotations (at least two DBAs) and release schedules
  • Your first fire: full disks, a log that won’t clear (log_reuse_wait_desc), and troubleshooting

Chapters

  1. Intro
  2. Are we sure it’s the data tier?
  3. On-call procedures
  4. Appropriate contacting methods
  5. On-call scheduling
  6. What after-hours work is scheduled?
  7. Your first fire
  8. Troubleshooting

Transcript

Read the full transcript

Something is broken. The bridge call has fourteen people on it. Everybody is looking at you. Welcome to firefighting.

Are we sure it’s the data tier?

When an app gets slow, the database gets blamed first. Every time. The app throws “database timeout,” and that’s all anybody reads.

Before you touch anything, prove it’s the database. Is SQL Server’s CPU busy? Is anything blocking? Are queries waiting on something? If SQL Server is sitting there bored and the app is still slow, it’s not your fire. Say so, show them the numbers, and let the app team keep looking.

“You got time to lean, you got time to clean” is a bad mindset. That’s what the manager at your first fast food job said. It’s great advice for a fryer. It’s terrible advice for production DBAs.

In the middle of an incident, “I’ve got a minute, let me clean up these indexes” is how you start a second fire while the first one is still burning. If you’re not sure a change fixes the problem, don’t make it. And never change two things at once, because when it gets better, you won’t know which one did it. When it gets worse, you won’t know what change caused the degradation.

The most common correct action for the DBA is to do nothing. I mean it. Most fires on the data tier are a symptom of something upstream: a release, a batch job, a user doing something creative. If you start killing sessions and flipping settings, you might make the graph look better for a minute, and you’ll destroy the evidence of what really happened. Watch. Gather. Then act, if you still need to.

On-call procedures

When is it OK to contact a DBA after hours? Two reasons. That’s it.

Significant production impact. Significant work stoppage.

Everything else is a ticket for the morning. “The report I run on Mondays is slow” is not a page at 11:00 PM on a Saturday. Write these two rules down, get your boss to sign off on them, and send them to everyone who can page you. If you don’t, you will get called for everything.

Pick one way to wake up a DBA. A page, a phone call, an on-call app. Not email. Not a Teams message at 2:00 AM that you’ll read at 8:00. If it’s important enough to wake someone up, it’s important enough to call. Tools like incident.io and Everbridge are common. They’ll text you, email you, and dial your cell phone until they track you down.

How many DBAs? You need at least two people on a rotation. That puts each of you on call 50% of the time. You’re on call every other week for the rest of your life. That is almost a hostage situation. With fewer than two, you’re on call more than 50% of the time, and that’s not sustainable. It ends in episode 9.

Release schedules. Know when releases happen. Most after-hours fires aren’t born. They’re released. If there’s a release Thursday night, the 2:00 AM Friday page is probably the release. Make an attempt to align those schedules so that you can work them all in a single night, and not have to be up multiple nights supporting different teams.

Your first fire

Low disk space.

The database isn’t set to auto grow. The disk has plenty of room, but the file isn’t allowed to use it. The data file fills up and inserts fail. The log file fills up and everything that writes fails. Add space, or turn auto growth back on.

If it’s the log, don’t shrink it and call it fixed. Microsoft says it in plain English: shrinking a log file alone can’t solve the problem of a full log file. You need to find out why the log can’t clear. SQL Server will tell you. Look at log_reuse_wait_desc in sys.databases. Nine times out of ten it’s a log backup that isn’t running, or a transaction that’s been open since lunch.

Another common cause is a long running query that’s blocking, or eating the server.

Find the head blocker. Use sp_WhoIsActive to identify this.

Before you kill it, know this. KILL doesn’t make the query disappear. It rolls it back, and rolling back a long transaction takes a long time. A query that’s been running for two hours isn’t going to roll back in two seconds. Ask the owner first, if you can find them.

A lot of small queries all getting slow at once.

That’s usually a bad query plan. One query got a bad plan, and it runs a thousand times a minute. Query Store shows you when the plan changed, and it lets you force the old plan, the good one. In SQL Server 2022, Query Store is on by default for new databases. On older versions, or on databases that were upgraded, turn it on now, before you need it. Force the good plan to stop the bleeding. Fix the query later, during business hours, with the developers.

A bad release.

If the fire started at the same time as a release, the fastest fix is to roll back the release. Not to tune your way around it at 3:00 AM.

User error.

Somebody ran an UPDATE without a WHERE clause. Do not restore over production. Restore a copy of the database to a point in time right before the mistake, give it a different name, and copy the rows back. Episode 6 covers how to make sure you can do this.

Get your tools installed before the fire, not during it.

Brent Ozar’s First Responder Kit. It’s free and open source. sp_Blitz gives you a health check. sp_BlitzFirst tells you what’s happening on the server right now. Adam Machanic’s sp_WhoIsActive. Who’s running what, who’s blocking whom.

Put them on every server, the same way, every time. That’s standardization, from episode 8.

Then, every fire, the same order:

Is it the database? What changed? What is SQL Server waiting on? What’s the smallest change that stops the bleeding? Make sure you have a plan to undo anything you do… in case your do turns into doodoo. Write down what happened, while you still remember.

And don’t forget, the answer to number 4 is usually nothing.

I’ve had someone from the virtual team vMotion my SQL Server over to another ESXi host that was overloaded. There was nothing I could do to fix it on the database, other than have the virtual team vMotion it back.