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.
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
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.
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.
--Create a test database.
CREATE DATABASE myDatabase
--Change database context.
--Create a test table.
CREATE TABLE myTable ( firstname VARCHAR(50), surname VARCHAR(50), email VARCHAR(255) )
--Start explicit transaction but do not commit.
INSERT INTO myTable VALUES ( 'pete', 'whyte', 'email@example.com' )
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.
sp_whoisactive @get_plans = 1;
@filter sysname = ''
@filter_type VARCHAR(10) = 'session'
@not_filter sysname = ''
@not_filter_type VARCHAR(10) = 'session'
@show_own_spid BIT = 0
@show_system_spids BIT = 0
@show_sleeping_spids TINYINT = 1
@get_full_inner_text BIT = 0
@get_plans TINYINT = 0
@get_outer_command BIT = 0
@get_transaction_info BIT = 0
@get_task_info TINYINT = 1
@get_locks BIT = 0
@get_avg_time BIT = 0
@get_additional_info BIT = 0
@find_block_leaders BIT = 0
@delta_interval TINYINT = 0
@output_column_list VARCHAR(8000) = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]'
@sort_order VARCHAR(500) = '[start_time] ASC'
@format_output TINYINT = 1
@destination_table VARCHAR(4000) = ''
@return_schema BIT = 0
@schema VARCHAR(MAX) = NULL OUTPUT
@help BIT = 0
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.