firefighting Archives - John McCormack Engineering Manager

Red button with the word HELP in bold capital letters — Image by OpenClipart-Vectors from Pixabay

I mean this sincerely when I say, you have got to get your alerting under control. Excessive alerting is a sign either of your service being out of control, or your alerts being misconfigured. Alert fatigue reduces reliability and destroys morale of on-call engineers. Here is why.

Why over alerting is an enemy of SRE

It reduces reliability

Alerts that you have seen before, possibly multiple times per week, lack importance. If you get twice daily alerts about a replication lag, it’s tempting to simply acknowledge it and expect it to self resolve. Especially if the alert comes in out of hours.
- If it’s the case that certain types of events always self heal, they are rarely a good candidate for alerting. These should be downgraded to email notifications or silently logged in order that history can be searched and trended. They should not page on-call engineers.
If an on-call engineer receives more than two alerts per on-call shift, they have a limited amount of time to investigate the root cause and put in proactive fixes. Engineers with a busy pager are always firefighting, and end up reworking the same incidents time after time.

Over alerting destroys morale

Out of hours alerts interrupt sleep patterns and this makes engineers unwell.
Over alerting causes the engineer to be in a state of high alert, especially if they are responsible for a mission critical service.
Regular out of hours alerting can make life difficult for the engineer’s family. Especially their partner who gets woken up as well due to the blaring page notification.
All of these lead to a fear of the next out of hours rotation.

Why does alert creep happen

In my experience, I put this down to two factors. Good intentions and incident post mortems. Starting with good intentions, I’ve done it myself. I’ve thought, you know what will help, a really fine grained alert about X condition. There’s a serious flaw with this. You are motivated for the information at the point in time you create the alert, but when it starts firing multiple times per day or week, you know it is safe to ignore and it stops receiving the action it needs.

The second and most prolific factor in my opinion is incident post mortems. In SRE, post mortems should not be about blame. That being said, when you have to present at an incident review meeting, often to people who don’t work closely with you, it’s natural to want to be seen as competent and not the cause of maxing out the error budget. You also really don’t want repeat incidents so the tendency is to identify reporting gaps. This insures you for the future and makes you look competent, the trouble is that the burden on the on-call engineer grows. More benign alerts, especially overnight, are not the friend of on-call engineers.

Simple steps to fix alert fatigue

Trim and downgrade

Do your alerts line up with your Service Level Objectives (SLO)? There is no need to alert on issues that don’t interrupt your service or uptime. They should be focussed on issues that are eating into your error budget, and thresholds need regular evaluation.

It’s important to trim alerts regularly. During a recent “fixit day” (where meetings are banned and engineers should deal with nagging problems), I relegated 4 alerts from pages to Slack alerts. There is no expectation to check Slack when on call, meaning only the important issues come through the paging service. Don’t let alerts die in Slack or email. Even these need regular evaluation and lifecycle management.

As well as this, some alerts are just no longer useful and should be cut altogether. Don’t be hasty about this but evaluate, can anything actually be done to resolve the issue or is this just information? If it’s information, it’s better for everyone to cut it.

Fair rotation and fair notice

This advice is for engineering managers. Most rotations are fair. If you have x people, they are on every x weeks. Possibly you run a secondary or a working hours schedule as well. Whatever you do, rotations for employees should be equally spaced. The nuance is when it comes to longer holidays such as Christmas and New Year. Split the usual weekly cover into smaller chunks to ensure someone isn’t on high alert the whole time.

When changing schedules, give people fair notice, especially if your jurisdiction doesn’t legally demand it. It’s the right way to treat people and they will reward you by being more engaged and happy at work.

Pay people - Some companies don’t pay for on-call time. I disagree with this but if the salary and other benefits mean the job is still worthwhile, that’s a decision for individual engineers to make. However, a small amount of compensation goes a long way to making the rotation feel less burdensome. You may even get volunteers when someone is sick and you need emergency cover.

When your SQL Server is in big trouble

It’s so important to stay calm when your SQL Server is in big trouble. I’m talking about when blocking is through the roof, when CPU is pinned and people are yelling from all different directions. Staying calm isn’t just about a state of mind, you need to have a process that you work through, that you have practised and you know inside out.

Part 1

How bad is it?

Silhouette of a firefighter in front of a blaze

In this post, I want to describe what I call a priority 2 problem. It is serious and business impacting but the server is online. It needs immediate attention however or it could escalate to a P1.

P1 – Business critical system(s) are unavailable
P2 – Serious impairment of functionality on a critical system
P3 – Performance is slower than usual but requests are completing
P4 – Performance is generally sub optimal and should be improved

Get a colleague on comms

One thing that is guaranteed to happen is that people who are not directly involved in fixing the issue, will want updates. They have good reason to want updates and are well meaning, but the constant need to reply to emails or Teams messages will seriously impact the speed at which you are working.

