Search results matching tags 'who is active' and 'DMVs'

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.

Twenty Nine Days of Activity Monitoring (A Month of Activity Monitoring, Part 30 of 30)

Adam Machanic — Sat, 30 Apr 2011 14:00:00 GMT

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

Pop quiz: What happens when you promise to write 30 posts on a topic, but you don’t plan properly and lose a bit of steam toward the end?

Answer: A recap post, a day or two early! Woo hoo!

The month started with a few introductory posts. A history of monitoring (from my point of view), some information on Who is Active’s design philosophy, and a few words on the Who is Active license.

The key takeaways from these first posts? The monitoring story has been pretty poor for most of SQL Server’s history. It’s better now, but it’s still a lot of work. Who is Active was created to make things more accessible. It’s free, with a few restrictions designed to keep people who make money from monitoring from making money on Who is Active. Anyone else can and should use and enjoy.

Next I got new users up to speed. My post on installation covered security considerations, followed by a post on why you may not see as much data as you're used to when you first start using Who is Active. I continued with a post on the various options supported by the procedure (plus a nod to the online help mode) and another post describing the default output columns.

One of the more important posts of the month described how Who is Active treats active requests and sleeping sessions. This can be a confusing area for new users, since it's not always obvious what's going on unless you're looking in the right place at the output (the [status] column).

Once the refresher was finished I began covering some of the basic options exposed by the procedure. Filters were one of the first things I implemented, so it was a logical place to start (the "not" filters came much later). Seeing the query text is a key part of the procedure's functionality, and that was next on my list. If you're not sure what the [sql_text] column means, this post will set the record straight.

The ability to see the query text is nice, but so is the ability to get a query plan--and of course Who is Active supports that too. And since everything a query does is transactional, the procedure allows users to collect information about what those transactions are up to.

The next section of the month was all about query processing. I started with a couple of background posts: One on how the query processor works (at a somewhat high level), and another on what blocking really means.

Who is Active exposes two different modes that help with analysis of real-time waits. My 15th post of the month covered the default--lightweight--collection mode. My followup post covered the more extensive full waits collection mode.

Sometimes a query plan and wait information isn't quite enough to diagnose a performance issue. What if the query plan usually works well, but isn't performing properly only in this specific case? Who is Active has a feature to help you figure that out. And what if you need a bit more information on some of the settings that the request is using?

Mining wait information yields some amazing returns. One of the additional pieces of information that you can get is the actual name of the object that's causing a block. Another thing you can see is the exact node within a query plan where a task is currently doing work. You can (and should!) also use waits to figure out whether tempdb is properly configured. There are still more tricks you can play with waits, but they'll have to wait for a future post.

Like wait information, lock data can also be mined. However, there is so much locks information that the real challenge is rendering it in a human-readable manner. Who is Active does that using a special custom XML format. The procedure also helps with another type of blocker analysis, in the form of finding the "lead blocker."

Once you've figured out what information you're interested in, why not set up the output so that you can see the information the way you want to see it? Who is Active helps with this by allowing users to dynamically customize output columns and sort order in a few different ways. And while you could always run Who is Active interactively, that might get a bit dull. Automated data collection is a much nicer methodology in many cases.

Most of the numbers shown by Who is Active are cumulative. But oftentimes it's more interesting to compare data between two snapshots. The procedure can help you do that, using its delta mode.

What fun would a month of Who is Active be without an official release? v11.00 has numerous new features, several of which were perfected this month thanks to feedback I received on the various posts in the series. It's great to have such enthusiastic users! Some of these new features didn't make it into prior posts, and other existing features are a bit hidden. So I did a writeup covering the more important things that you may not have noticed while working with Who is Active.

I finished the month with a discussion on security for slightly tougher situations. I hope that the module signing technique will allow Who is Active to be used in a number of places where security auditing requirements have made things difficult.

And that's that. A month of activity monitoring with Who is Active. Thank you for reading! Next, this text is going to be edited, expanded in places, and put into a much more comprehensive form. Watch your RSS reader for more information.

Enjoy!

Access for All! (A Month of Activity Monitoring, Part 29 of 30)

Adam Machanic — Fri, 29 Apr 2011 14:00:00 GMT

This post is part 29 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

Early in the month I discussed basic security requirements for Who is Active. I mentioned the VIEW SERVER STATE permission and the fact that I consider it to be “a relatively low barrier to entry.”

But what if, in your organization, it’s not? Auditing requirements being what they are, you might be required to lock things down. And granting someone full and unrestricted VIEW SERVER STATE may simply not be an option.

