Sp_whatnow? sp_who, sp_who2 and sp_whoisactive are stored procedures that allow you to view current users, sessions, and processes within a SQL Server instance. You’d want to see this for identifying things like blocking or checking general activity.
sp_whoisactive is definitely one of my favourite stored procedures. Created by Adam Machanic, this tool gives you an instant view of current SQL user process. Similar to sp_who/sp_who2 but doesn’t show system processes and gives you more useful information.
Below is a quick run-through of each of these stored procedures.
sp_who
This’ll get you information on all background and user processes going on in SQL at the time of execution. Info includes login names, hostnames, what kind of SQL command is running and which database it’s running against.
The first 50 results are system SPIDS which you’d never kill. Unless, maybe you identify the lazy writer is broken or something? I never have a need for sp_who personally as sp_who2 & sp_whoisactive do the trick.
See my other post for more on killing SPIDs.
sp_who2
An undocumented stored procedure by Microsoft, which means that it can be deprecated or modified without any notice. It shows the same as above but with some additional columns. I use this to kill off user connections on rare occasions but that’s about it really.
sp_whoisactive
You’ll see replication SPIDs such as your log reader agent, monitoring collections as well as any end users running reports / manual SQL queries. Info includes full SQL queries, query plans, hardware usage, tempDB allocations, blocks and more. This 4 minute video by Brent Ozar gives the perfect run-through:
To test this in less than 5 minutes, download sp_whoisactive, copy/paste the contents into a new query window and run the code against any database. It’s likely you will have no active user sessions in your test environment, so before we run it we’ll setup a blocking process. Run the following in sequential order, highlighting until each line break.
Now run a select statement in a new query window on the same table. The insert has a lock on the table until committed, so it’ll continue to execute as a result of being blocked.
Running sp_whoisactive now will look something like this.
As you can see I’ve highlighted the blocking sessions. For more information on encountering such events, see my blog post – Killing SQL Server Processes.
On rare occasions I’ve see my own exec sp_whoisactive SPID come back within the results. Likely it’ll depend on performance. You’ll know if your server is burning alive if running sp_whoisactive takes a long time to return results.
Most of the time I’m using sp_whoisactive with no parameters as shown above or with @get_plans = 1, but there are many that can be utilised depending on the task.
Another common use of this stored proc is to have it running as an agent job every 30-60 seconds, storing the results into a table. See my other blog post – Logging sp_whoisactive to a table for a run-through on that.
Leave a Reply