THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Who is Active: The License (A Month of Activity Monitoring, Part 3 of 30)

This post is part 3 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.


I regularly get questions from people about the Who is Active license. The bottom line is that Who is Active is free for most users. But it’s important to clarify which users are not entitled to use it.

Here’s the license text, taken directly from the header of the stored procedure:

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

The goal of the license is to enable pretty much anyone to download and use the stored procedure, unless you’re using it to make money. This is not the same as using the stored procedure as part of the job for which you’re paid. So if you’re a DBA supporting your company’s database servers, download and enjoy. If you’re a developer working on a database-backed application—either for your company or even for resale—and you want to monitor the database using Who is Active, download and enjoy. If you’re interested in learning about SQL Server’s internal behaviors at home or at school, download and enjoy. Even if you’re a third-party consultant helping a customer, download and enjoy (and please, tell your customer that you’ve installed in on their system, so that they can use it going forward).

What you’re not allowed to do is send or deploy the stored procedure to anyone else outside of your company (client, if you're a consultant), school, or home. Obviously (I hope) the goal here is not to stop you from forwarding the procedure to your friend working as a DBA for some other company. It's not my intention to stop that from happening, and even if it were my goal it's not like I could enforce things on such a granular level. But in that case it would probably be better to link your friend to the download page instead, so that she can be sure to download the most recent version.

There are two groups in particular that were on my mind as I wrote the license: remote DBA services companies, and companies that create monitoring tools.

Let’s start with monitoring tools companies. If you’re writing a monitoring tool, and the tool will use Who is Active to collect data, you’re going to have to distribute the procedure with your tool in some fashion. This is explicitly disallowed without my consent. So far I’ve authorized two outside parties, both for creation of free tools only, to leverage Who is Active as part of their work. Neither has the right to distribute it.

Next let’s consider remote DBA companies. Having worked for one of these, I know firsthand that part of the equation is selling the great monitoring that your company provides. As a matter of fact, remote DBA companies are probably a complete subset of monitoring tools companies—all of the good ones create and use their own tools. And the same rules apply: if you’re using Who is Active as part of your remote monitoring solution, you must be distributing it to your customers, and this means that you’re in violation of the license. I have currently not authorized any remote DBA companies to use Who is Active as part of their solution.

The reasoning behind these rules is quite simple: Who is Active is my creation and I have put several hundred hours of work into it. If it’s being used in a tool that you're distributing I need to understand who you are, what you’re doing, and why. And if you’re leveraging my work to make money, I think it's only fair that I get a cut.

I hope that this clarifies any questions about the license going forward. If I’ve missed something, or if you’re aware of some violation of the license, please leave a comment below or drop me a line—my e-mail address is in the header of the stored procedure along with the license text.

 
 

Homework

Yesterday’s homework challenge was to describe the lifecycle of the CPU column in the sysprocesses view. My goal with this question was to highlight the ambiguity of the information: When a session is sleeping, the CPU column represents the total CPU time consumed by all requests that were active on behalf of the session prior to it sleeping. And when the session is active, the CPU column means the same thing—but it may (or may not) get updated over the course of the request to reflect work being done. This means that you can’t use the column to figure out how much CPU time the currently active query has consumed; you don’t know what else has already contributed to the number. And especially in the case of parallel queries, you may see numbers that either aren’t updated regularly, or that are totally nonsensical. It is for this reason, among others, that SQL Server 2005 introduced two different sources for this metric: sys.dm_exec_requests for active requests, and sys.dm_exec_sessions for aggregate information on all of the requests that have been processed on behalf of a given session. Who is Active uses both of these views—in addition to the legacy sysprocesses view—and I’ll describe the sleeping/active data lifecycle in a post later this month.

Today’s challenge: The sys.dm_exec_requests and sys.dm_exec_sessions DMVs have a number of columns in common. Identify the full set of equivalent columns and categorize them into columns that will be updated in the sessions DMV at the end of a request (with information about what happened over the course of the request), and columns that may be different during the course of the request, yet may revert to their original value at the end of the request. (Hint: consider the impact of the changing scope of a request.) Place your answer in the comments section below.

Published Sunday, April 03, 2011 11:00 AM by Adam Machanic

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Antony said:

I don't think anyone could complain at these very generous terms, Adam!

I know you've put hundreds of hours of work into it, and in doing so you've saved those of use that use it many times that - so on behalf of us all, thank you for your hard work.

April 3, 2011 12:24 PM
 

Darcy said:

I'm very new to dmv's and thankful for the information in your posts.  Very helpful.  My crack at the homework is below:

Columns that will be updated at the end:

cpu_time, logical_reads, reads, and writes

Columns that may change and revert to their original value:

status, context_info, textsize, language, date_format, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_iosolation_level, lock_timeout, deadlock_priority, row_count, prev_error

April 4, 2011 2:56 PM
 

Adam Machanic said:

This post is part 4 of a 30-part series about the Who is Active stored procedure. A new post will run

April 20, 2011 10:42 AM
 

Adam Machanic said:

This post is part 30 of a 30-part series about the Who is Active stored procedure. A new post will run

April 30, 2011 11:45 AM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement