on January 14, 2016
Hindsight is everything. I was lucky to be trained by a great team of DBAs back when I first started with SQL Server. But it’s hard to know exactly what you really need to know, particularly as new tools are becoming available.
Here’s the three things I wish I’d caught on to sooner.
3. How to See What’s Running in SQL Server (and How Long It’s Been Running)
I used the built in sp_who2 procedure for a long, long time.
Sp_who2 doesn’t tell you much. It doesn’t tell you exactly which queries are running, how long they’ve been running, or what they’re waiting on. It shows you information on a lot of sessions that are just sitting there, sleeping, doing nothing. And it even orders things funny if you have a lot of sessions.
Now, I’m a huge fan of Adam Machanic’s free sp_WhoIsActive procedure. One of the most popular posts I’ve ever written on this blog is how to log results from sp_WhoIsActive into a table. That’s still cool!
sp_WhoIsActive has less overhead than SQL Server’s Activity Monitor, and I find it easier to understand, too.
2. How to Deal With Wait Stats
There’s two things to know:
- Wait stats are critical for performance tuning SQL Server
- Learning wait stats isn’t like riding a bike, it’s like learning a foreign language
Much like learning a language, you need to spend some time with it and get used to what’s normal and listen a lot. By sampling wait stats regularly and baselining, you learn what’s normal.
You also need to research individual wait stats and dig into their meaning. You need to learn how different wait stats are related to one another. That’ll take time.
A great free tool to get started with wait stats is Brent Ozar’s sp_AskBrent. You can sample wait stats since startup, for a defined interval, or log them to a table. (Bonus: it shows you some wait stats that Activity Monitor hides.)
1. Why the Transaction Log File is So Important
Even after I learned how to set up log backups, I didn’t really understand what the point was. When I finally got the concept I felt very much like a light bulb had come on over my head. Everything about backups suddenly made much more sense.
Here’s the basics:
When modifications happen in SQL Server, they must go to two places, and two places only: memory and the transaction log. This is because SQL Server uses “write-ahead logging”. (AKA “WAL”. Creative acronym, right?)
Lots of committed inserts, updates, and deletes may be recorded only in your SQL Server’s memory or the transaction log, and they may not be in the data files at all. So if you suddenly lose your data files, the ONLY place those modifications might be written to disk is in your transaction log– and that’s why log backups are so critical.
This is also why the transaction log file is even more important to protect on the SQL Server than the data files are. If the data files vanished and the SQL Server was still online, you could back up the tail of the log to avoid data loss.
REGISTER