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.

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

THE sp_whoisactive DOCUMENTATION HAS BEEN MOVED -- IT CAN NOW BE FOUND HERE.

Published Saturday, April 30, 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

 

mjswart said:

Good work! April is finally over.

I appreciate all the work that went into this month and into "Who Is Active".

May 1, 2011 6:12 PM
 

Fabricio Lima said:

Congratulations for this project!!!

Thanks a lot.

May 2, 2011 10:31 AM
 

Narayana said:

Thank you! I've been using this SP for months, and really love it. Thanks for the updates!

May 6, 2011 8:20 AM
 

CD said:

What a journey....TNX awesome write up.

May 9, 2011 4:06 PM
 

M A Srinivas said:

I am getting the following error in additional_info

<msdb_query_error>Mutator 'modify()' on 'additional_info' cannot be called on a null value.</msdb_query_error>

May 31, 2011 5:22 AM
 

Adam Machanic said:

Hi M A,

Thanks for the error report. Can you share the entire additional_info structure, as well as the value in the program_name column and the parameters you're passing to sp_whoisactive?

Thanks,

Adam

May 31, 2011 10:08 AM
 

IJeb Reitsma said:

Thanks for these great 30 posts! I learned a lot from it, not only about the tool but also about SQL Server.

I looked at the tool for the first time about a year ago. It is very good for capturing real time information.

But for me the tool did not seem very useful for datacollection over a longer period of time or normal user activity baselining.

At your TechEd session you talked about using this tool for creating a baseline of normal user activity and resource usage during the day.

I am interested in doing that with this tool.

I can do that by running the tool every minute or hour or so and capturing the output in a table (of all the users sessions).

