Who is Active? is a comprehensive server activity stored procedure based on the SQL Server 2005 and 2008 dynamic management views (DMVs). Think of it as sp_who2 on a hefty dose of anabolic steroids. Features supported by Who is Active? include:

• Server activity collection, including data about currently running T-SQL, server resources consumed by the request, and query plan collection
• Real-time wait statistics collection and blocker reporting
• Delta collection mode, in order to find out what processes are doing over time
• A number of filter options to help you narrow down the scope of data returned, the order of rows, and the number and order of output columns
• Ability to collect to a table, rather than sending the data back as a rowset
• An online help system to help you figure out what options are available. Use the @help=1 option to hit the ground running.

License:

Who is Active? is free to download and use for personal, educational, and internal corporate purposes, provided that the included comment header is preserved. Redistribution or sale of Who is Active?, in whole or in part, is prohibited without the author's express written consent.

Donate! Support this project: http://tinyurl.com/WhoIsActiveDonate 

---

Change log for the most recent several versions:

 

Who is Active v11.00

• Added wait information for OLEDB/linked server waits
• Wait collection will now "downgrade" to get_task_info = 1 style data if no other information is available in get_task_info = 2 mode
• Fixed sort order bug w/ multicolumn sorts
• Added header information to online help
• Added a login_time column to the output
• The duration for sleeping sessions is now the sleep time, rather than the time since login
  

 

Who is Active v10.99

• CPU deltas can now use real-time thread-based metrics for more accurate data (use both @delta_interval and @get_task_info = 2)
  
• command_type information added to [additional_info] column for active requests
  
• Query plans that are not able to be rendered due to XML data type limitations will now be returned in an encapsulated text form
  
• Fixed bug where system processes were sometimes reporting start times in the future (thanks, Neil Hambly!)
• Fixed bug where @get_locks sometimes failed with a constraint error (thanks, various reporters!)
  

 

Who is Active v10.83

• Modified elapsed time logic to retrieve more accurate timing information for system SPIDs, in many cases
  
• Fixed bug where @get_task_info = 2 was adding an "N" before some waits
• Fixed bug where tempdb_allocations was being incorrectly calculated for active requests
  

 

Who is Active v10.76

Enhancements

• SQL Agent job info (job name and step name) is now included in the additional_info column (use @get_additional_info = 1) (thanks, Argenis Fernandez!)
  
• If there is a lock wait, information about the blocked object (name, schema name, and ID) is now included in the additional_info column (use both @get_additional_info = 1 and @get_task_info = 2)
• Service Broker activated tasks are now shown by default, without using @show_system_spids mode. The program_name column contains the queue_id
• Various numeric columns, including reads, writes, cpu, etc, have been made nullable. These will occasionally return NULL, on extremely active systems where the DMVs return data more slowly than queries start and complete

Updates

• Removed workaround to handle MARS bug documented here: http://connect.microsoft.com/SQLServer/feedback/details/490178/request-id-in-sys-dm-os-tasks-wrong-when-using-mars ... this was done in order to fix an issue where task information could not be populated in systems that had been up for some time (thanks, Michael Codanti!)
  
• Modified the way transaction data is collected; @get_transaction_info should now perform better than it previously did
• Modified central collection mechanism to read fewer rows from sysprocesses on each pass.
  

Bug Fixes

• Removed get_original_login column from @get_additional_info option, in order to make the procedure once again fully compatibly with all versions of SQL Server 2005 and SQL Server 2008
  
• Fixed bug where @get_transaction_info would throw an exception when run on servers set to use a non-US English language (thanks, Tobias Ortmann!)
  
• Fixed bug where @get_task_info = 0 erroneously collected blocking information
• Fixed issue where on SQL Server instance start-up, SPIDs have a start_date of 1900-01-01 until recovery is complete, which was causing an overflow exception (thanks, Michael Codanti and Allen White!)

 

Who is Active v9.98 (10.00 Release Candidate)

• Added new option, @get_additional_info: Returns a column called [additional_info] that contains various non-performance-related information sourced from the sessions and requests DMVs
• Fixed @get_avg_time: This option had been broken for several versions. (thanks, Ola Hallengren)
• Workspace memory greatly decreased: In some cases the proc would ask for a workspace memory grant of up to 200MB. This was problematic in situations with a lot of concurrent activity. This version reduces the grant to under 4MB in the vast majority of cases.
• NOTE: The workspace memory fix is a tradeoff, and the opposite side of the coin is that the proc will now cause slightly more activity in tempdb. I need testers to compare the performance of v9.90 to v9.98. Please give it a try and let me know your results as soon as possible!
  

 