Enter module signing. By securing Who is Active (or any other securable, for that matter) via inherited permissions, it’s often possible to get around auditing requirements, as long as the module itself has been reviewed. This is not at all a difficult thing to do, but in my experience most DBAs haven’t played much with signed modules. Today I’ll show you how quick and easy it can be to set things up.

Start by creating a certificate.

USE master
GO

CREATE CERTIFICATE WhoIsActive_Permissions
ENCRYPTION BY PASSWORD = '1bigHUGEpwd4WhoIsActive!'
WITH SUBJECT = 'Who is Active',
EXPIRY_DATE = '9999-12-31'
GO

Once you have a certificate in place, you can create a login from the certificate. The goal is to grant permissions, and to do that you need a principal with which to work; a certificate does not count. A login based on the certificate uses the certificate’s cryptographic thumbprint as its identifier. These logins are sometimes referred to as “loginless logins,” but I refer to them as “proxy logins” since that’s what they’re used for: proxies for the sake of granting permissions.

CREATE LOGIN WhoIsActive_Login
FROM CERTIFICATE WhoIsActive_Permissions
GO

The login can be granted any permission that can be granted to a normal login. For example, VIEW SERVER STATE:

GRANT VIEW SERVER STATE
TO WhoIsActive_Login
GO

Once the permission has been granted, the certificate can be used to sign the module—in this case, Who is Active. When the procedure is executed, a check will be made to find associated signatures. The thumbprint of the certificates and/or keys used to sign the module will be checked for associated logins, and any permissions granted to the logins will be available within the scope of the module—meaning that the caller will temporarily gain access.

ADD SIGNATURE TO sp_WhoIsActive
BY CERTIFICATE WhoIsActive_Permissions
WITH PASSWORD = '1bigHUGEpwd4WhoIsActive!'
GO

Getting to this step will be enough to allow anyone with EXECUTE permission on Who is Active to exercise most of its functionality. There are a couple of notes and caveats: First of all, every time you ALTER the procedure (such as when upgrading to a new version), the signature will be dropped and the procedure will have to be re-signed. You won’t have to create the certificate or the login again; you’ll just have to re-run that final statement. Second, you’ll only be able to use most of the functionality. Certain features, such as blocked object resolution mode, won’t operate properly, depending on whether the caller has access to the database in which the block is occurring. This may or may not be a problem—it depends on your environment and what users need to see—and Who is Active itself won’t throw an exception. An error message will be returned somewhere in the results, depending on what the user has tried to do.

If you would like to grant database-level permissions based on the certificate login so as to avoid these errors, that’s doable to. Just do something like:

USE AdventureWorks
GO

CREATE USER WhoIsActive_User
FOR LOGIN WhoIsActive_Login
GO

EXEC sp_addrolemember
'db_datareader',
'whoisactive_user'
GO

This will allow Who is Active to figure out what the various blocked or locked object names are. Since the login is just a proxy no one can actually log in and get direct access to read the data, so this isn’t something I consider to be a security risk. However, keep in mind that if anyone has the password for the certificate and sufficient privileges in master, a new module could be created and signed. Keep the password secure, and make sure to carefully audit to catch any infractions before they become a risk.

Security policy should never be a reason to limit your monitoring choices. Module signing is a powerful tool for Who is Active in addition to many other applications. I highly recommend studying it in detail in order to enhance your ability to provide high-quality, totally flexible, and completely secure solutions.

Who is Active's Hidden Gems (A Month of Activity Monitoring, Part 28 of 30)

Adam Machanic — Thu, 28 Apr 2011 14:00:00 GMT

This post is part 28 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

Over the past month this series has covered a lot of ground. After writing almost 30 blog posts on the stored procedure it’s interesting to look back and see the fact that it’s gotten much bigger and more complex than I realized. Kind of like watching a plant grow, you don’t notice the day-to-day changes until one day you go to water it and see that it’s taken over your entire garden.

A few things that I’ve added along the way have been especially useful, but there hasn’t been a good place to mention them thus far in the series. With just three posts left for the month, now is the time.

Always Show Blockers

In a post earlier this month I talked about filters. Filters let you decide exactly what you want to see, and what you not don’t want to see (“not” filters). But sometimes you have no choice: if you’re filtering so that you only see session 123, and it’s being blocked by session 456, you’ll also see information about session 456. The idea is that you should always get enough context to fully evaluate the problem at hand. Even if it means that you see more information than you were intending to see the first time around. You probably would have asked for 456 next anyway.

