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.

Installing Who is Active (A Month of Activity Monitoring, Part 4 of 30)

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



An entire post on installation? Isn’t Who is Active just a stored procedure..?

Well, yes. And yes. It might be as easy as downloading the .ZIP file, unzipping it, opening the .SQL file in Management Studio, and hitting F5 or CTRL-E.

But if you’re like some of the people who’ve e-mailed me over the past few years, you may have some questions...

 
What Permissions Are Required?

Most of what Who is Active does requires VIEW SERVER STATE permission. This is a permission level that allows access to the various instance-global DMVs, including the request, session, and transaction related views. In most cases there is no reason to avoid granting this privilege to a user; the main concern is situations where a user might be able to harvest private information by looking at SQL text, especially when it is non-parameterized. These cases being quite few and far between, I consider this to be a relatively low barrier to entry.

Beyond VIEW SERVER STATE, various other Who is Active features may require access to specific databases. The most important of these features are locks collection and blocked object name resolution (both of which will be covered in a subsequent post). When these are used, the stored procedure will attempt to access the database in which the lock or blocking is occurring, in order to resolve the affected object names. If the user calling Who is Active does not have sufficient privileges in the database, Who is Active will collect the error message and report it instead of the object name.

 
 

Which Database Should I Put it In?

The stored procedure is named “sp_WhoIsActive” for a reason: It’s designed to live in the master database; the “sp_” prefix, as you’re probably aware, allows a stored procedure in master to be called from the context of any database on the instance.

I know that many DBAs like to keep all of the DBA scripts in a special-purpose DBA database. Who is Active will work fine from there. But really, it’s much nicer to keep it in master. Never underestimate the power of convenience.

 
 

Help! It Keeps Throwing the Error: “Incorrect syntax near '.'”

It’s amazing how many times people have written and asked me about this particular error. Not because it’s obvious what’s going on, but because of what it means: You’ve upgraded to SQL Server 2005 or SQL Server 2008 from SQL Server 2000, and you haven’t updated the database compatibility level. This error is thrown when a database in SQL Server 2000 compatibility mode encounters a common table expression. It’s not pretty, and it doesn’t need to happen.

Stop reading this right now and go run the following query against your production SQL Server 2005 or 2008 instances:

SELECT *
FROM sys.databases
WHERE
    compatibility_level < 100

If any rows are returned, think long and hard about why that database needs to be set so as to make your life more difficult. And then update the compatibility level to something that makes sense in 2011, not 1998.

 
 

Help! It’s Throwing Some Other Error!

If Who is Active is throwing some error aside from the one above, and it’s not a permissions-related issue, then it’s probably my fault. Leave me a comment here, write me an e-mail, or find me on Twitter. Please do one of these things so that I can start working on the problem. If there is a problem, I want to fix it. And trust me when I say that I take problems with Who is Active very seriously. Most of the features and bug fixes are the result of users telling me what does and does not work for them. I can’t stress enough how much I enjoy both getting, and acting on, your feedback.

 
 

Homework

Yesterday I asked you to identify session-level columns that may not reflect the value that was present during a request. Information on such metrics as CPU time, reads, writes, etc, is available for active requests in the sys.dm_exec_requests DMV, and gets aggregated in the sys.dm_exec_sessions DMV after each request is completed. The columns that show the settings of user options such as ANSI_NULLS and ARITHABORT behave somewhat differently. Try the following batch to see how the DMVs treat these values.

SET ANSI_NULLS ON;

SELECT
    ansi_nulls
FROM sys.dm_exec_sessions
WHERE
    session_id = @@SPID
   
EXEC
('
    SET ANSI_NULLS OFF;
    SELECT
        ansi_nulls
    FROM sys.dm_exec_requests
    WHERE
        session_id = @@SPID;
')

SELECT
    ansi_nulls
FROM sys.dm_exec_sessions
WHERE
    session_id = @@SPID;

If you’re using Who is Active, or if you’re writing your own DMV queries, this is yet another thing to keep in mind. Don’t be surprised when values seem to shift around at will; the information in the sessions and requests DMVs tends to mirror the actual behavior of the settings as requests go in and out of context.

Today’s task: In addition to permissions to query the instance-global DMVs, VIEW SERVER STATE grants a user access to two DBCC commands. Identify them in the comments below.

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

 

maxx said:

DBCC SQLPERF(LOGSPACE) and DBCC INPUTBUFFER() both require VIEW SERVER STATE permissions.

April 4, 2011 11:36 AM
 

Darcy said:

DBCC SQLPERF(LOGSPACE)

DBCC INPUTBUFFER()

April 4, 2011 3:05 PM
 

martinz said:

Hi Adam,

Is there any advantage \ disadvantage of marking the procedure in master with sp_MS_marksystemobject?

cheers

Martin.

April 5, 2011 5:23 AM
 

Adam Machanic said:

Maxx and Darcy, good job. But it's not just DBCC SQLPERF(LOGSPACE) -- it's all features of DBCC SQLPERF.

Martin: No benefit at all. Marking a stored procedure as a system object is useful when the procedure uses the database-local views and you want to be able to call it from a database and get results from that database. Who is Active is designed to be instance global so if it needs to access data in a given database it switches its context to that database first.

April 5, 2011 10:52 AM
 

Michael Codanti said:

I just wanted to comment that Adam does take bug reports and feature requests seriously. (I wish some of the companies that we pay annual support contracts were a quarter as responsive in terms of actually fixing bugs.)

April 5, 2011 11:42 PM
 

Adam Machanic said:

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

April 7, 2011 8:47 PM
 

Adam Machanic said:

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

April 30, 2011 11:44 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
 

Tim said:

Hmm, just wondering if this

SELECT *

FROM sys.databases

WHERE

   compatibility_level < 100

should be this

SELECT *

FROM sys.databases

WHERE

   compatibility_level < 90

?

September 12, 2011 2:45 PM
 

Alexander said:

First of all let me thank u about your help, it really helps me. I have some question, hope you can help me, I followed this steps

1- I installed in other database no master

2- Followed this steps at http://www.littlekendra.com/2011/02/01/whoisactive/

3- there is a field called sql_text that almost always show me <?query begin tran> not always but almost.

An also i got some messages :

Warning: Null value is eliminated by an aggregate or other SET operation.

Warning: Null value is eliminated by an aggregate or other SET operation.

Warning: Null value is eliminated by an aggregate or other SET operation.

2013-06-10 12:28:50: Logged info. Waiting...

thanks so much!

June 10, 2013 1:43 PM
 

Adam Machanic said:

@Tim: Hm, I think you're correct. Too bad I only noticed your comment almost two years later. Oh well.

@Alexander: Sounds like some app on your end is beginning transactions and then just letting 'em sit there. Might want to sort that out.

Don't worry about the warnings. They're warnings, not errors. I keep meaning to sort 'em out, since they do bother some people, but it's never been a high priority of mine.

--Adam

June 10, 2013 2:39 PM
 

Manuel Bulhao said:

@Tim,Adam: I am too always getting a lot of <?query begin tran> but I can say that they are all related to the implemented (transactional) replications/jobs. And as they are configured to run continuously, it does certainly explain this behavior.

@Adam: incredible stuff, thanks a lot.

January 27, 2014 8:43 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