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.

Getting More Information (A Month of Activity Monitoring, Part 18 of 30)

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


Sometimes you just need more.

With over 20 columns in the default output plus several more than can be dynamically enabled and disabled, Who is Active was already overwhelming enough for certain users. But requests kept pouring in for various additional information—metrics to help debug trickier situations and edge cases.

Rather than cluttering the output, I decided to create a single, special-purpose column for everything that’s not quite important enough to be on its own in the output. The [additional_info] column is an XML column that returns a document with a root node called <additional_info>. What’s inside of the node depends on a number of things, but by default you can expect to see:

  • 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

Rather than repeat the documentation, I’ll point you to the BOL entry for sys.dm_exec_requests for information about what all of these mean. Most of them are various settings that can be manipulated by a given user, batch, or stored procedure. They impact the results of a query and, in some cases, its plan. So it’s a good idea to be able to pull them up when needed.

Beyond these, the [additional_info] column might also contain various other pieces of information, depending on which options are selected and what happens to be running. For example, if a SQL Agent job is running [additional_info] will be populated with:

  • job_id: the identifier for the job in MSDB
  • job_name: the name of the job, from MSDB
  • step_id: the identifier for the job step in MSDB
  • step_name: the name of the job step, from MSDB
  • msdb_query_error: included when an error occurs that renders Who is Active unable to resolve the job and step names

Today’s post is just a quick overview; I’ll cover other things you can expect to see in [additional_info] in a later post. In the meantime, how do you get all of this information? Simple:

EXEC sp_WhoIsActive
    @get_additional_info = 1

 

Homework

What information would you like to see added to the [additional_info] column? Let me know in the comments below. I’ll carefully consider every suggestion but there are no guarantees; remember, too much data is just as bad as not enough (and maybe worse). I plan to be just as careful with [additional_info] as I am with the rest of the columns output by the stored procedure.

Published Monday, April 18, 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

 

Adam Machanic said:

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

April 19, 2011 10:06 AM
 

Uri Dimant said:

Hi Adam

EXEC sp_WhoIsActive

   @get_additional_info = 1

Is the above part of who_is_active_v10.00.sql???

April 20, 2011 2:57 AM
 

Uri Dimant said:

Hi Adam

EXEC sp_WhoIsActive

   @get_additional_info = 1

Is the above part of who_is_active_v10.00.sql???

April 20, 2011 2:57 AM
 

Adam Machanic said:

Hi Uri,

Yes, it's included in v10.

--Adam

April 20, 2011 9:35 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