Search results matching tag 'performance monitoring'

[New England] SQL Saturday #203: April 5-6, Cambridge MA

Adam Machanic — Tue, 29 Jan 2013 16:10:00 GMT

SQL Saturday returns to the Boston area this April, with what is certain to be an exceptional speaker and session lineup. (The actual schedule will be posted soon, but in the meantime you can see the submitted sessions.)

The free event ($10 if you'd like to eat lunch) will take place on Saturday, April 6. Highly recommended for anyone in the area who is interested in bettering your database skills!

Friday, April 5, as a lead in to the Saturday event, I'll be delivering my popular full-day "No More Guessing!" seminar. This seminar teaches a solid, proven performance troubleshooting methodology designed to help you quickly find the actual root cause of performance problems. Full information is available on the registration site.

This seminar has sold out at events like PASS Summit and SQLBits, and it is being offered in Boston at the discounted price, so book early to avoid disappointment.

Huge thanks to Mike Hillwig for taking the lead on putting together this event. Looking forward to seeing many of you there!

Who is Active v11.11

adam machanic — Thu, 22 Mar 2012 21:07:52 GMT

Fixes the following issues:

Bug with transaction information not working for databases with "th" in their name
Bug with very large CPU times causing an overflow exception on SQL Server 2005
Excessive tempdb utilization caused by large string concatenation
Added host_process_id to additional_info collection

Who is Active v11.03

adam machanic — Fri, 08 Jul 2011 15:31:21 GMT

Contains fixes for a couple of minor bugs that existed in v11.00.

An in-depth analysis of SQL Sentry's overhead

AaronBertrand — Tue, 05 Jul 2011 13:30:00 GMT

One of my first major projects at SQL Sentry was framed shortly after I joined the company: to publish an analysis of the overhead that Performance Advisor and Event Manager place on both the server(s) being monitored and on the server(s) doing the monitoring. Every vendor wants to sell you on the "zero impact" and "no footprint" lines, but we all know that it is not possible for you to accurately measure performance on a server without also causing at least some performance degradation by virtue of performing said monitoring. So what does "zero impact" really mean in those cases? Most vendors won't tell you, and I can understand why: there are so many variables and "what ifs" involved that they couldn't possibly tell you - at least not with a straight face.

As I would tend to advise you even from an impartial, outside perspective (regardless of the solution being researched), the only way you can get the absolute truth about impact is to test it for yourself, in your own environment, using your hardware, data, network, usage patterns, etc. On the other hand, we realize that you may not have an environment yet, or you may not have a suitable replica of production (we all know how most folks feel about testing in production). We know this because, even with trial versions available and frequently downloaded, two of the most common questions we get about our software are "how much overhead will this cause on the monitored instance?" and "what kind of server(s) will we need for the monitoring components?"

To help answer this question without all of the effort that might be required on your part, the paper sets out to show, in our simulated environment, roughly how much additional load is placed on a single monitored server, and the total load placed on the monitoring servers when they are watching 1, 10, 25, 50 and 100 SQL Server instances. We tried to make the test environment as generic as possible, with separate servers for each monitoring component (to isolate and measure the resource impact of each), and a virtual environment of over 100 servers (we went virtual here to ease creation and re-creation of the environment, and also for the obvious budgetary benefits). Now, your environment may be quite different, so the numbers shown in the paper may not necessarily reflect your exact scenario. But they should be close enough to give you a general idea of what to expect from our software.

This is the first of many impact studies we will be conducting and making public. We are not afraid to show you our numbers, even in cases where they might not be the most flattering, because we can often learn as much from this process as you can. We are quite happy with the results of these tests, but we look forward to further tests where we measure the impact on a single monitored server with more variables and subject to heavier and more realistic workloads.

Download the paper (.doc, 2.1 MB)

Who is Active? v11.00

adam machanic — Wed, 27 Apr 2011 04:20:03 GMT

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:

Would fail for sessions running with ANSI_PADDING or QUOTED_IDENTIFIERS turned off
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%] [%]'

Who is Active? v10.00

adam machanic — Thu, 21 Oct 2010 21:00:03 GMT

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:

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.

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.

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

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:

Would fail for sessions running with ANSI_PADDING or QUOTED_IDENTIFIERS turned off
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%] [%]'

Who is Active? v10.00: DMV Monitoring Made Easy

Adam Machanic — Thu, 21 Oct 2010 18:33:00 GMT

Today I am happy to release the newest official build of my Who is Active procedure: v10.00.

For those of you who haven't been following along, here are some quick facts on Who is Active:

Who is Active is a DMV-based monitoring stored procedure that uses 15 different views to show a large amount of data about what's running on your server
Who is Active was designed to be extremely flexible, and includes options to not only get different types of data, but also to change the output column list and sort order
Who is Active was designed with performance in mind 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
Who is Active is compatible with all versions of SQL Server after SQL Server 2005 RTM. It does require that the host database (generally master) is not set for SQL Server 2000 compatibility mode
Who is Active is free for most users. Refer to the license at the top of the procedure for more information
Who is Active has been a work in progress for over 3 years. My source control system contains over 600 revisions, and version 10.00 is over 3900 lines long -- by far the biggest and most complex single stored procedure I have ever worked with.

Click Here to Download Who is Active version 10.00

A bit more information, for those who are interested:

Most of the changes since the last stable build (v9.57) 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.

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.

I also added a few features along the way:

The name of the actual latch is shown when displaying a latch wait, not just the name of the wait (LATCH_EX, etc)
The transaction_writes column shows both the number of writes and the actual amount of log space consumed, in kB
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.

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.

