sp_WhoIsActive: Advanced Logging To Tables

sp_WhoIsActive: Advanced Logging To Tables

---

Video Summary

In this video, I delve into a set of custom scripts designed to help you monitor and manage your SQL Server instances more effectively. These scripts include a stored procedure that dynamically creates views based on the `whoisactive` pattern, allowing for easy retention management and comprehensive query analysis. By leveraging these views, you can easily identify and troubleshoot performance issues, especially those related to blocking and resource contention. Additionally, I walk through another stored procedure responsible for logging detailed information from the `sp_whoisactive` command into a table, ensuring that even fleeting events are captured. The third script handles retention by deleting old log tables and updating views accordingly, maintaining data integrity without overwhelming your database with historical records. Finally, I provide an agent job setup to automate the logging process, making it as seamless as possible for you. All this code is freely available on my GitHub repository, so feel free to check it out and adapt it to fit your needs.

Full Transcript

Erik Darling here, Darling Data, Enterprise Solution Architects. Just kidding, what a goofy, goofy thing to call yourself. In this video, we are going to talk about the set of code that I use to log the wonderful talented, beautiful, vocally gifted, SBHoo is active to tables. The views that I create to help you figure out what’s going on on your server, how I manage retention, and the agent job that I provide to get you up and running. Now, I created all this stuff because this is stuff that I have to do regularly in my client work if they don’t have a monitoring tool or to catch things that other stuff might be a bit overbearing to use, like extended events or something like that. Folks don’t have a monitoring tool. Crazily enough, some people who are still not on a version of SQL Server where Query Store is available, some of this stuff is very, very, very, very, very, very useful.

So, excuse me. Foggy in here today. So, the first store procedure in the bunch is one that will create views for you to use behind the scenes. Now, this store procedure gets, there’s a reason why this is a store procedure, and there’s a reason why this thing gets called every time SBHoo is active runs, and we’ll sort of talk a little bit about that. So, the two views that I create are, so, I should take a step back. The way that I do my logging is I don’t just keep dumping everything into one giant table because those tables can get out of hand really quickly. It also becomes really hard to search through them unless you add indexes to them to, like, figure out, like, you know, when something happened, right? Like, search through for, like, dates or whatever.

So, what I do in this procedure is look in sys.tables for any new table that the store procedure, again, it creates one table per day, which also makes managing retention a lot easier. So, it’ll look through sys.tables for any tables with a name that matches the whoisactive pattern that I create, and it will essentially create one outer view called dbo.whoisactive with all of those tables sort of union all together, right? So, you have one view that encapsulates all of the daily tables. The daily tables are called, like, whoisactive with, like, the year, day, and month.

And then you have the whoisactive view, which union all those tables together so you don’t have to search through, like, which tables are there and whatever. You can still, obviously, select from individual tables if you are interested in something that happened on a particular day at a particular time. That’s fine. But, again, that’s a lot easier to do for a table that only has been logged to for one day rather than for a table that’s been logged to for, like, a week.

The other view that I create is one that specifically does a recursive CTEquery to enumerate blocking on a server. So, the blocking query, I mean, it’s a long, complicated thing, but you’ll see the results in a minute, and I think you’ll be fairly enamored with what comes back. But this is the whole recursive CTEthing, and then I select everything out of that recursive CTEto give you a bunch of good information about the blocking queries on your server.

Even this can be really useful. Even if you have the block process report set up, the minimum threshold for blocking to get logged in it is five seconds. So, if you have blocking that goes on for less than five seconds or fewer than five seconds, depending on how you think about these things, then this can be useful to catch that stuff as well. I don’t suggest you log SP who is active to a table every second. That would give you a different problem.

But sometimes the agent job that I give you goes every minute. You are free to adjust that to your needs. All right. So, this is the stored procedure that creates the views. And again, the reason this is important is because as tables get created daily or as tables leave their attention period, we’re not going to have them available.

We’re going to either not have them available or we’re going to have new tables available that need to be part of those views. So, the outer view here really helps to, you know, encapsulate all those daily tables. And then the blocking thing just works off the other view.

And I know nested views, bad, horrible, but, you know, I’m a professional, so you can’t argue with me. The second store procedure is the one that does the actual logging. And this one pretty much does the, follows the instructions in the SP who is active documentation to set up a table, set up the destination table that we’re going to log SP who is active to.

All right. So, it does a bunch of this stuff. It, you know, concatenates things together nicely.

And then it executes who is active and we tell the store procedure we want to log you to a table. So, we give you, so we get that stuff and so we get all the parameters right in there. And then this is what does the actual logging to a table.

Right. This is what does the actual logging. That sets up the table and gets all the stuff that we need set up for it. This does the actual logging.

