sp_activespids will tell you
There’s a lot of ways to see what requests are running on a MS SQL instance. For years I’ve trusted this handy custom stored procedure, sp_activespids, to get it done quickly and with little overhead. It’s been around in one form or another for almost 20 years. I’ve made most of the more “recent” changes to it but half a dozen DBA’s have tweaked it over the years (miss you guys). It was radically modified after SQL 2005 to rely on dynamic management views. In IT years it’s about 100 years old but as long as Microsoft is going to rely on the same DMV’s, it’s still relevant.
Sure there is the “activity monitor” in SSMS but if I’m hearing about performance issues (or notice it in monitoring), I don’t want to add to the overhead on a strained system while pulling that up. I just want a quick real-time view into what is actively running right now.
sp_activespids is like a dog jumping up over the fence line to see what is going in the neighbor’s back yard. The dog’s not going to have the history of what WAS going on over there, but he smells BBQ and he wants to see who’s cooking the meat, if the smoker is accessible and if anyone is around to eat it RIGHT NOW. If the dog was created by AI it’ll just be able to fly over the fence like a magnificent fur angel, land on its mysteriously suspended paw, and eat the meat with both mouths.
Just like that dog’s sense of smell, it returns a lot of information quickly and I don’t have to pull up a script or log in to some interface for some other monitor that may be a few minutes behind. And since it is registered as a system object, it can be executed from the context of any database.
So, I present to you sp_activespids (way better looking than it’s older cousin, sp_who2, which does not have its own website, more on that later).
You can download it below. It consists of two procedures.
- ActiveSpids
- sp_activespids
The prior you can store in any administrative database you already have like DBAdmin per the example in the scripts. The later goes in master and gets registered as as system object so that you can quickly call it from the context of any database. There is also a script which grants the minimum permissions necessary to be able to call it (in case you have other people on your team that do not have sysadmin or you want to run it from a non-privileged account).
https://github.com/danshargel/sp_activespids
I also recommend familiarizing yourself with Adam Machanic’s sp_whoisactive. It might be the only stored procedure in the universe that has its own domain and website. That’s how good these performance based administrative procedures are. I guarantee that “activity monitor” does not. Since both procedures ultimately rely on MSSQL DMV’s they’re going to be fairly similar. A lot of his documentation would apply to sp_activespids and I’m too lazy to write it. Try them both out, can’t hurt.