But if I do that the result is strange because if a session is 'sleeping' at the time of capturing the data I get session wide information (that's what I need), but if the session is busy at the time of snapshot creation it shows information about the individual request (and thats not very usefull for activity baselining).

Another thing is that I do have to calculate the deltas by myself afterwards.

The 'delta' feature included in the tool seems to be designed for capturing information of running requests.

To be short, my question is; how would you recommend creating a baseline using this tool?

June 2, 2011 8:37 AM
 

Adam Machanic said:

Hi IJeb,

I should probably write a blog post on this topic because it brings into question the entire methodology of baselining. The point of a baseline in a production environment is to establish what constitutes "normal."

For example, you could collect every 5 minutes during a given busy interval (i.e. if your app's busiest time is from 10:00 a.m. - 12:00 a.m. each day), and over the course of a few days note that it's expected that during that interval there will be between, e.g., 50 and 60 active requests, 20 of them for stored procedure X, 20 for stored procedure Y, and 20 for stored procedure Z. That's the kind of information that I would make part of an activity baseline.

Of course that's only one part of the baseline. You should also baseline performance counters and other information, which can be correlated when things change to figure out why the change occurred.

I'm not sure why you need session-level information. Do you have sessions that connect and issue many stored procedure (or ad hoc SQL) calls? Most applications these days don't operate that way, so WIA was not designed to push much of that kind of data. That said, if a lot of people tell me I'm dead wrong I can figure out how to collect more information at that level.

Unfortunately we weren't given nearly enough time to get into all of these things at a deep level in the TechEd session. My precon at this year's PASS conference will cover the topic in great detail.

Hope that helps a bit,

Adam

June 2, 2011 10:20 AM
 

M A Srinivas said:

Hello Adam

Here are the details requested on SQL 2005 SP4

<additional_info>

 <text_size>1024</text_size>

 <language>us_english</language>

 <date_format>mdy</date_format>

 <date_first>7</date_first>

 <quoted_identifier>ON</quoted_identifier>

 <arithabort>ON</arithabort>

 <ansi_null_dflt_on>ON</ansi_null_dflt_on>

 <ansi_defaults>OFF</ansi_defaults>

 <ansi_warnings>ON</ansi_warnings>

 <ansi_padding>ON</ansi_padding>

 <ansi_nulls>ON</ansi_nulls>

 <concat_null_yields_null>ON</concat_null_yields_null>

 <transaction_isolation_level>ReadCommitted</transaction_isolation_level>

 <lock_timeout>-1</lock_timeout>

 <deadlock_priority>0</deadlock_priority>

 <row_count>0</row_count>

 <command_type>UPDATE STATISTIC</command_type>

 <agent_job_info>

   <job_id>BBB33B89-E604-4EB9-8ECB-908FD4095302</job_id>

   <step_id>2</step_id>

   <job_name />

   <step_name />

   <msdb_query_error>Mutator 'modify()' on 'additional_info' cannot be called on a null value.</msdb_query_error>

 </agent_job_info>

</additional_info>

------------------------------------------------

Parameters

-----------------------------------------------

@filter sysname = '',

@filter_type VARCHAR(10) = 'session',

@not_filter sysname = '',

@not_filter_type VARCHAR(10) = 'session',

--Retrieve data about the calling session?

@show_own_spid BIT = 0,

--Retrieve data about system sessions?

@show_system_spids BIT = 0,

--Controls how sleeping SPIDs are handled, based on the idea of levels of interest

--0 does not pull any sleeping SPIDs

--1 pulls only those sleeping SPIDs that also have an open transaction

--2 pulls all sleeping SPIDs

@show_sleeping_spids TINYINT = 1,

--If 1, gets the full stored procedure or running batch, when available

--If 0, gets only the actual statement that is currently running in the batch or procedure

@get_full_inner_text BIT = 0,

--Get associated query plans for running tasks, if available

--If @get_plans = 1, gets the plan based on the request's statement offset

--If @get_plans = 2, gets the entire plan based on the request's plan_handle

@get_plans TINYINT = 1,

--Get the associated outer ad hoc query or stored procedure call, if available

@get_outer_command BIT = 1,

--Enables pulling transaction log write info and transaction duration

@get_transaction_info BIT = 1,

--Get information on active tasks, based on three interest levels

--Level 0 does not pull any task-related information

--Level 1 is a lightweight mode that pulls the top non-CXPACKET wait, giving preference to blockers

--Level 2 pulls all available task-based metrics, including:

--number of active tasks, current wait stats, physical I/O, context switches, and blocker information

@get_task_info TINYINT = 2,

--Gets associated locks for each request, aggregated in an XML format

@get_locks BIT = 1,

--Get average time for past runs of an active query

--(based on the combination of plan handle, sql handle, and offset)

@get_avg_time BIT = 1,

--Get additional non-performance-related information about the session or request

--text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on,

--ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null,

--transaction_isolation_level, lock_timeout, deadlock_priority, row_count, command_type

--

--If a SQL Agent job is running, an subnode called agent_info will be populated with some or all of

--the following: job_id, job_name, step_id, step_name, msdb_query_error (in the event of an error)

--

--If @get_task_info is set to 2 and a lock wait is detected, a subnode called block_info will be

--populated with some or all of the following: lock_type, database_name, object_id, file_id, hobt_id,

--applock_hash, metadata_resource, metadata_class_id, object_name, schema_name

@get_additional_info BIT = 1,

--Walk the blocking chain and count the number of

--total SPIDs blocked all the way down by a given session

--Also enables task_info Level 1, if @get_task_info is set to 0

@find_block_leaders BIT = 1,

--Pull deltas on various metrics

--Interval in seconds to wait before doing the second data pull

@delta_interval TINYINT = 10,

--List of desired output columns, in desired order

--Note that the final output will be the intersection of all enabled features and all

--columns in the list. Therefore, only columns associated with enabled features will

--actually appear in the output. Likewise, removing columns from this list may effectively

--disable features, even if they are turned on

--

--Each element in this list must be one of the valid output column names. Names must be

--delimited by square brackets. White space, formatting, and additional characters are

--allowed, as long as the list contains exact matches of delimited valid column names.

@output_column_list VARCHAR(8000) = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',

--Column(s) by which to sort output, optionally with sort directions.

--Valid column choices:

--session_id, physical_io, reads, physical_reads, writes, tempdb_allocations,

--tempdb_current, CPU, context_switches, used_memory, physical_io_delta,

--reads_delta, physical_reads_delta, writes_delta, tempdb_allocations_delta,

--tempdb_current_delta, CPU_delta, context_switches_delta, used_memory_delta,

--tasks, tran_start_time, open_tran_count, blocking_session_id, blocked_session_count,

--percent_complete, host_name, login_name, database_name, start_time, login_time

--

--Note that column names in the list must be bracket-delimited. Commas and/or white

--space are not required.

@sort_order VARCHAR(500) = '[session_id] ASC',

--Formats some of the output columns in a more "human readable" form

--0 disables outfput format

--1 formats the output for variable-width fonts

--2 formats the output for fixed-width fonts

@format_output TINYINT = 1,

--If set to a non-blank value, the script will attempt to insert into the specified

--destination table. Please note that the script will not verify that the table exists,

--or that it has the correct schema, before doing the insert.

--Table can be specified in one, two, or three-part format

@destination_table VARCHAR(4000) = '',

--If set to 1, no data collection will happen and no result set will be returned; instead,

--a CREATE TABLE statement will be returned via the @schema parameter, which will match

--the schema of the result set that would be returned by using the same collection of the

--rest of the parameters. The CREATE TABLE statement will have a placeholder token of

--<table_name> in place of an actual table name.

@return_schema BIT = 0,

@schema VARCHAR(MAX) = NULL OUTPUT,

--Help! What do I do?

@help BIT = 0

--~

Thanks

Srinivas

June 17, 2011 6:15 AM
 

Ken Trock said:

Adam, has anyone asked you if WIA could be an official part of SQL Server either now or in the future? It's obviously way better than sp_who2 and surely superior to the ad-hoc versions many of us have created for our own use.

June 24, 2011 1:03 PM
 

Adam Machanic said:

Hi Ken,

Many users have commented that it would be nice to see it as part of SQL Server, and I have tried a few times to set the wheels in motion. Alas, these efforts have invariably ended in long e-mail chains with no definitive answers, followed by the people on the Microsoft side no longer responding to my e-mails.

If you have some pull or a way in the door, please drop me a line privately and we'll talk. I would certainly love to see this happen.

--Adam

June 24, 2011 2:01 PM
 

Sreekanth CHAVA said:

Thanks a lot Adam, It was a nice project, I got to one of your post while I was searching for scripts on blocking, at last I have the script which I have being looking for a long time, yahooo..... this will go into my library.

Congratulations and once again thank you for all your hard work.

Cheers

SreekanthCHAVA

January 5, 2012 6:34 PM
 

Ben said:

Thanks for the series.

I read it last April and had need to review it again a little today.

At the top of each day, you wrote:

"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."

Did this ever happen?

January 10, 2012 9:37 PM
 

Adam Machanic said:

Ben: Working on it :-)

January 11, 2012 10:45 AM
 

Adam Machanic said:

It's been several months since the last Who is Active fix, so I thought I'd call this one out specifically

March 22, 2012 5:30 PM
 

Denis said:

In my toy 2012 SQLXPRESS, I got the following error when use WIA, is this a known issue?

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)