And then this store procedure runs to manage retention. So, by default, I keep 10 days worth of SP who is active tables on there. You are free to change that to your whims and fancies.

All right. So, first we delete any tables that are outside of our retention period. And then if we have created any new tables, again, because they are daily tables, then this, the who is active logging procedure to create views gets run to recreate the view definition so that we make sure that we’re only looking at the correct set of tables when we select from our views so that we don’t get weird errors.

Or we don’t get weird errors when the view tries to get created either. That would be terrible, wouldn’t it? I wouldn’t like that.

The third store procedure is the one that manages retention. So, this one builds up a string, essentially drop table for any tables that match the who is active pattern that I create that fall outside the retention period. All right.

So, good stuff there. Okay. This one’s pretty short and sweet and to the point, so I’m not going to spend too much time on it. You’ll just have to trust me that it works if you don’t feel like reading all the code. And all of this code is available freely in my GitHub repo.

So, if you decide to, you want to check it out, there’s a link in the video description for you to go do that. And if you find any issues with it, if you want to make any improvements to it, you can’t because I’m a professional and you can’t. You can’t reason with me.

Just kidding. I’m very happy to get GitHub issues, either to answer questions, get performance, report bugs, things like that. The final piece of code that I give you is to set up the agent job to run spwho is active.

Now, again, this will get you every minute, right? This will execute every single minute. Right?

So, it’s just pretty stock agent job creation stuff, agent schedule creation stuff. There’s nothing all that new, brave, or interesting in here. This is just a quick and easy way for me to hit F5 and get all the right stuff in place.

So, when I need to do this for clients, I don’t have to sit there and remember to tick the right boxes and set things at the right interval and all that. So, four neat pieces of code built on a very neat piece of code called spwho is active. And the results are pretty useful.

So, this is the main who is active view right here. And if we select star from this and we just say order by collection time. Collection time is a column way over here that isn’t part of like the stock and standard who is active results.

This collection time is one that specifically gets used so you know when these things got logged to a table. But if we order that by collection time and then say CPU descending because, you know, let’s say we care the most about high CPU queries on a server. We will get all of the standard spwho is active results back except logged to a table.

All right. So, we get all this fun stuff in there from, you know, again, this is just from a couple runs that I did of the agent job while I had some stress on the server. I didn’t want to do it live because, you know, it’s kind of, you know, again, the labor pains and the baby.

Here’s the baby. Okay. Cool. Now, what we can do, oh, my goodness, I forgot to alias that.

Someone fire a SQL prompt. All right. So, if we look at the spwho is active blocking, granted, I did not do anything too ambitious here because I really just wanted to get this recorded and available for you to consume and digest and, well, excrete in some way. Hopefully some useful way that makes your job and life easier and, I don’t know, helps you track down some problems.

But what this gives you back, and this is, again, what that whole big recursive view query that I showed you earlier in the video does, is gives you how long things were going on for. It tells you the speed and the date that things happened, which is useful information. If we come over here, we can see the weights that were in place.

So, the query getting blocked was waiting on LCKMIS. This was waiting on sleep, bpool, bpool, steal, well, and some other stuff too. I like to get as much information into this stuff as possible because I’m not doing it live and I don’t know what I’m going to need to troubleshoot.

So, I’d like to get as much stuff in there as I can so that I don’t have to say, crap, I missed it. Because nothing breaks confidence in a consultant like them saying, crap, I missed it. So, we get all this stuff back with, like, you know, the normal blocking stuff, that lovely new implicit trend column, you know, open transaction count, collection time, all that other stuff that we sort of talked about before.

Again, we’ve talked about this in a previous SPHUAS active video, but this is the get additional info column where you can see exactly which object we were waiting on locks to be released from so that we could do our read query. But, yeah, this is what the blocking view gives you. If there were more blocking in here, we would have, you know, additional things showing us other lead blockers or other queries that were blocked.

So, again, nothing too ambitious here, just enough to sort of give you an idea of what to expect from the results. So, with all that being said, thank you for watching. I hope you learned something.

I hope you’ll try these scripts out. I’ve had a lot of very good luck with them helping clients find weird issues with SQL Server. So, hopefully, you can, you know, with your clients or your employer, you can do the same thing.

If you enjoy this sort of stuff, if you enjoy my videos or me or, I don’t know, if you just need something to fall asleep to, like and subscribe. I’m here for you. I can be your lullaby.

Okay. And, let’s see, we covered thank you, covered like and subscribe, covered all the code, covered. I don’t think there’s anything else, is there?

No. All right. It is eight minutes until five o’clock. I think that’s close enough for me to start having glasses of wine, so maybe I’ll just go do that and save other videos for tomorrow. All right.

Cool. I’m out of here. Thanks for watching.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.