Back in the good old pre-covid days of office working, this could easily be achieved by someone sitting near you and the two of you communicating verbally. With remote working being more and more common now, I recommend you have some kind of audio channel open that you can speak when you need to and silence is ok too. This could be a phone call on speaker, or a teams call. The technology isn’t the main thing here, the idea is that you can express updates vocally to someone capable of communicating with the wider organisation.

Where possible, your colleague should be technically capable of answering related questions. They should open a chat in Teams or Slack or whatever software your organisation prefers and provide regular updates there. They should answer questions, keep to the update schedule and most importantly, divert people away from you.

A pre practiced routine

Now the scenarios can be different, in this one I want to talk about how I would handle a SQL Server which appears to be functioning (barely) but is extremely slow. In other words, it is a priority 2 incident as defined above. Helpdesk supervisors are calling tech support to advise and customers are calling them as the website is unusable. On top of that, the MI team and data scientists are contacting the DBAs directly because their queries won’t run.

Have a script or checklist

In my script, I tend to use well established community stored procedures. The sort of ones that most DBAs know about and many use. If you start trying to write new queries in the middle of a slow down, that is going to cost you time. Stick with what works, what is established and what you have used before and know well.

I’ve mentioned these in previous posts but the main things I am going to run are:

sp_whoisactive – https://github.com/amachanic/sp_whoisactive
sp_blitzfirst – https://www.brentozar.com/first-aid/
sp_blitzlock – https://www.brentozar.com/first-aid/

sp_whoisactive

I always run this first because it it designed specifically for showing you current database activity, and it has saved my bacon so many times in the past. Simply running this parameter free will show you what is running on your SQL Server right now. It is ordered by query duration descending so the long runners are at the top. It can give you blocking information, information on wait types and on open transactions as well. For advanced features, you need to use some parameters.

sp_blitzfirst

If you can find what you need with sp_whoisactive, you may not even need to use this stored procedure, Where this procedure comes into its own is it tells you when certain things have recently changed or when they out of the ordinary.

e.g. sp_blitzfirst will tell you if:

The plan cache has been recently erased
You have high CPU utilization from a program other than SQL Server
How long each wait type has been waiting during the previous 5 seconds

If something stands out that you don’t understand, there will be data in the URL column that you can copy/paste into a web browser. The web page will give you an explanation of the problem.

sp_blitzlock

This proc is all about deadlocks. You might not need to run it if the first two have given you enough information to fix the issue in hand. However, if deadlocks are at the root of your problems, this will tell you which queries are involved and when. It’s also easier to read than deadlock graphs.

There are more things that I’ll run in the fullness of time but we’ll leave them for later. I only want to focus on these 3 things for now to zero in on the immediate problem.

The script

USE dba

/*
	To run this, you also need to install:
	- sp_whoisactive
	- sp_blitzfirst
	- sp_blitzlock
*/

-- Raises an error if you run the whole script in error
RAISERROR ('Dont run it all at once',20,-1) WITH LOG

-- sp_whoisactive with various parameters
EXEC sp_whoisactive @find_block_leaders = 1 --,@get_locks = 1
EXEC sp_whoisactive @sort_order = 'sql_text' -- Tells at a glance if you have a lot of the same query running. For the F5 report runner troublemakers


-- What has been hurting us in the last 5 seconds. Look for wait stats, and anything out of the ordinary, such as the plan cache has been recently erased.
EXEC dbo.sp_BlitzFirst @expertmode = 1


-- Are we experiencing deadlocks 
EXEC sp_BlitzLock

-- Deadlocks in last hour
DECLARE	@StartDateBlitz datetime = (SELECT DATEADD(HH,-1,GETDATE())), @EndDateBlitz DATETIME = (SELECT GETDATE())
EXEC sp_BlitzLock @EndDate = @EndDateBlitz, @StartDate = @StartDateBlitz
GO



/*  Some other things to consider

	Have the usual optimisation jobs run as expected. Stats/indexes etc
	If one proc has regressed badly, could it help to clear only that plan from the cache or to recompile it.
	EXEC sp_blitz -- Although not as handy as the others for real time issues. Better used as a health check
	EXEC sp_blitzcache -- More useful for helping you identify the resource hungry queries, allowing you 
	EXEC sp_readerrorlog 0,1, 'memory'
*/

The calm after the storm

I will dedicate a full post to this however for now, it is important to say that slow queries or resource intensive queries should be identified and added to a backlog for fixing. If one particular query is likely to go bad again, it should be treated as a priority to fix, in order that we don’t see the same issue escalating again.

You should also do a SQL Server Health Check in order to satisfy that you don’t have an sub optimal configuration causing you issues.

Related to: When your SQL Server is in big trouble

Locks, blocks and deadlocks in SQL Server

Zero Budget DBA – SQLBITS 2020