Who is Active v9.90

• Discovered that due to inconsistent results from DMVs on servers under extreme load, the script will very rarely throw a unique key exception. Added IGNORE_DUP_KEY to the two main temp tables used in the script, in order to avoid this situation. (Thanks, Sankar Reddy and others)
  

 

Who is Active v9.89

• Fixed a bug where wait types were prefixed with an N in the wait_info column
• Changed the filter for system SPIDs on sysprocesses to use the hostprocess column rather than hostname (thanks, Dan [last name unknown] and Erland Sommarskog)
  

 

Who is Active v9.87

• First stab at international database support
• Switched from VARCHAR to NVARCHAR almost everywhere appropriate
• sql_text, sql_command, locks, login_name, wait_info, database_name, and other areas should all show the full set of available characters
• Not currently supporting double-byte characters in the tran_log_writes column, due to issues with right-to-left languages causing SSMS to mangle the output
• Use of characters in identifier names (e.g. database names, table names, etc) from unsupported character ranges per the W3C XML standard may cause Who is Active to throw a run-time exception. This is something I need your help with. Are you using characters in your identifier names in the ranges 1-8, 11-12, 14-31, 55296-57343, or 65534-65535? I suspect the answer is no; please let me know if I'm mistaken.
• Changed the CREATE syntax at the top so that the stored procedure will not longer get dropped and re-created, thereby ensuring that existing permissions won't get overwritten when upgrading to a new version
• Please let me know if you're granting access to Who is Active using module signing, in which case I will attempt to make further modifications in this area
  

 

Who is Active v9.72

• Removed reference to sys.dm_exec_query_memory_grants; now getting granted query memory info from sys.dm_exec_requests
• Who is Active is now compatible with all versions of SQL Server 2005 and SQL Server 2008
  
• Thanks for testing help, Mladen Prajdic
  

 

Who is Active v9.71

• Fixed a bug where session_id was being converted to TINYINT rather than SMALLINT (thanks, Linchi Shea)
• Fixed a bug where self-blocking sessions in sysprocesses were incorrectly being shown (thanks, Jason Pease)
• Fixed a bug where block leaders was causing a recursion overflow (thanks, Sankar Reddy)
  

 

Who is Active v9.68

• Fixed a bug where an internal UNIQUE constraint could occasionally be violated (session_id/kpid is not, as it turns out, truly unique in sysprocesses -- needed to add ecid to the key)
• Fixed a bug where ignoring CXPACKET waits would cause other wait types to not bubble up in the default @get_task_info = 1 mode
• Fixed a bug where PREEMPTIVE wait types were not showing properly in some cases, due to the task state being set as RUNNABLE rather than SUSPENDED in sys.dm_os_tasks
• Now showing the actual latch type for LATCH_* waits
  

 

Who is Active v9.62

• Further improved performance of the default "lightweight" wait collection mode (@get_task_info = 1).
• Changed the name of the tempdb_writes column to tempdb_allocations (thanks Linchi Shea)
  

 

Who is Active v9.59

• Fixed collation bug where some database names would cause an error to occur due to special characters used as part of the script's processing work
• Added log used kB information to the transaction_writes column
  

 

Who is Active v9.55

• Massive re-work of the core queries, resulting in greatly improved performance and more consistent results even in high-throughput environments
  
• Created a new lightweight wait collection mode, used by default. This mode collects only the top non-CXPACKET wait, giving preference to blockers.
• To see full wait and task info, use the new option @get_task_info = 2
• New feature added that shows all sessions blocking those included in the base filter criteria, whether or not they would normally be returned.
• To see the feature in action, create a new database and have a session use it. Then fire up another session and try to drop the database. The second session will be blocked, and by default previous versions of the script would not have returned the blocking session because it is not active, nor does it have an open session. Version 9.55 will return the blocking session.
  
• Release candidate for the next "official" version. Please test!
  

 

Who is Active v9.07

• Flipped the version to v9.0!
• Added nodeId information when collecting CXPACKET waits
• Made the help output even nicer
• Added a new option, @show_system_spids which, when set to 1, makes the tool show system SPIDs as well as user SPIDs
• Changed @get_sleeping_spids to @show_sleeping_spids, in order to align with the naming used by the rest of the procedure
  

 

Who is Active v8.99

• Added additional workarounds for cases where sys.dm_exec_requests does not output proper or meaningful statement offsets. The latest issues I've identified occur when a query is calling a scalar UDF. The offsets and the sql_handle are not updated in a single operation, so it is possible to get strange results. I've added a couple of checks to try to avoid this in most cases, but the issue will not actually be solved until the SQL Server team fixes it in the engine.
• Want it fixed? Vote here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=478601
  

 

Who is Active? v8.96

• Dynamic sort ordering
• Removed @sort_column and @sort_column_direction parameters.
• Replaced with @sort_order parameter, which accepts a list of columns and sort directions (completely injection-safe, of course)
• "Not" filters
• Added new @not_filter and @not_filter_type parameters which behave the same as the @filter and @filter_type but do the opposite. Great for when you have service accounts, etc, that you don't want to see information about
• Improved the online help (@help=1) option
• Added a second table containing all of the output columns
• Improved the layout of the first table
  

 

Who is Active? v8.89

•  Removed reference to SQL Server 2008-specific DMV column (parent_task_address)
  
• Tasks and requests are now related via kpid from sysprocesses
  

 

Who is Active? v8.88

• Added @get_sleeping_spids option
• 0 gets no sleeping SPIDs (feature requested by Alvaro Mosquera)
• 1 (default) gets sleeping SPIDs only if they are holding an open transaction
• 2 gets all sleeping SPIDs (feature requested by a few people over the past couple of months)
  
• Added @format_output option 2, the "Aaron Bertrand" option (proper formatting for fixed-width fonts)
  
• Re-wrote the join condition to find tasks associated with requests--now using task_address rather than request_id
• Fixed a bug where MARS connections and other situations cause more rows in the Connections DMV than I expected, which created a PK error on one of the temp tables
• Many thanks to Michelle Ufford for reporting this bug!
  

 

Who is Active? v8.81

• Flipped the @get_plans options changed in v8.75 so that 1 now gets the plan based on the current running statement (after I tested it a bit I found that mode to be much more useful than the other mode when using stored procedures). 2 now gets the full plan.
• Modified the timeout code for getting plans and query text, to better handle other errors that might occur (now sends back an error message instead)
• Added a link to this downloads section so that you can more easily find new "Beta" builds!

Who is Active? v8.77

Fixed two bugs:

• Was casting SUM(context_switches) and SUM(physical_io) for the session into an INT, causing an overflow problem (thanks, Sankar Reddy!)
• Note to self: [some int value] + [some int value] may be greater than [max int value]
  
• Was incorrectly dividing used memory KB by 8192 instead of 8 to get the number of used pages
  

 

Who is Active? v8.75

Added a new suboption to @get_plans:

• As before, an argument value of 0 will cause plans to not be fetched, and a value of 1 will cause plans to be fetched based on the plan_handle associated with the request.
• The modification is that a value of 2 willh cause the plans to be fetched based on both the plan_handle and the statement offsets associated with the request.

Using a value of 1--pulling the plan based only on the plan_handle--you may see that the generated plan is the plan for the entire stored procedure or batch currently running, and not just the statement that is currently active. Using option 2 you may be able to get more targeted plans, that will correspond directly to the statement you see in the sql_text column.

I have not thoroughly tested this change. Please give it a try and let me know if you see anything strange or unexpected--in either a good way or a bad way.

 

Who is Active? v8.74

Further tweaks to yesterday's RUNNABLE enhancement. Fixed a bug where the number of active tasks was miscounted in some cases. Also renamed the "threads" column to "tasks" to be more specific about what it is really counting.

Who is Active? v8.72

• Modified wait_info to show tasks on the runnable queue. These will show up with wait type "RUNNABLE"

 

Who is Active? v8.71

• Added program_name to default output
• Removed @spid parameter, replaced with flexible filter options
• @filter_type - allows user to specify session, database, host, login, or program
• @filter - The actual text to filter; supports wildcards

Two minor bug fixes for v8.69:

1. Would fail for sessions running with ANSI_PADDING or QUOTED_IDENTIFIERS turned off
2. Would very rarely fail due to an improperly formed join to sys.dm_exec_query_memory_grants

Both of these should be fixed in this version.

 

Who is Active? v8.67

Online help: @help = 1

• self-explanatory
  

Find block leaders: @find_block_leaders = 1

• Returns a column called "blocked_session_count" that is the result of a count, starting from each blocker and walking down the entire blocking chain.  Each blocker will have the count of all blocked sessions down the chain, including those that are blocked by SPIDs the blocker is blocking

@output_column_list supports simple wildcards

• % and _ are now allowed.  So you can do, e.g.: @output_column_list = '[tran%] [%]'