When your SQL Server is critically injured you need a first responder kit to help you diagnose the problem and apply emergency aid. In this session, Kendra Little introduces you to invaluable tools and techniques for triaging an emergency. If you have one year’s experience with database administration, this half-hour session will set you up to triage like a pro.
Want a list of links referenced in the video? Scroll on down!
Links from the video:
- Dedicated Admin Connection: https://www.brentozar.com/go/DAC
- sp_WhoIsActive tutorial: https://www.brentozar.com/go/active
- sp_WhoIsActive instructions for running in a loop and storing off data: https://www.brentozar.com/go/activehistory
- DMV queries from Glenn Berry: https://www.brentozar.com/go/glenn
- Perfmon tutorial by Brent: https://www.brentozar.com/go/perfmon
7 Comments. Leave new
Really neat,Kudos for this!
Well done Kendra.
My emergency kit is filled with good intentions. I may have to change that. 🙂
http://www.qwantz.com/index.php?comic=2097
I’d also say “Think Blocking” – nothing faster than sp_who2 in emergency situations. Also it’s not safe to assume that you can always modify production systems to create procedures like sp_whoisactive or even databases for DBA usage.
Tony S.
Hi Tony,
Thanks for the comment. In my experiences with busy OLTP systems, I’ve found that sp_who2 may be quick to execute, but it can be painfully time consuming to untangle. When there’s a large volume of sessions, things are sorted very strangely, and figuring out what’s blocking what can involve a lot of scrolling and scrambling. On quieter systems, it can be OK.
I do agree about looking at blocking. That’s one of the things I do first in the quick check of sp_whoisactive, because I always want to know quickly if that’s involved or not. Things can get a little tricky if you don’t know how much blocking is normal for a given app, of course!
My own experience has been that most companies allow the creation of a database for administrative purposes, but many require that it be done with a change request in the change control system. (Which I like!) The benefits are that it keeps administrators from having to make changes to application and system databases, which I’ve found most change approvers agree with. So hopefully most of the time people can get these created— makes maintenance so much more portable and easier to standardize.
sp_who2 is definitely cumbersome to work with. That’s why I built a tool to make it easy.
Here’s a presentation : http://www.slideboom.com/presentations/300782/Building-a-Performance-Data-Warehouse-for-SQL-CPU-&-PIO
I used this tool for 10 years. I let it run all the time so that when complaints came in about a performance problem that had passed, I could usually find a smoking gun if the issue were CPU or PIO.
Thank you for the interview tips. They helped me to get very nice feedback from the employeer today. You guys are great!