Search results matching tags 'who is active' and 'performance monitoring'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=who+is+active,performance+monitoring&orTags=0Search results matching tags 'who is active' and 'performance monitoring'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Who is Active v11.11http://sqlblog.com/files/folders/beta/entry42453.aspxThu, 22 Mar 2012 21:07:52 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42453adam machanic<p>Fixes the following issues:</p><ul><li>Bug with transaction information not working for databases with "th" in their name</li><li>Bug with very large CPU times causing an overflow exception on SQL Server 2005</li><li>Excessive tempdb utilization caused by large string concatenation</li><li>Added host_process_id to additional_info collection<br></li></ul>Who is Active v11.03http://sqlblog.com/files/folders/beta/entry36726.aspxFri, 08 Jul 2011 15:31:21 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:36726adam machanicContains fixes for a couple of minor bugs that existed in v11.00.<br>Who is Active? v11.00http://sqlblog.com/files/folders/release/entry35240.aspxWed, 27 Apr 2011 04:20:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:35240adam machanic<p>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:</p>
<ul><li>Server activity
collection, including data about currently running T-SQL, server
resources consumed by the request, and query plan collection</li><li>Real-time wait statistics collection and blocker reporting</li><li>Delta collection mode, in order to find out what processes are doing over time</li><li>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</li><li>Ability to collect to a table, rather than sending the data back as a rowset</li><li>An online help system to help you figure out what options are available. Use the @help=1 option to hit the ground running.</li></ul>
<p>License:</p>
<p>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. </p>
<p>Donate! Support this project: <a href="http://tinyurl.com/WhoIsActiveDonate">http://tinyurl.com/WhoIsActiveDonate</a>&nbsp; <br></p><hr>
<p><br></p>
<p>Change log for the most recent several versions: </p>
<p>&nbsp;</p><p>Who is Active v11.00 </p><ul><li>Added wait information for OLEDB/linked server waits</li><li>Wait
collection will now "downgrade" to get_task_info = 1 style data if no
other information is available in get_task_info = 2 mode</li><li>Fixed sort order bug w/ multicolumn sorts</li><li>Added header information to online help</li><li>Added a login_time column to the output</li><li>The duration for sleeping sessions is now the sleep time, rather than the time since login<br></li></ul><p>&nbsp;</p><p>Who is Active v10.99</p><ul><li>CPU deltas can now use real-time thread-based metrics for more accurate data (use both @delta_interval and @get_task_info = 2) <br></li><li>command_type information added to [additional_info] column for active requests<br></li><li>Query plans that are not able to be rendered due to XML data type limitations will now be returned in an encapsulated text form<br></li><li>Fixed bug where system processes were sometimes reporting start times in the future (thanks, Neil Hambly!)</li><li>Fixed bug where @get_locks sometimes failed with a constraint error (thanks, various reporters!)<br></li></ul><p>&nbsp;</p><p>Who is Active v10.83</p><ul><li>Modified elapsed time logic to retrieve more accurate timing information for system SPIDs, in many cases<br></li><li>Fixed bug where @get_task_info = 2 was adding an "N" before some waits</li><li>Fixed bug where tempdb_allocations was being incorrectly calculated for active requests<br></li></ul><p>&nbsp;</p><p>Who is Active v10.76</p><blockquote><p><b>Enhancements</b> <br></p></blockquote><ul><li><b>SQL Agent job info</b>
(job name and step name) is now included in the additional_info column
(use @get_additional_info = 1) (thanks, Argenis Fernandez!)<br></li><li>If there is a lock wait, <b>information about the blocked object</b> (name,
schema name, and ID) is now included in the additional_info column (use
both @get_additional_info = 1 and @get_task_info = 2)</li><li>Service Broker <b>activated tasks are now shown by default</b>, without
using @show_system_spids mode. The program_name column contains the
queue_id</li><li>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 </li></ul><blockquote><b>Updates</b><br></blockquote><ul><li>Removed workaround to handle MARS bug documented here: <a href="http://connect.microsoft.com/SQLServer/feedback/details/490178/request-id-in-sys-dm-os-tasks-wrong-when-using-mars" target="_blank">http://connect.microsoft.com/SQLServer/feedback/details/490178/request-id-in-sys-dm-os-tasks-wrong-when-using-mars</a> ...
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!)<br>
</li><li>Modified the way transaction data is collected; @get_transaction_info should now perform better than it previously did
</li><li>Modified central collection mechanism to read fewer rows from sysprocesses on each pass.<br></li></ul><blockquote><b>Bug Fixes</b><br></blockquote><ul><li>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<br>
</li><li>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!)<br>
</li><li>Fixed bug where @get_task_info = 0 erroneously collected blocking information</li><li>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!)</li></ul>&nbsp;<br>
<p>Who is Active v9.98 (10.00 Release Candidate)<br></p>
<ul><li><b>Added new option, @get_additional_info</b>:
Returns a column called [additional_info] that contains various
non-performance-related information sourced from the sessions and
requests DMVs</li><li><b>Fixed @get_avg_time</b>: This option had been broken for several versions. (thanks, Ola Hallengren)</li><li><b>Workspace memory greatly decreased</b>:
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.</li><ul><li><b>NOTE</b>: 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. <b>Please </b>give it a try and <b>let me know your results</b> as soon as possible!<br></li></ul></ul>
<p>&nbsp;</p>
<p>Who is Active v9.90</p>
<ul><li>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)<br></li></ul>
<p>&nbsp;</p>
<p>Who is Active v9.89</p>
<ul><li>Fixed a bug where wait types were prefixed with an N in the wait_info column</li><li>Changed
the filter for system SPIDs on sysprocesses to use the hostprocess
column rather than hostname (thanks, Dan [last name unknown] and Erland
Sommarskog)<br></li></ul>
<p>&nbsp;</p>
<p>Who
is Active v9.87</p>
<ul><li>First stab at international database support</li><ul><li>Switched
from VARCHAR to NVARCHAR almost everywhere appropriate</li><li>sql_text,
sql_command, locks, login_name, wait_info, database_name, and
other areas should all show the full set of available characters</li><li>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</li><li>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. <b>This is something I need your help with.</b> 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.</li></ul><li>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</li><ul><li>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<br></li></ul></ul>
<p>&nbsp;</p>
<p>Who
is Active v9.72</p>
<ul><li>Removed reference to
sys.dm_exec_query_memory_grants; now getting granted query memory info
from sys.dm_exec_requests</li><ul><li>Who is Active is now compatible
with all versions of SQL Server 2005 and SQL Server 2008<br></li><li>Thanks
for testing help, Mladen Prajdic<br></li></ul></ul>
<p>&nbsp;</p>
<p>Who
is Active v9.71</p>
<ul><li>Fixed a bug where session_id was being
converted to TINYINT rather than SMALLINT (thanks, Linchi Shea)</li><li>Fixed
a bug where self-blocking sessions in sysprocesses were incorrectly
being shown (thanks, Jason Pease)</li><li>Fixed a bug where block
leaders was causing a recursion overflow (thanks, Sankar Reddy)<br></li></ul>
<p>&nbsp;</p>
<p>Who
is Active v9.68</p>
<ul><li>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)</li><li>Fixed a bug where ignoring CXPACKET waits would cause other
wait types to not bubble up in the default @get_task_info = 1 mode</li><li>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</li><li>Now showing the actual latch type for
LATCH_* waits<br></li></ul>
<p>&nbsp;</p>
<p>Who
is Active v9.62 </p>
<ul><li>Further improved performance of the default
"lightweight" wait collection mode (@get_task_info = 1).</li><li>Changed
the name of the tempdb_writes column to tempdb_allocations (thanks
Linchi Shea)<br></li></ul>
<p>&nbsp;</p>
<p>Who is Active v9.59</p>
<ul><li>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</li><li>Added
log used kB information to the transaction_writes column<br></li></ul>
<p>&nbsp;</p>
<p>Who
is Active v9.55<br></p>
<ul><li>Massive re-work of the core queries,
resulting in <b>greatly improved performance</b> and more consistent
results even in high-throughput environments<br></li><li>Created a new<b>
lightweight wait collection mode</b>, used by default. This mode
collects only the top non-CXPACKET wait, giving preference to blockers.</li><ul><li>To
see full wait and task info, use the new option @get_task_info = 2</li></ul><li>New
feature added that<b> shows all sessions blocking those included in the
base filter criteria</b>, whether or not they would normally be
returned.</li><ul><li>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.<br></li></ul><li>Release candidate for the next
"official" version. <b>Please test!</b><br></li></ul>
<p>&nbsp;</p>
<p>Who is
Active v9.07 <br></p>
<ul><li>Flipped the version to v9.0!</li><li>Added
nodeId information when collecting CXPACKET waits</li><li>Made the help
output even nicer</li><li>Added a new option, @show_system_spids<b> </b>which,
when set to 1, makes the tool show system SPIDs as well as user SPIDs</li><li>Changed
@get_sleeping_spids to @show_sleeping_spids, in order to align with the
naming used by the rest of the procedure<br></li></ul>
<p>&nbsp;<br></p>
<p>Who
is Active v8.99</p>
<ul><li>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.</li><ul><li>Want it fixed? Vote
here: <a href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=478601">https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=478601</a><br></li></ul></ul>
<p>&nbsp;</p>
<p>Who
is Active? v8.96</p>
<ul><li>Dynamic sort ordering</li><ul><li>Removed
@sort_column and @sort_column_direction parameters.</li><li>Replaced
with @sort_order parameter, which accepts a list of columns and sort
directions (completely injection-safe, of course)</li></ul><li>"Not"
filters</li><ul><li>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</li></ul><li>Improved
the online help (@help=1) option</li><ul><li>Added a second table
containing all of the output columns</li><li>Improved the layout of the
first table<br></li></ul></ul>
<p>&nbsp;</p>
<p>Who is Active? v8.89</p>
<ul><li>&nbsp;Removed
reference to SQL Server 2008-specific DMV column (parent_task_address)<br></li><ul><li>Tasks
and requests are now related via kpid from sysprocesses<br></li></ul></ul>
<p>&nbsp;</p>
<p>Who
is Active? v8.88</p>
<ul><li>Added @get_sleeping_spids option</li><ul><li>0
gets no sleeping SPIDs (feature requested by Alvaro Mosquera)</li><li>1
(default) gets sleeping SPIDs only if they are holding an open
transaction</li><li>2 gets all sleeping SPIDs (feature requested by a
few people over the past couple of months)<br></li></ul><li>Added
@format_output option 2, the "Aaron Bertrand" option (proper formatting
for fixed-width fonts)<br></li><li>Re-wrote the join condition to find
tasks associated with requests--now using task_address rather than
request_id</li><li>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</li><ul><li>Many thanks to Michelle Ufford for reporting
this bug!<br></li></ul></ul>
<p>&nbsp;</p>
<p>Who is Active? v8.81</p>
<ul><li>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.</li><li>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)</li><li>Added a link to this
downloads section so that you can more easily find new "Beta" builds!</li></ul>
<br>
<p>Who
is Active? v8.77</p>
<p>Fixed two bugs:</p>
<ul><li>Was
casting SUM(context_switches) and SUM(physical_io) for the session into
an INT, causing an overflow problem (thanks, Sankar Reddy!)</li><ul><li>Note
to self: [some int value] + [some int value] may be greater than [max
int value]<br></li></ul><li>Was incorrectly dividing used memory KB by
8192 instead of 8 to get the number of used pages<br></li></ul>
<p>&nbsp;</p>
<p>Who
is Active? v8.75 <br></p>
<p>Added a new suboption to @get_plans:</p>
<ul><li>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.</li><li>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. </li></ul>
<p>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.</p>
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.
<p>&nbsp;</p>
<p>Who is Active?
v8.74 <br>
</p>
<p>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.<br></p>
<br>
<p>Who is Active? v8.72<br>
</p>
<ul><li>Modified wait_info to show tasks on the runnable queue. These
will show up with wait type "RUNNABLE"</li></ul>
<p>&nbsp; <br></p>
<p>Who is
Active? v8.71 <br>
</p>
<ul><li>Added program_name to default output</li><li>Removed @spid
parameter, replaced with flexible filter options</li><ul><li>@filter_type
- allows user to specify session, database, host, login, or program</li><li>@filter
- The actual text to filter; supports wildcards</li></ul></ul>
<br>
<p>Two minor bug fixes for v8.69:</p>
<ol><li>Would fail for sessions running with ANSI_PADDING or
QUOTED_IDENTIFIERS turned off</li><li>Would very rarely fail due to an
improperly formed join to sys.dm_exec_query_memory_grants</li></ol>
<p>Both of these should be fixed in this version.</p>
<p>&nbsp;</p>
<p>Who is Active? v8.67</p>
<p>Online help: @help = 1</p>
<ul><li>self-explanatory<br></li></ul>
<p>Find block leaders: @find_block_leaders = 1 </p>
<ul><li>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.&nbsp;
Each blocker will have the count of all blocked sessions down the
chain, including those that are blocked by SPIDs the blocker is blocking</li></ul>
<p>@output_column_list supports simple wildcards</p>
<ul><li>% and _ are now allowed.&nbsp; So you can do, e.g.:
@output_column_list = '[tran%] [%]'</li></ul>
<p>&nbsp;</p>Who is Active? v10.00http://sqlblog.com/files/folders/release/entry29675.aspxThu, 21 Oct 2010 21:00:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:29675adam machanic<p>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:</p><ul><li>Server activity
collection, including data about currently running T-SQL, server
resources consumed by the request, and query plan collection</li><li>Real-time wait statistics collection and blocker reporting</li><li>Delta collection mode, in order to find out what processes are doing over time</li><li>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</li><li>Ability to collect to a table, rather than sending the data back as a rowset</li><li>An online help system to help you figure out what options are available. Use the @help=1 option to hit the ground running.</li></ul><p>License:</p><p>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. </p><p>&nbsp;</p><p>Change log for the most recent several versions: </p><p>&nbsp;</p><p>Who is Active v9.98 (10.00 Release Candidate)<br></p><ul><li><b>Added new option, @get_additional_info</b>:
Returns a column called [additional_info] that contains various
non-performance-related information sourced from the sessions and
requests DMVs</li><li><b>Fixed @get_avg_time</b>: This option had been broken for several versions. (thanks, Ola Hallengren)</li><li><b>Workspace memory greatly decreased</b>:
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.</li><ul><li><b>NOTE</b>: 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. <b>Please </b>give it a try and <b>let me know your results</b> as soon as possible!<br></li></ul></ul><p>&nbsp;</p><p>Who is Active v9.90</p><ul><li>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)<br></li></ul><p>&nbsp;</p><p>Who is Active v9.89</p><ul><li>Fixed a bug where wait types were prefixed with an N in the wait_info column</li><li>Changed
the filter for system SPIDs on sysprocesses to use the hostprocess
column rather than hostname (thanks, Dan [last name unknown] and Erland
Sommarskog)<br></li></ul><p>&nbsp;</p><p>Who
is Active v9.87</p><ul><li>First stab at international database support</li><ul><li>Switched
from VARCHAR to NVARCHAR almost everywhere appropriate</li><li>sql_text,
sql_command, locks, login_name, wait_info, database_name, and
other areas should all show the full set of available characters</li><li>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</li><li>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. <b>This is something I need your help with.</b> 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.</li></ul><li>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</li><ul><li>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<br></li></ul></ul><p>&nbsp;</p><p>Who
is Active v9.72</p><ul><li>Removed reference to
sys.dm_exec_query_memory_grants; now getting granted query memory info
from sys.dm_exec_requests</li><ul><li>Who is Active is now compatible
with all versions of SQL Server 2005 and SQL Server 2008<br></li><li>Thanks
for testing help, Mladen Prajdic<br></li></ul></ul><p>&nbsp;</p><p>Who
is Active v9.71</p><ul><li>Fixed a bug where session_id was being
converted to TINYINT rather than SMALLINT (thanks, Linchi Shea)</li><li>Fixed
a bug where self-blocking sessions in sysprocesses were incorrectly
being shown (thanks, Jason Pease)</li><li>Fixed a bug where block
leaders was causing a recursion overflow (thanks, Sankar Reddy)<br></li></ul><p>&nbsp;</p><p>Who
is Active v9.68</p><ul><li>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)</li><li>Fixed a bug where ignoring CXPACKET waits would cause other
wait types to not bubble up in the default @get_task_info = 1 mode</li><li>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</li><li>Now showing the actual latch type for
LATCH_* waits<br></li></ul><p>&nbsp;</p><p>Who
is Active v9.62 </p><ul><li>Further improved performance of the default
"lightweight" wait collection mode (@get_task_info = 1).</li><li>Changed
the name of the tempdb_writes column to tempdb_allocations (thanks
Linchi Shea)<br></li></ul><p>&nbsp;</p><p>Who is Active v9.59</p><ul><li>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</li><li>Added
log used kB information to the transaction_writes column<br></li></ul><p>&nbsp;</p><p>Who
is Active v9.55<br></p><ul><li>Massive re-work of the core queries,
resulting in <b>greatly improved performance</b> and more consistent
results even in high-throughput environments<br></li><li>Created a new<b>
lightweight wait collection mode</b>, used by default. This mode
collects only the top non-CXPACKET wait, giving preference to blockers.</li><ul><li>To
see full wait and task info, use the new option @get_task_info = 2</li></ul><li>New
feature added that<b> shows all sessions blocking those included in the
base filter criteria</b>, whether or not they would normally be
returned.</li><ul><li>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.<br></li></ul><li>Release candidate for the next
"official" version. <b>Please test!</b><br></li></ul><p>&nbsp;</p><p>Who is
Active v9.07 <br></p><ul><li>Flipped the version to v9.0!</li><li>Added
nodeId information when collecting CXPACKET waits</li><li>Made the help
output even nicer</li><li>Added a new option, @show_system_spids<b> </b>which,
when set to 1, makes the tool show system SPIDs as well as user SPIDs</li><li>Changed
@get_sleeping_spids to @show_sleeping_spids, in order to align with the
naming used by the rest of the procedure<br></li></ul><p>&nbsp;<br></p><p>Who
is Active v8.99</p><ul><li>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.</li><ul><li>Want it fixed? Vote
here: <a href="https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=478601">https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=478601</a><br></li></ul></ul><p>&nbsp;</p><p>Who
is Active? v8.96</p><ul><li>Dynamic sort ordering</li><ul><li>Removed
@sort_column and @sort_column_direction parameters.</li><li>Replaced
with @sort_order parameter, which accepts a list of columns and sort
directions (completely injection-safe, of course)</li></ul><li>"Not"
filters</li><ul><li>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</li></ul><li>Improved
the online help (@help=1) option</li><ul><li>Added a second table
containing all of the output columns</li><li>Improved the layout of the
first table<br></li></ul></ul><p>&nbsp;</p><p>Who is Active? v8.89</p><ul><li>&nbsp;Removed
reference to SQL Server 2008-specific DMV column (parent_task_address)<br></li><ul><li>Tasks
and requests are now related via kpid from sysprocesses<br></li></ul></ul><p>&nbsp;</p><p>Who
is Active? v8.88</p><ul><li>Added @get_sleeping_spids option</li><ul><li>0
gets no sleeping SPIDs (feature requested by Alvaro Mosquera)</li><li>1
(default) gets sleeping SPIDs only if they are holding an open
transaction</li><li>2 gets all sleeping SPIDs (feature requested by a
few people over the past couple of months)<br></li></ul><li>Added
@format_output option 2, the "Aaron Bertrand" option (proper formatting
for fixed-width fonts)<br></li><li>Re-wrote the join condition to find
tasks associated with requests--now using task_address rather than
request_id</li><li>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</li><ul><li>Many thanks to Michelle Ufford for reporting
this bug!<br></li></ul></ul>
<p>&nbsp;</p><p>Who is Active? v8.81</p><ul><li>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.</li><li>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)</li><li>Added a link to this
downloads section so that you can more easily find new "Beta" builds!</li></ul><br><p>Who
is Active? v8.77</p><p>Fixed two bugs:</p><ul><li>Was
casting SUM(context_switches) and SUM(physical_io) for the session into
an INT, causing an overflow problem (thanks, Sankar Reddy!)</li><ul><li>Note
to self: [some int value] + [some int value] may be greater than [max
int value]<br></li></ul><li>Was incorrectly dividing used memory KB by
8192 instead of 8 to get the number of used pages<br></li></ul><p>&nbsp;</p><p>Who
is Active? v8.75 <br></p><p>Added a new suboption to @get_plans:</p><ul><li>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.</li><li>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. </li></ul><p>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.</p>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. <p>&nbsp;</p><p>Who is Active?
v8.74 <br>
</p><p>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.<br></p><br><p>Who is Active? v8.72<br>
</p>
<ul><li>Modified wait_info to show tasks on the runnable queue. These
will show up with wait type "RUNNABLE"</li></ul><p>&nbsp; <br></p><p>Who is
Active? v8.71 <br>
</p>
<ul><li>Added program_name to default output</li><li>Removed @spid
parameter, replaced with flexible filter options</li><ul><li>@filter_type
- allows user to specify session, database, host, login, or program</li><li>@filter
- The actual text to filter; supports wildcards</li></ul></ul><br>
<p>Two minor bug fixes for v8.69:</p>
<ol><li>Would fail for sessions running with ANSI_PADDING or
QUOTED_IDENTIFIERS turned off</li><li>Would very rarely fail due to an
improperly formed join to sys.dm_exec_query_memory_grants</li></ol>
<p>Both of these should be fixed in this version.</p>
<p>&nbsp;</p><p>Who is Active? v8.67</p>
<p>Online help: @help = 1</p>
<ul><li>self-explanatory<br></li></ul>
<p>Find block leaders: @find_block_leaders = 1 </p>
<ul><li>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.&nbsp;
Each blocker will have the count of all blocked sessions down the
chain, including those that are blocked by SPIDs the blocker is blocking</li></ul>
<p>@output_column_list supports simple wildcards</p>
<ul><li>% and _ are now allowed.&nbsp; So you can do, e.g.:
@output_column_list = '[tran%] [%]'</li></ul><p>&nbsp;</p>Who is Active? v10.00: DMV Monitoring Made Easyhttp://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspxThu, 21 Oct 2010 18:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:29677Adam Machanic<p>Today I am happy to release the newest official build of my <a href="http://tinyurl.com/WhoIsActive">Who is Active</a> procedure: <b>v10.00</b>. </p><p>For those of you who haven't been following along, here are some quick facts on Who is Active:</p><ul><li>Who is Active is a DMV-based monitoring stored procedure that<b> uses 15 different views to show a large amount of data about what's running on your server</b></li><li>Who is Active was designed to be <b>extremely flexible</b>, and includes options to not only get different types of data, but also to change the output column list and sort order</li><li>Who is Active was <b>designed with performance in mind</b> at every step; users report that under normal conditions response times are generally subsecond, with slightly longer response times on servers that are extremely taxed</li><li>Who is Active is<b> compatible with all versions of SQL Server after SQL Server 2005 RTM</b>. It does require that the host database (generally master) is not set for SQL Server 2000 compatibility mode</li><li>Who is Active is<b> free for most users</b>. Refer to the license at the top of the procedure for more information<br></li><li>Who is Active has been a work in progress for over 3 years. My source control system contains over 600 revisions, and <b>version 10.00 is over 3900 lines long</b> -- by far the biggest and most complex single stored procedure I have ever worked with.</li></ul><p>&nbsp;</p><p><a href="http://sqlblog.com/files/folders/29675/download.aspx"><b>Click Here to Download Who is Active version 10.00</b></a><br></p><p><br></p><p>A bit more information, for those who are interested:</p><p>Most of the changes since the last stable build (<a href="http://sqlblog.com/blogs/adam_machanic/archive/2009/12/03/who-is-active-v9-57-fast-comprehensive-dmv-collection.aspx">v9.57</a>) have been bug fixes, and I would like to thank the many people who've sent me feedback over the past 10 months. As a result of fixing the various issues I am happy to say that this new version is quite solid, returning data even when the underlying DMVs aren't doing their jobs as advertised.<br></p><p>The most major enhancement is much better support for international character sets. When I first wrote the procedure I used VARCHAR for everything because, let's face it, I'm American and we know in our hearts that no one else in the world has any other language aside from that which we use. Well, luckily I was slapped into reality by a few users in far away lands such as Isreal and Sweden, and this version fully supports international character sets for everything except one feature where I couldn't get right-to-left encodings working properly. Stay tuned for a minor update to fix that discrepancy. </p><p>I also added a few features along the way:</p><ul><li>The name of the actual latch is shown when displaying a latch wait, not just the name of the wait (LATCH_EX, etc)</li><li>The transaction_writes column shows both the number of writes and the actual amount of log space consumed, in kB</li><li>An option called @get_additional_info was added. This option lets you collect information about the running session or request including connection settings like ANSI_NULLS, DATEFIRST, etc.</li></ul><p>I still need to fully document this thing, and it's a goal I hope to achieve before the end of the year. Bear with me, please. And in the meantime, feel free to ask me any questions either here or on Twitter.</p><p>Enjoy! And thanks again to everyone who has sent me feedback along the way. This would not be possible without you. </p><p>&nbsp;</p><p>... <br></p><p>A few terms for the search engines:</p><p>sp_whoisactive whoisactive sp_who sp_who2 sp_who3 sp_who4 sp_who5 <br></p>