Feb 10 2012 19:13:17

Copyright (c) Microsoft Corporation

Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

(1 row(s) affected)

Warning: The join order has been enforced because a local join hint is used.

Msg 8631, Level 17, State 1, Procedure sp_WhoIsActive, Line 1706

Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

October 22, 2012 12:00 PM
 

Adam Machanic said:

Hi Denis,

Not expected at all. What parameters are you passing in?

Thanks,

Adam

October 22, 2012 1:40 PM
 

Denis said:

No parameter passed in.

Here someone described the long case statement failed in 2012 version with same error. Not sure how many CASEs in the WIP procedure.

http://connect.microsoft.com/SQLServer/feedback/details/718795/tsql-case-statement-fails-internal-error-server-stack-limit-has-been-reached-8631

Thanks,

Denis

October 23, 2012 10:50 AM
 

Adam Machanic said:

Hi Denis,

Could be something else other than CASE expressions -- lots of things can cause the stack to grow. Are you on Who is Active v11.11 ? If not, please try that.

Also, can you update your Express instance to CU4 and see if it still happens? That CU includes a few major query processor fixes.

Thanks,

Adam

October 23, 2012 11:45 AM
 

Denis said:

Updated to  CU4 :

-------

SQL Server 2012 - 11.0.2383.0 (Intel X86)

Oct  6 2012 11:13:58

Copyright (c) Microsoft Corporation

Express Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)

---

Same error. I do use v11.11.

Denis

October 24, 2012 7:58 AM
 

Denis said:

Sorry. I've realized I am using v11.00. I downloaded v11.11. It has no problem in SQL Express 2012. Thanks for all your great posts !

October 24, 2012 2:40 PM
 

Beneberu said:

"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." Were you able to combine and post the documnet? If so,  please post the link.

Thanks.

November 26, 2013 3:35 PM
 

Afredo said:

Could you please explain for me that Microsoft has provided activity monitor tool to troubleshoot query, so what is the benefits of WhoisActive compared with Activity Monitor.

Many thanks if you could give me a blue.

January 24, 2014 2:07 AM
 

Afredo said:

Sorry for the previous post, I have spent time to read your articles about WhoisActive from the beginning and I found the answer.

Thanks for great work.

January 24, 2014 2:14 AM
 

Bigpa said:

Brilliant tool(belt), thanks a lot !

This is the gift that keeps on giving

April 13, 2014 2:55 PM
 

Sameera said:

Thank you for the briliant 30 articles on activity monitoring and WhoIsActive.

By the way you can use wso2 Business Acitivity Monitor (http://wso2.org/projects/bam) to take this effort a bit further by configuring the system to generate meaningful results out of the actvities produced. Or you may use wso2 Complex Event Processor (http://wso2.org/projects/cep) to analyse the activity streams and perform some actions autonomously.

September 19, 2014 12:01 PM

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

Privacy Statement