Enjoy! And thanks again to everyone who has sent me feedback along the way. This would not be possible without you.

...

A few terms for the search engines:

sp_whoisactive whoisactive sp_who sp_who2 sp_who3 sp_who4 sp_who5

SSMS : Which parts do you love? Which parts do you hate?

AaronBertrand — Wed, 23 Jun 2010 00:56:00 GMT

I find myself categorizing a subset of SSMS features ranging from "must have" features to "cringe when they are mentioned." I'll try to summarize the standouts for you here, then ask you these questions: Which parts of SSMS do you rely on? Which do you wish were not there at all?

The "I almost always use it to get the task done." variety

SQL Server Agent's Job / Job Step Editor. While not pretty, the wizard does make quick work of creating jobs with several steps, multiple schedules, and various execution paths. (Let's just pretend the 2008 R2 version isn't a no-op due to a debilitating bug.)
Profiler. Similar to the job procedures, I am unlikely to ever get the hang of scripting a server-side trace from memory. So, I use the profiler UI to set up the trace details, then save the script for future use. If I have to run a slightly different server-side trace, I can use the same script with minimal changes; but in many cases it is just as quick to use the UI again and start from scratch. (Technically, I guess this isn't part of SSMS, but it is certainly a management UI that has had its share of criticism over the years.)
Right-click object > Modify, or Script as ALTER to New Window. This is by far the quickest way for me to get at the *current* version that is deployed, and when I am done testing my revisions against the dev server, I can then move the changes to source control. I see a lot of people using sp_helptext or manually selecting from syscomments / sys.sql_modules / OBJECT_DEFINITION(), but this can involve several more steps to get the code into suitable (and more importantly, maintainable) shape.
Registered servers. These are a god-send, especially since I've learned to export carefully constructed nodes and share them across all machines. I wish the tool in general was more consistent at remembering passwords depending on how I've initiated or switch a connection, but that's another story I suppose.

The "I will use it if I have to, but usually feel icky and prefer better alternatives." variety

Table Designer - occasionally I will use this tool (right-click a table, Design), but only at the beginning of projects, where I want to slightly adjust the column order in a new table. Even in that case, more often than not, I'll drop and re-create the table. For tables already in use, I always use DDL commands like ALTER TABLE, knowing that any new columns end up at the end of the table. And that's okay with me.
SQL Server Agent > Job History. This dialog has some real quirkiness; in our environment, it is always confused because it is being run on a desktop in one time zone against servers in another. I have been complaining about this since before SQL Server 2005 was released (though first officially documented in 2006; see Connect #124841). I also find the interface cumbersome to use and drill down - usually I get more complete and consumable information, and more immediately, from SQL Sentry's Event Manager - which also has the ability to store more history in the repository without bogging down production instances of msdb.
Select Top N Rows. This is a quick way to grab an arbitrary 1000 rows (or whatever number you customize) from a table. Sadly, it just blindly executes the query without giving you the opportunity to modify the column list, where clause or isolation level (personally, I prefer Script > As SELECT To > New Query Editor Window). Oh, and it won't necessarily put you in the context of the right database; instead, it uses a three-part name for the table, which can be frustrating if you try to add a simple join or subquery and forget which database you're really in.

The "I will not touch it with a 10-foot pole"! variety:

Activity Monitor. While this replacement of the useless view of the same name in previous iterations of the tool shows a lot of potential, and its motivation is in the right spot, it is in its infancy and has a lot of quirky behaviors. If I want a good look at what is going on in my server, I will use SQL Sentry's Performance Advisor - which does a great job of warning me about issues even when I'm not paying attention. For a really quick pulse of current activity or in environments where I haven't convinced them to use Performance Advisor, then I turn to Adam Machanic's sp_whoIsActive. For a really quick look, yes, Activity Monitor can be useful... but for very little incremental effort you can get to a lot more information without memorizing all of the DMVs.
This will be ridiculously shocking, but any of the features that allow you to shrink a database or a file. I acknowledge that there are some cases where shrinking a file is necessary, and I don't want to get into a religious battle about it. I just wish that the task was much more difficult and thought-provoking to complete. Rather than one button or checkbox, imagine if the process to shrink a file were as convoluted as setting up SQL Server? There is one case where I'd applaud a longer and more cumbersome set of steps to accomplish a simple task.
Most other designers / wizards. Including, but not limited to:

View Designer
Query Designer
Edit Top N Rows (formerly Open Table)
New/Edit Database
New/Edit Login

Go ahead, search Connect. There are dozens of bugs against these dialogs, and few if any will ever be fixed. I can quote myself on twitter from earlier tonight: "Never send a UI to do a DDL's job." As I've explained above, this isn't always true; but in most cases the DDL is going to be more predictable and also infinitely more repeatable.

The "Wait, why isn't that feature there?" variety

I'm cheating here a little bit, but there are some noticeable omissions from Management Studio that I can't even complain about because they never bothered to create them. UIs to support features that are cumbersome to set up using code alone, such as Service Broker and Extended Events (they are gradually getting to others such as Mirroring and of course the new DACPAC stuff). Or things that are long overdue but are otherwise covered by great 3rd party add-ins, like Mladen Prajdic's SSMS Tools Pack.

The "Oh yeah, I didn't even think of that one!" variety

It is a pretty vast application and there are probably several features I didn't even think to mention. So again, I'll ask: which parts of SSMS can you not live without? Which ones would you throw off a pier if you were in control?