“RUNNABLE” Waits

In @get_task_info = 2 mode, you may see waits called “RUNNABLE.” This could strike you as an oddity, given that there is no such wait type in SQL Server. I wanted to show tasks on the runnable queue, and making up a fake wait type seemed like a reasonable way of accomplishing the task. In practice, it has worked extremely well—I’ve used this feature countless times to help understand scheduler contention on a SQL Server instance.

Accurate CPU Time Deltas

CPU time is a tricky metric. It gets handled by Who is Active’s delta mode, and has for several versions. But historically, never very well. The data simply isn’t represented in an easily-obtainable fashion in the core DMVs. Recently I decided to dig deeper into this problem and discovered that I could get better numbers from some of the thread-specific DMVs. They’re cumulative numbers, based on the lifetime of the entire thread—not too good for the usual Who is Active output. But for snapshot and delta purposes, just about perfect. Meaning that in v11.00 of Who is Active, you can see the [CPU] column show a value of 0, while the [CPU_delta] column shows a value in the thousands. It’s not a bug. It’s a feature. (It really is!)

Text Query Plans and the XML Demon

SQL Server 2005 introduced query plans as XML. Management Studio knew how to render these plans graphically. And we were able to pull the plans from DMVs. Life was great. Except, perhaps, when you actually wanted to view one of these plans, and you had to save the thing out to a .SQLPLAN file, close the file, then re-open it. That’s about three steps too many for my taste, so I was overjoyed when the Management Studio team decided to wire things up the right way in SQL Server 2008. Click on a showplan XML document, see a graphical plan. Simple as that.

Unfortunately, the XML data type has its own issues, including one particularly nasty arbitrary limitation that has to do with nesting depth. The idea is to make sure that XML indexes don’t crash and burn too often (not a big concern for me, given that I’ve never seen one used in a production environment—but I digress). The problem is that query plans are heavily nested. And to get that nice graphical plan workflow, SSMS needs the plan rendered as XML.

In prior versions of Who is Active I gave up and returned either an error or a NULL. But in v11.00 I decided to make things a bit better. If the nesting issue occurs, Who is Active will now return the plan as XML encapsulated in some other XML in a text format, along with instructions on how to view the plan. This won’t give you a nice one-click experience, but it will give you the ability to use Who is Active to see some of the bigger plans that are causing performance issues.

Service Broker Needs Love Too

One of the most interesting features of Service Broker is activation. But a vexing design choice on behalf of the Service Broker team was to make activation procedures launch as system sessions. This means, among other things, that prior versions of Who is Active filtered them right out of the default view. To see them you’d have to enable @show_system_spids. And then you’d have to ignore all of the other system stuff. And you’d get woefully bad time information (no, the activation process hasn’t been running for 25 days; that’s the last time you restarted the SQL Server instance). In Who is Active v11.00 this has been fixed. Service Broker activation processes are now displayed by default along with other user activity. And I found a way to fix the timing issue, thanks to some advice on Twitter from Remus Rusanu, one of the guys who originally worked on Service Broker. So if you’re using activation and monitoring with Who is Active, life is good.

Homework

The homework section of this series is officially closed. Your assignment is to go enjoy the rest of your day.

Who is Active v11.00 (A Month of Activity Monitoring, Part 27 of 30)

Adam Machanic — Wed, 27 Apr 2011 14:00:00 GMT

This post is part 27 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

Thanks to your feedback over the past month, I’ve managed to get a lot of work done on the next version of Who is Active.

So much work, in fact, that I’ve finished a new major release.

Click here to download Who is Active v11.00

There are numerous enhancements in this release. In no particular order:

CPU deltas use-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
Modified elapsed time logic to retrieve more accurate timing information for active system SPIDs
SQL Agent job info (job name and step name) is now included in the additional_info column (use @get_additional_info = 1)
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 and database_id associated with the activation procedure
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
Query plans that cannot be rendered due to XML type limitations are now returned in an encapsulated format, with instructions, rather than sending back an error
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
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
Fixed various bugs

I’ve written about several of these things over the past month, and there are a couple of posts left for the remainder of the month, so I won't elaborate here.

Thank you, thank you, and thank you again to everyone who has taken the time to give me feedback and/or report bugs! Who is Active would not be what it is without you!

