Why You Shouldn’t Use SQL Server’s Activity Monitor

I love free tools. I also love analyzing SQL Server’s wait statistics. But I’m not a fan of Activity Monitor, a free tool in SQL Server Management studio, which helps you look at wait stats.

Activity Monitor just doesn’t give you the whole truth.

I fired up a workload with HammerDB against a test SQL Server 2014 instance. My workload runs a query that’s very intensive against tempdb, and it’s really beating the SQL Server up by querying it continuously on seven threads.

Let’s look at our wait statistics. Here’s what Activity Monitor shows in SQL Server 2014:

ActivityMonitorSQLServer

“Buffer Latch” must be my problem.

Here’s what our free procedure, sp_BlitzFirst shows for a 10 second sample while the workload is running. I ran: exec sp_BlitzFirst @ExpertMode=1, @Seconds=10;

sp_BlitzFirst

Hmmmm, that top wait type doesn’t seem like it was in the first screenshot at all.

Here’s what Adam Machanic‘s free procedure, sp_WhoIsActive, shows for an instant in time during the workload. I ran: exec sp_WhoIsActive

spWhoIsActive

Here I get specifics on exactly what type of wait is is and the related query.

Let’s compare.

Activity monitor groups wait types. It took a whole lot of waits and rolled them up into ‘Buffer Latch’. This isn’t necessarily a bad thing, but I’ve never heard of documentation that explains what’s rolled up into which groups. By comparison, sp_BlitzFirst showed me the specific wait types PAGELATCH_UP, PAGELATCH_SH, and PAGELATCH_EX, with the amounts for each one. sp_WhoIsActive even showed me the type of page that is having the bottleneck (GAM) and the database and data file (tempdb’s data file 1).

Activity monitor leaves out wait types. sp_BlitzFirst showed that in aggregate, my #1 wait was CXPACKET over the sample. That tells me that a lot of my queries are going parallel. That’s not necessarily a bad thing, but it’s important for me to know that about my workload at the time. It helps me know that I want to learn more, and make sure that the right queries are going parallel. (In this case, the query that’s going parallel is pretty cheap, and is just as fast single threaded. My throughput goes up dramatically if I adjust the Cost Threshold setting up a bit.)

Friends don’t let friends use Activity Monitor. It may be convenient, but it doesn’t tell you the truth. Do yourself a favor and use a free tool that gives you wait statistics straight up.

Start with sp_BlitzFirst instead.

Previous Post
Building a Reliable SQL Server for kCura Relativity [Video]
Next Post
A Guide to Contributing Code

17 Comments. Leave new

  • Recently had a developer leave this open for over a day. Not only did it drag the server, but he ended up blocking himself from expanding tree items in the GUI.

    Reply
  • And then there’s the FREE, Ignite tool that I’ve found extremely useful, and educative. You have to hunt for it down the bottom of SolarWinds’ Free Products list, but it’s worth it. Using, particularly, the WAIT analysis it performs over a rolling hour, we’ve identified and solved a huge number of slow-running queries for many different WAIT causes.

    I have NO connection with SolarWinds BTW, so this isn’t a product plug, merely a freebie-plug.

    Reply
    • What is the name of the software you’re speaking of? I perused SW’s site and only found one free product for database analysis/management, named Database Monitor.

      Reply
    • That’s the NEW name SW gave it. When you download, it’s Ignite_f_8_3_407_setup.exe, as of last week, that is.

      If you click the download button, you register (it’s easy to unsubscribe) and you’ll see a product image that shows, at top left, “Ignite”.

      I have it watching 5 servers on 3 different domains from one webpage. Must admit that some of the language in the setup pages is “different”, for an accidental DBA like me, but it’s worth wading through. SQL Login for the ignite_repository (SQL Database on a dev server), I think I took the defaults, but used sa just for the install, and you assign the strong password.

      Reply
  • I think that experienced DBAs prefer monitor the activity of SQL Server via T-SQL commands and dynamic management views/functions. Activity monitor is just for beginners.

    Reply
    • Kendra Little
      October 22, 2014 9:22 am

      I think beginners end up using it, just because they don’t have a way of knowing about other things. But I think it’s harder for a beginner.

      But I think sp_WhoIsActive is more intuitive for a beginner. Most people can see pretty quickly, “This is what’s running, sorted by how long.” And the wait types are easier to search on because they haven’t been renamed and categorized and had some things removed. It gets right to the point.

      Reply
  • So, I would like to speak in Activity Monitor’s defence. I think that as long as you are aware o
    Its limitations, it is a good basic tool to give you that mile-high overview of what’s going on in a particular SQL Server instance. I agree that it should NOT be used as the definitive too for rooting out performance issues and I ageee there are much better tools out there…

    If you were ever a Unix sys admin, I think you relate to the anology of the vi editor. The best thing you can say about it is “It’s always there”.

    Reply
    • Acting as Defense Council for Activity Monitor will be Ian Miller! 🙂

      I can see your point also applying to sp_who2.

      Reply
    • Except vi/vim editors aren’t just basic tools when it comes to Unix tools that are “always there”. After all those years they are superior compared to a lot of younger editing/programming tools.

      Of course they aren’t very intuitive for people used to GUI and mouse only, like in that very true joke:
      – what’s the best random character generator?
      – windows user with task to exit vi/vim.

      But after a while, when you get beyond basic shortcuts and configuration, You are getting powerfull and very fast tool that is hard to replace.

      Reply
  • To expand on this a bit, sp_whoisactive works very well as part of an automated monitoring solution. Capture wait stats, active processes, get a look back in time to see what was slowing things down last night or last week.

    http://realsqlguy.com/servers-youre-sleeping/

    Reply
  • Rick Obsitnik
    October 24, 2014 2:13 pm

    “Friends don’t let friends use Activity Monitor” I live by that statement. I give ugly looks to those that say look at it in Activity Monitor…

    Reply
  • Most senior level resources rarely rely on canned tools, external or native. Most of us like the idea of controlling what we are seeing at a very granular level. I like Randal’s script for wait stats and sp_whoisactive is the single best free tool there is. Now, I’m not neglecting all of the incredible scripts written by those here, just that I use sp_whoisactive every single day… on every single machine. This combining of wait stats isn’t helpful and often times confusing for those new to waiters. Many third party tools do this also. I have clients that use Ignite. If you have a chance check out the sundry wait types on an Ignite instance and see if anything jumps out. Now, you’ll have to know the basics of wait stats but if you do, it will jump out at you. Hint… it will be one of the top waits and will be the only one that isn’t a true wait stat at all.

    Reply
  • I use activity monitor sometimes, but I find that when I need it most it fails me. I had some developer write a bad query that SQL Server estimated was going to return 1009360000000000 rows and fill up tempdb. Since activity monitor requires tempdb it was failing while I was trying to find issues. I don’t have access on this server to install sp_whoisactive since I am only a developer myself, so I ended up using sp_who2 to find stuff to kill.

    Reply
  • The screen shot for the sp_BlitzFirst results is broken (for me). Could you have a quick look? Link points to http://2cqznbs0ua3adth82a3e1a15lq-wpengine.netdna-ssl.com/wp-content/uploads/2014/12/sp_BlitzFirst.png which displays a “Website not found” when I try to load the pictures separately.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}