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.