THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |

Who is Active? v10.00

File Details
Downloads: 33136 File Size: 22kB
Posted By: Adam Machanic Views: 35726
Date Added: 21 Oct 2010

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.


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.


Change log for the most recent several versions:


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.


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%] [%]'


No comments exist for this file.
Privacy Statement