I would especially like to thank Paul White. Paul has acted as my unofficial "lead QA engineer" for the past few versions. He's found some very interesting bugs in my code, in addition to pointing out some quirks in the DMVs that I wasn’t handling properly. Thanks, Paul, for the great job you’ve done!

One other thing I would like to point out is an addition to the header: a donation link. Several people recently have asked me how to donate, and I didn’t have a good answer. Now, I do.

Use this link to support the Who is Active project!

While I feel strange asking for money, the truth is that I’ve invested well over a thousand hours in the stored procedure, documentation, and support (I respond to EVERY e-mail I receive from Who is Active users). If you’ve been able to improve your day-to-day performance thanks to Who is Active, it would be great if you—or even better, your employer—could help support my work on the project. I’ve listed a recommended donation of $40 per installed instance, but please donate however much you feel Who is Active is worth to you and/or your organization.

Thanks again, and enjoy!

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

Delta Force (A Month of Activity Monitoring, Part 26 of 30)

Adam Machanic — Tue, 26 Apr 2011 14:00:00 GMT

This post is part 26 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

The CPU is pegged. Your hard disks are fried. Memory consumption is through the roof. Who is to blame?!?

Well that’s easy. Blame the session with the most CPU consumption. Blame the session doing the most I/O operations. Ask Who is Active what’s going on—it’ll tell you...

Clearly session 52 is the problem here. It’s consumed over 2,000,000 milliseconds of CPU time. Session 53, on the other hand, has consumed a paltry five seconds. No brainer, right?

Except that it’s not.

Session 52? It’s in a wait state. (WAITFOR, to be exact.) Not consuming any CPU time at all at the time I ran Who is Active. Session 53? Using plenty. So why the discrepancy? Does Who is Active have a major bug?

The fact is—and this will be obvious to a lot of you, so forgive me—most of the metrics reported by Who is Active are cumulative. They’re totals from the entire session, or the entire lifetime of the request (depending on what’s going on). In the case of session 52 in the image above, that much CPU time was consumed over a five-minute period prior to the point in time when I ran Who is Active. Session 53, on the other hand, is currently consuming CPU time. But how do I figure that out?

The answer: delta mode. This feature is something for which you can thank Jimmy May, who kept telling me about his “SQL Deltoids” script that he’d written back in the bad old days of SQL Server 2000. “If only there were a way to apply that script to the SQL Server 2005 DMVs,” he lamented one day. So I added the functionality into Who is Active. It’s quite simple, and surprisingly effective.

To use delta mode, simply invoke Who is Active’s @delta_interval option. Setting an interval will cause the procedure to execute its main logic branch, then wait the specified amount of time—a number of seconds between 1 and 255—before running the same main logic branch again. All of the numeric values that changed during that period will be compared, and the changed numbers will be output in a series of columns named with _delta at the end.

For example, here are the same two sessions as above, viewed in delta mode:

EXEC sp_WhoIsActive
@delta_interval = 5

During the five-second delta interval, session 52 consumed no CPU time. Session 53, on the other hand, consumed over 5,000 milliseconds of time. If the CPU is pegged, 52 is no longer contributing—53 is the session of interest.

Enabling delta mode will add some or all of the following columns to the output, depending on what other options have been set:

physical_io_delta
reads_delta
physical_reads_delta
writes_delta
tempdb_allocations_delta
tempdb_current_delta
CPU_delta
context_switches_delta
used_memory_delta

By leveraging these delta columns in conjunction with the @sort_order option, it’s easy to quickly see which sessions are currently consuming your valuable server resources—which is generally more interesting than seeing which sessions may have been consuming resources but are now waiting or sleeping.

Note: The various sessions and requests you'll see in delta mode pertain to the information captured after the wait interval. Information about requests that were running as of the first collection but are not running as of the second is discarded. New requests that started after the first collection will be shown, but with delta values of NULL. Locks, transactions, and other optional information, will also be captured only for the second collection, since no delta calculation is possible for those metrics (at least, not yet).

Homework

Delta mode, in its current form, is only really useful if you have requests that last longer than a second (at minimum—I usually do five or ten-second intervals). What kind of workload do you see on your servers? Is delta mode as it exists today something that you’re making use of (or that you’ll start making use of, now that I’ve written a post about it)?

Capturing the Output (A Month of Activity Monitoring, Part 25 of 30)

Adam Machanic — Mon, 25 Apr 2011 14:00:00 GMT

This post is part 25 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

Yesterday’s post was about configuring the output based on what you want to see. Today’s post is about taking that perfect output and persisting it.

