In theory, SQL Server performance monitoring is pretty simple:
Review the server's top wait types
Find the queries causing those wait types
Fix those queries, or improve the way the server reacts to them (indexes, settings, etc.)
Let's hop right into it. I've got one lab server (named Colorful), so when I open the app, my estate overview isn't particularly interesting - yours will be:
Go into the server's details, and you see the top wait types over time:
When your SQL Server's top wait stat is ASYNC_NETWORK_IO, that indicates SQL Server is waiting on the app to ask for the next part of the result set.
This is a real pain in the rear to troubleshoot because often these waits happen for really short duration each time - think milliseconds, not seconds. It's an issue of death by a thousand cuts.
You've got a bunch of users on the same SQL Server, and you wanna know who's using the most resources. SQL Server doesn't make this easy. You can query some management views to see which queries or databases have been using the most resources, but you can't intuitively tell which USERS are.
To solve this problem, we're going to use Resource Governor.
When your SQL Server restarts, how do you find out?
I've seen a few senior DBAs do a really smart trick: they write a stored procedure to send them an email, and then they mark that stored procedure as a startup stored procedure so that it runs automatically whenever SQL Server starts up. (You could also try this with an Agent job scheduled to run at Agent startup, but ... Agent may not actually start.)
Page Life Expectancy is a Perfmon counter that's supposed to track how long pages stay in memory before they get flushed out to make room for other pages that are needed instead.
Paul Randal has blogged this a few times, and rather than rehash 'em, I'd rather point you to a couple of his roundups:
SentryOne's Greg Gonzalez recently published a post about "Why Wait Stats Alone Are Not Enough." Sure, Greg's obviously biased towards his own monitoring tool, but his post is really well-written and fair. I want to explain the same story, but from a different (and simplified) angle.
Say we've got a really simple database server, and we start out with just one query that runs each day:
Everybody tells you to capture baselines - but what exactly are you supposed to capture? Which Perfmon counters matter? How do you track which data files are growing, and which ones are slow? How can you track which queries are using the most resources? All you have to do is install the free, open source…
You've seen the CEIP Service on your SQL Server, and you're wondering what queries it runs and how it sends that information back to Microsoft. I was wondering too, because I started seeing queries running that I didn't expect:
Ah-ha, the telemetry service, also known as SQLCEIP! Starting with SQL Server 2016, your database server phones home to Microsoft by default. I clicked on the sql_text to see what query was running:
[crayon-6a5e231bb38ac121177373/]
(Cross-posted from the PASS Blog.)
Years ago, my first go-to tool for performance tuning was Performance Monitor: it was available everywhere, worked in all versions, and gave me a lot of useful information without paying for a monitoring tool. Since then, I've moved on - wait stats are a much better starting point - but there are still 3 good Perfmon counters that I rely on.
Queries are running when you're not around. They're wreaking havoc - maybe they're filling up your TempDB, or causing blocking, or flushing your buffer pool out.
When I wanna see what queries are running, I run sp_BlitzWho:
When you need to find which queries are blocking other queries, your decision comes down to when the blocking happened. Is it happening now, recently, or coming up soon? During a live emergency, start with sp_WhoIsActive. Adam Machanic's excellent free sp_WhoIsActive replaces Activity Monitor, sp_who, and sp_who2, and it's way more powerful. The documentation is extensive -…
Someone comes running in and asks, "Are you doing something on the SQL Server right now? Can you take a look at it? It seems really slow." It's one of the most common complaints we get about our databases - and yet, most of us don't have a written plan with what steps to take, in order.
In the free 30-minute intro calls I do about our SQL Critical Care®, one of the questions I ask is, "Do you have a monitoring tool? And if so, what has it told you about the root cause of the problems you're facing?"
I either hear one of two things:
I love Query Plans
No seriously, I love digging into query plans. Even writing XQuery to dig into them in sp_BlitzCache. I was both honored and horrified to take over development of it from Jeremiah.
It's a beast, but it's my kind of beast. At around 4500 lines of code, when you want to make a change, well, &^%$ happens sometimes.
It used to make sense
When I first look at a server, I want to know how busy it is, where its bottlenecks are, what is SQL Server waiting on and many other things. Batch Requests/sec is one of the data points that is used to measure how busy a server is.
WHAT IS BATCH REQUESTS/SEC?
I like science fiction Not enough to go to conventions, or memorize every line, but I have some opinions on things. Dune is probably my favorite series of stories in the genre. Yes, I think Paul would beat Luke in a fight. Sorry, Star Wars people. He's just kind of a wimp. Throw grenade, digress One…
We've already covered how to log activity using sp_WhoIsActive, but I wanted to share how I've seen it done in the wild. The difference between the two methods is that this version logs everything to the same table and is for continuous activity logging (scheduled job), whereas the other version uses a new table each time it logs activity and is for when you are working on a problem.
Moving parts SQL Server keeps on growing. With every new edition, you get more features, feature enhancements, and uh, "feature enhancements". As I'm writing this, SQL Server 2005 is less than a week away from support ending, and SQL Server 2016 is up to RC2. Brent's retrospective post got me thinking a bit. We went…
