Episodes / Episode 5 of 9

SQL Server Monitoring: What Should Wake a DBA Up

Episode 5: SQL Server Monitoring: What Should Wake a DBA Up
Episode 5SQL Server Monitoring: What Should Wake a DBA Up5:41

Monitoring is the only way out of firefighting. What a SQL Server DBA should alert on, what makes a ticket, what belongs on a dashboard, and what not to look at.

Watch on YouTubeFull course playlistSubscribe

In this episode

  • Why monitoring is the only way out of firefighting
  • Picking a tool (Idera, SolarWinds, AppDynamics, Datadog, Splunk): what you tell it to look at matters more
  • Under 24 hours: the alerts that should wake you up
  • Under a week: the ones that make a ticket; at some point: the dashboard
  • If everything is an alert, nothing is: turn off the ones that add no value

Chapters

  1. The only way out of firefighting
  2. Pick a tool
  3. What you need to look at
  4. Alerts that make a ticket
  5. Dashboard items
  6. Things not to look at

Transcript

Read the full transcript

The only way out of firefighting

Remember episode 2? Everything was on fire, and you were the guy with the bucket. Here’s the bad news. If the first person to find out your SQL Server is broken is a customer, you’re going to be carrying that bucket forever.

Monitoring is the only way out. Not more caffeine. Not working Saturdays. Monitoring. You want to find out the log drive is 90% full on Tuesday morning while you’re drinking coffee, not at 2:00 AM on Saturday when it hits 100% and the help desk is calling your cell phone.

Pick a tool

There are a lot of tools out there that will watch your SQL Servers for you. Here are the ones you’re going to run into.

Idera. The most common one I’ve seen. SolarWinds. AppDynamics. Loved by other teams, because it shows the entire app, not just SQL. Datadog. An up-and-comer, and my current favorite. Splunk. Good for security logs.

Here’s the secret the sales guys won’t tell you. The tool matters a lot less than what you tell it to look at. Every one of these tools will happily send you 400 emails a day. Brad set up 400 email alerts once. Then he made an Outlook rule that moves them all into a folder. He has never opened the folder.

If everything is an alert, nothing is an alert. So here’s what to look at, sorted by how fast it needs a human.

What you need to look at

Less than 24 hours. This one wakes you up. These are things that are causing a production outage or a work stoppage right now. If one of these fires at 2:00 AM, you get up. That’s the deal.

A database data file, filegroup, or log file at 100%. Full data file means inserts fail. Full log file means everything that writes fails. Database status that should wake you up. Primary database status suspect or recovery pending. Your database is broken. Cluster confused. If an availability group replica is stuck in resolving, that means the cluster took it down, and it needs help.

Toxic waits. THREADPOOL means SQL Server ran out of worker threads. New connections just sit there. To the users, the server looks dead, even though it’s technically on. RESOURCE_SEMAPHORE means queries are waiting in line for memory, so they can’t even start. SQL Server error log, severity 21 and up. These are the fatal ones. Twenty-two and twenty-three mean something might be corrupt. Twenty-four means hardware. You don’t sleep through hardware.

OS disk at 100%. OS CPU over 90% for five minutes. SQL Server service not running. SQL Server down is obvious. Blocking longer than 10 seconds on an OLTP server. Somebody’s app is timing out. Go find the lead blocker.

Less than a week. This one makes a ticket. Nothing is down yet. But if you ignore these for a week, one of them is going to graduate to the wake-up-the-DBA list.

A failed backup. One failed backup is a ticket. DBAs don’t get fired for failing to take backups. They get fired for not being able to restore. Failed SQL Agent jobs. These are often a canary in a coal mine. Disaster recovery or high availability posture down. Your availability group, mirroring, or log shipping is falling behind. Production works fine. Your safety net has a hole in it, and the hole gets bigger every minute.

Secondary database status Recovery Pending, Recovering, or Offline. Long running version store transaction, version store size, and tempdb contention. One transaction that’s been open since Thursday will hold the version store hostage, and tempdb grows until it eats the drive. High read latency and high write latency. This is an indication that you are about to max out the disk capabilities.

SQL Agent log, severity 2. That’s Agent for warning. SQL Server error log, severities 11 through 20. Most of what you’ll see here is failed logins. Look anyway. Sometimes it’s a misconfigured app. Sometimes it’s somebody guessing passwords. OS disk at 90%. This is the Tuesday morning coffee alert. This is the one that saves your Saturday.

At some point. This one goes on the dashboard. These are trends, not emergencies. Look at them during your 9:00 AM checklist (episode 9).

Average disk queue length per disk. Page life expectancy low. Longest running query. Oldest open transaction. Blocking longer than 5 seconds. Deadlocks. SQL Server picks a victim and kills it, so nothing is down. But somebody’s transaction got rolled back, and if it keeps happening, the developers need to hear about it.

Things not to look at

Every alert you add takes a little bit of your attention, and you only have about three hours of that a day. Spend it on the stuff that requires attention. If an alert isn’t providing any value, turn it off.