There are many reasons that you might like to store the results of a Who is Active call to a table. Some of the real use cases that I’ve been told about by Who is Active users include:

Scheduled monitoring. Capturing the results of Who is Active calls in 5 or 10 minute intervals to see what’s happening on the database server throughout the day (or night)
Using it as part of a build process, to verify that the correct things are happening in the correct order at the correct times
Using it as part of an exception handling system that automatically calls Who is Active every time an error occurs, to snapshot the current state of the database instance

And there are various other use cases in addition to these. The point is that there are a number of reasons that you might want to capture the output.

Unfortunately, it’s not as simple a task as you might think. The first time I tried to make it work, I did something like:

CREATE TABLE #output
(
...
)

INSERT #output
EXEC sp_WhoIsActive

This approach failed miserably. If you try it, as I did, you’ll see the following error message:

Msg 8164, Level 16, State 1, Procedure sp_WhoIsActive, Line 3086
An INSERT EXEC statement cannot be nested.

Who is Active uses a number of INSERT EXEC statements, and they cannot be easily changed or removed, so for a while it seemed like all was lost. After a bit of brainstorming, however, I realized that I could simply build yet another INSERT EXEC into Who is Active—one that will insert into a table of your choice.

Of course, first you need a table. And if you’ve been reading this series you’re no doubt aware that the output shape returned by Who is Active is extremely dynamic in nature, and depends on which parameters are being used. So the first option I added was a method by which you can get the output schema. Two parameters are involved: If @return_schema is set to 1, the schema shape will be returned in an OUTPUT parameter called @schema. This is best shown by way of example:

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive
    @output_column_list = '[temp%]',
    @return_schema = 1,
    @schema = @s OUTPUT

SELECT @s

The idea is that you set up your Who is Active call with all of the options you’d like, then bolt on the @return_schema and @schema parameters. Here the column list is being restricted to only those columns having to do with tempdb. If you run this code, the SELECT will return the following result:

CREATE TABLE <table_name> ( [tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL)

This result can be modified by replacing the “<table_name>” placeholder with the name of the table you actually want to persist the results to. Of course this can be done either manually or automatically—after the call to Who is Active, the text is sitting in a variable, so a simple call to REPLACE is all that’s needed. That call could even be followed up by a call to execute the result and create the table...

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive
    @output_column_list = '[temp%]',
    @return_schema = 1,
    @schema = @s OUTPUT

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.monitoring_output')

EXEC(@s)

Of course now you probably want to put something into the table. Crazy! To do this, drop the @return_schema and @schema parameters and replace them with @destination_table—the name of the table into which the results should be inserted:

EXEC sp_WhoIsActive
@output_column_list = '[temp%]',
@destination_table = 'tempdb.dbo.monitoring_output'

Now the results of the call will be inserted into the destination table. Just remember that every time you change the Who is Active options, you’ll have to re-acquire the output shape. Even a small change, such as adding an additional column to the output list, will result in a catastrophic error.

EXEC sp_WhoIsActive
@output_column_list = '[session_id][temp%]',
@destination_table = 'tempdb.dbo.monitoring_output'

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

How far you take this feature depends on how creative you are. Some of you have come up with elaborate schemes, but I generally keep it simple. Something that I like to do is to set up a short semi-automated process by using Management Studio’s GO [N] option. I use this when I’m doing intense debugging, and will do something like:

DECLARE @s VARCHAR(MAX)

EXEC sp_WhoIsActive
    @format_output = 0,
    @return_schema = 1,
    @schema = @s OUTPUT

SET @s = REPLACE(@s, '<table_name>', 'tempdb.dbo.quick_debug')

EXEC(@s)
GO

EXEC sp_WhoIsActive
    @format_output = 0,
    @destination_table = 'tempdb.dbo.quick_debug'

WAITFOR DELAY '00:00:05'
GO 60

This will first create a table in tempdb, after which it will collect the results every five seconds for a five-minute period. I set @format_output to 0 in order to get rid of the text formatting so that I can more easily work with the numeric data. The results can be correlated to performance counters or other external information using the [collection_time] column, which was added to Who is Active specifically to support automated data collection.

Homework

Share your experiences with Who is Active’s data collection feature. How are you using it? Have you hit any problems or roadblocks? Any awesome success stories? A few sentences will be great, and more is fine if you’re in the mood to tell a story.

The Output of Your Dreams (A Month of Activity Monitoring, Part 24 of 30)

Adam Machanic — Sun, 24 Apr 2011 14:00:00 GMT

This post is part 24 of a 30-part series about the Who is Active stored procedure. A new post will run each day during the month of April, 2011. After April all of these posts will be edited and combined into a single document to become the basis of the Who is Active documentation.

Early in the process of creating Who is Active it became clear that there was no way everyone was going to agree.

With each release I received requests to move some column so that it would show up on the lefthand side, or to change the sort order, or to make some other modification that someone felt was necessary to help them more easily digest the data. The problem: it was impossible to accommodate all of these requests. So I decided to go with a self-service model.

In order to allow users to dynamically customize the output (without touching any code), the stored procedure exposes three options: @output_column_list, @sort_order, and @format_output. Each of these is discussed in the following sections.

@output_column_list controls not only whether or not certain columns will be shown in the output, but also the order in which the columns are displayed. The correct argument is a list of bracket-delimited column names (or partial names with wildcards). Delimiters are not necessary (use whatever delimiter you like, or none at all; they’re ignored). The key to successfully using this option is to remember that inclusion of columns in the output is additive: many columns (such as [additional_info]) are only added to the output if both the correct options are enabled for the stored procedure and the columns are included in the column list. If you start modifying the list and don’t take this into account, you may not see the columns you’re expecting when you go back later and start changing options. To keep things flexible, make sure to use wildcard columns, especially a generic wildcard ([%]) at the end.

Using the column list can be as simple as specifying the exact columns you’re interested in:

EXEC sp_WhoIsActive
@output_column_list = '[tempdb_allocations][tempdb_current]'

Notice that no delimiter is used here. Again, any extraneous text aside from the column specifiers is ignored, so the following call is equivalent:

EXEC sp_WhoIsActive
@output_column_list = 'this[tempdb_allocations]is[tempdb_current]ignored'

Easier than specifying exact column names is to use wildcards that match the pattern of the columns you’re interested in:

EXEC sp_WhoIsActive
@output_column_list = '[tempdb%]'

All three of these calls will yield the same output, similar to the following:

Of course, this will return only these two columns. Generally I’ll use the column list feature just to move things around so that I don’t have to do as much scrolling, and in those cases I almost always want everything else, too. That’s where the generic wildcard comes into play:

EXEC sp_WhoIsActive
@output_column_list = '[tempdb%][%]'

Now the tempdb-specific columns appear on the lefthand side, and all of the other columns follow, in a default order.

The next option we’ll cover is @sort_order. This option controls the order of the rows output by the stored procedure. Like @output_column_list, the input is a list of bracket-delimited column names. In this case, wildcards are not supported, but the keywords ASC[ENDING] and DESC[ENDING] are supported after the column name.

The following call returns data sorted by [login_name] ascending (ascending is default, so the keyword is optional), with ties broken by [session_id] descending:

EXEC sp_WhoIsActive
@sort_order = '[login_name][session_id] DESC'

As before, delimiters are optional and are ignored. Please note that the current betas of Who is Active have bug where multi-column sorts like this one are not properly honored. That issue will be fixed in the next version of Who is Active.

Sorting is especially useful when doing any kind of comparison of the various requests currently running. I use it extensively in block leader mode ([blocked_session_count] DESC), and it is quite handy in delta mode—which will be covered in a post in a few days.

The final option this post will cover is @format_output. This one is based on the fact that Who is Active is designed to leverage SSMS as a “graphical user interface” of sorts. Query text is sent back in an XML format, numbers are formatted as right-justified strings, and elapsed time is formatted in the more easily-digestible [dd hh:mm:ss.mss] form.

If you’ve been using Who is Active for a while you’ve probably noticed the right-justified numbers:

This format was suggested fairly early on by Aaron Bertrand, and is one of my favorite things about the stored procedure. It makes it much easier to pick out bigger numbers when you’re looking at a large set of data. By default, SSMS uses a non-fixed width font for grid results, so the default argument to @format_output, 1, takes this into consideration. But some people—like Aaron Bertrand—change the SSMS settings and use a fixed width font instead. If you’re one of these people you can use an argument of 2 to get nicely-formatted numbers. If you don’t change the argument you might notice that the numbers don’t seem to properly line up when you’re working with a set of data containing numbers of greatly differing size.

Still other people don’t like the right-justified numbers or are doing collection to a table (see tomorrow's post), and so formatting can be completely disabled by using an argument of 0. It’s up to you...

Homework

Today’s homework is to enjoy your Sunday. You’re a busy DBA; you’ve earned a break. Get outside and off of the computer!