Have you ever wondered about the internals of sp_who2? If you have, you have probably noted that sp_who2 is reliant upon a soon to be deprecated backwards compatibility view called sysprocesses. Sysprocesses evolved into a view with SQL 2005. Prior to SQL 2005 sysprocesses was a table. And that table was present back in SQL 6.5. Surprisingly, so was sp_who2.
A Little Background
Sp_who2 is a stored procedure of ignominious beginnings. This procedure started out as an undocumented stored procedure designed to augment the information provided from sp_who. The first version of SQL Server to use sp_who2 is SQL 6.5. I could find no reference to it from an earlier version of SQL server (6.0), though sp_who did exist dating back to the Sybase days. To verify this, I did go and reinstall SQL 6.5, found the proc and have compared it to recent versions. Here is a screenshot of that procedure from a SQL 6.5 installation.
Does that image bring back memories?
The procedure sp_who2 is still reliant on sysprocesses in SQL 2008. In 2008, sysprocesses is a little obfuscated but is present in the proc as sys.sysprocesses_ex. This view is present in the mssqlsystemresource database and uses fewer columns than the sys.sysprocesses view in the same database.
Changes
I have compared major revisions of the proc in SQL Server 6.5, SQL Server 2000, and SQL Server 2008 to try and see what has changed if anything. The proc has changed, albeit very little. Starting with SQL 6.5, all the way through SQL 2008 all of them have the same date stamp in the proc - 1995/11/03 10:16. This, to me, is a big indicator that not much really has changed with this particular stored procedure.
Some changes to note between 6.5 and 2000 are the use of varbinary and sysname types in the SQL 2000 version. There was also the elimination of a max SPID, deprecation of a few variables no longer used (commented out and left in the proc). There was a change in logic for the assignment of the user ID to a variable. The more notable items include the removal of additional fields used in 6.5. Those are kpid, hostprocess, memusage, waittype, uid, gid, login_time, net_address, net_library, nt_username, and nt_domain into the #tbl_sysprocesses temp table. The removal of such fields is fine since they were unused later in the proc. The last major difference is the permissions between the two. In SQL 6.5, there was a final Grant Execute statement granting permissions to public, which would be run every time the proc was executed. That snippet of code was removed by SQL 2000.
If we jump another version and compare SQL 2000 to SQL 2008, we get a new set of differences. Most notably changes are the change in schema from dbo to sys. Some error catching was thrown in for the 2008 version as was also the request_id. This is in addition to the change from using the sysprocesses table to the protected system view sys.sysprocesses_ex. In 2008 we also see the removal of two status types ("Mirror Handler" and "RA Manager"). The code that was commented out from 6.5 is finally removed in SQL 2008. It is also very important to note that the SQL 2008 defaults to setting ANSI_NULLS ON for this stored procedure. The SQL 2000 version sets this setting to OFF.
All in all, we see that not a whole lot has really changed with this stored procedure that is 14.5 years old. Despite significant changes in SQL server - this one procedure, that is so widely used, remains mostly unchanged.
Alternate Versions
Though this procedure has remained largely unchanged, it doesn't stop many people from implementing their own versions to pull back what they deem as essential information in lieu of using sp_who2. Often you will see that they have named their proc to be the same thing, or something along the lines of sp_who3. Most of these rewrites that I have seen still implement the use of sysprocesses. Some have written different versions of their proc depending on the version of SQL Server they are working with at the moment. Each person has their own set of requirements - and pretty much adhere to them.
While performing more research for this article when I was sitting down to write, I found one implementation that did not use any of the soon to be deprecated features. I also found a note that Microsoft had challenged the MVPs back in 2006 to write a replacement for sp_who2. As for that challenge, I have not been able to find corroborating statements. You can see the first note here. If you follow the link, you will also note that there is a query on that page to mimic the results of sp_who2. I came across this link as I was searching for more information on the deprecation of sysprocesses. I found a blog post by Kalen Delaney asking for it to not be deprecated. In that post Aaron Bertrand posted a link in the comments section to one of his re-writes (which has a link to the aforementioned link).
The query that I am about to share was written prior to finding this particular resource. Though I did make an update to my query, to add one more field that I had initially intentionally left-out, after reading that article. That field is the WaitType field. That change was sparked by evaluating this newly found script as well as by re-evaluating the old sp_who2 procedure. Note that the WaitType field is not explicitly returned by either query though. In both, it is returned in the Command Field.
My Re-write
Much the same as Aaron Bertrand in the aforementioned link, I wanted to create a procedure that could report the same sort of information as sp_who2 does. I wanted to have a piece of code that would rely on the 2005 system objects and not break in a future version of SQL Server by using an object that was known to be on the chopping block for a future release. There is also some functionality that sp_who2 does not provide that I thought would be nice to include. Add to that, Microsoft will need to rewrite this stored procedure some day to fit better with the new objects.
In order to rewrite this proc and remove sysprocesses, I needed to learn a few things about the replacement views. You can see what views are supposed to replace this table by visiting MSDN and reading this article. We can see that there are three views linked together in the effort of replacing sysprocesses: sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests. If we explore a little further, we can find that sys.dm_exec_connections returns detailed information about established connections in SQL Server. Sys.dm_exec_sessions returns information about each authenticated session in SQL Server. And sys.dm_exec_requests returns information about each request executed.
These views and this information, however, is not enough just yet to be able to rewrite this procedure. To gather all of the required and desired data, several more DMVs will need to be employed. These additional views are: sys.dm_os_waiting_tasks, sys.dm_os_tasks, sys.dm_tran_locks. There is also an additional function that I call in my script - sys.dm_exec_sql_text(). These views (and function) are helpful in returning information about blocking, locks, waits, task state, and the query text.
The remaining SQL 2005 features that I use will be described later. I will also admit here that I had intended on using IsNull rather than Coalesce. Then I realized that there were some fields that would be best used in a Coalesce to get more accurate results by combining them into one field via Coalesce. I am also intentionally leaving out RequestID. My version also does not currently support the querying of only active processes as does sp_who2.
Let's take a look at the query now.
/***************************************************************************************************************
*Routine Name: [dbo].[jb_mywho2]
*Purpose: Return SPID information from SQL Server
*Used by: Admins
*Inputs: @IsUserProcess (0,1),@CurrentSpid (0,Null)
*Returns:
*Test Code: EXEC jb_mywho2
***************************************************************************************************************/Create Procedure jb_mywho2
@IsUserProcessTinyInt = 0
,@CurrentSpidTinyInt = 0
As
Begin
Set NoCount On /*
Test Code
------------------------------------------
Declare @IsUserProcessTinyInt
,@CurrentSpidTinyInt
Select @IsUserProcess = 0
,@CurrentSpid = 0 */Select es.session_id As SPID
,ROW_NUMBER() over (partition by es.session_id order by es.login_time) As SubProcessID
,DB_NAME(Coalesce(er.database_id,tl1.resource_database_id,' - ')) As DBName
,Coalesce(ot.task_state,es.status,' - ') As TaskStatus
,es.login_name As LoginName
,Coalesce(ec.client_net_address,' - ') As IPAddress
,Coalesce((SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
FOR XML PATH(''), TYPE
),'') As QueryText
,Coalesce(er.wait_type,wt.wait_type,er.last_wait_type,' - ') As WaitType
,Coalesce(es.host_name,' - ') As HostName
,Coalesce(tl.request_session_id,'') As BlockedBy
,Coalesce((SELECT p.text
FROM (SELECT MIN(sql_handle) As sql_handle
FROM sys.dm_exec_requests r2
WHERE r2.session_id = tl.request_session_id
) As rb
CROSS APPLY
(SELECT text As [processing-instruction(definition)]
FROM sys.dm_exec_sql_text(rb.sql_handle)
FOR XML PATH(''), TYPE
) p (text)
),'') As BlockingText
,Coalesce(es.program_name,' - ') As ProgramName
,Coalesce(es.client_interface_name,' - ') As ClientInterface
,Coalesce(es.host_process_id,' - ') As UserProcessID
,es.login_time As LoginTime
,es.last_request_start_time As LastBatch
,es.total_elapsed_time *.001 As SessionElapsedTime
,es.total_scheduled_time * .001 As CPUTime
,es.memory_usage As Num8kPages
,Coalesce(ec.num_reads,'') As NumReads
,Coalesce(ec.num_writes,'') As NumWrites
,Coalesce(er.open_transaction_count,st.TranCount,0) As OpenTranCount
,Coalesce(dt.lockcount,0) As LockCount
From sys.dm_exec_sessions es
Left Outer Join sys.dm_exec_connections ec
On ec.session_id = es.session_id
Left Outer Join sys.dm_os_waiting_tasks wt
On wt.session_id = es.session_id
Left Outer Join sys.dm_os_tasks ot
On es.session_id = ot.session_id
Left Outer Join sys.dm_tran_locks tl
On wt.blocking_session_id = tl.request_session_id
Left Outer Join sys.dm_tran_locks tl1
On ec.session_id = tl1.request_session_id
Left Outer Join sys.dm_exec_requests er
On tl.request_session_id = er.session_id
Left Outer Join (
Select request_session_id,COUNT(request_session_id) As LockCount
From sys.dm_tran_locks
Group By request_session_id
) dt
On ec.session_id = dt.request_session_id
Left Outer Join (
Select session_id,COUNT(session_id) As TranCount
From sys.dm_tran_session_transactions
Group By session_id
) st
On ec.session_id = st.session_id
Where es.is_user_process >= (Case when @IsUserProcess = 0 Then 0 Else 1 End)
And es.session_id <> (Case when @CurrentSPID = 0 Then 0 Else @@SPID End)--@@SPID if current Spid is to be excluded
End
You can see that I have two input parameters for this new version. Being able to eliminate the current process or just show user processes was an important piece of this procedure for me. For me, reducing the amount of data to sift through when quickly running sp_who2 is helpful. Now I can just target the processes that I am highly confident would be the cause of a problem (by having eliminated already the least likely candidates - system processes and my spid that is running who2).
Also note, in this procedure, I am pulling back the Query Text, Blocking Query Text (if there happens to be blocking), IPAddress, LoginName associated to the spid, WaitType (already mentioned), ProgramName, ClientInterface, UserProcessID, LoginTime, Session and CPU Times, Number of Pages, and IO information.
Most of the items in the above list are self explanatory. I wanted to display the full query related to the spid - this could save me some precious moments when troubleshooting a blocking issue or slow-down on the server. This holds true of the blocking query text. When I decided to display the command as the full query text, I needed to split out the Wait Types that sp_who2 likes to lump in with the command field - thus the reason for the additional field. I also like knowing what ProcessID on the ClientMachine is related to this query. By knowing the ProcessID prior to going to the ClientMachine can also save a little time. There have been times when a user has exited all of the apps but a connection remains. Checking the ProcessID on the Client machine can help to close that connection - though rare it may be.
In addition to these fields there are two more fields that are of note. These fields may be of use when troubleshooting. Unfortunately, the functionality is not the same as one might expect if accustomed to using sysprocesses to troubleshoot. The first of these two is OpenTranCount. This is only a count of Open Transactions for active processes. Any process that has become dormant but has not committed a transaction may not show in this value. Also, nested transactions will only show a value of 1 in this column. Using a simple nested transaction test, I was able to spike the open_tran column in sysprocesses up to 5. However, the same test would only produce a 1 in the new DMVs. This kind of behavior might be expected after having read the following article from Paul Randal on nested transactions. SQL Server only recognizes the outer transaction and thus the reason for the value of 1 in the DMVs. Thus, for troubleshooting nested transactions (or uncommitted transaction count from the same session_id), one would have difficulty using just the new objects. For that reason, I also included the LockCount column. This is as the name implies. When troubleshooting, one may need to find the number of locks being held by a session. This will be helpful alongside the OpenTranCount column.
When running this procedure with the default parameters, you will quite possibly get duplicate entries. I considered changing the logic but then noticed that the duplicates were connecting to a different database. When I see this happening, there is a connection to the user database and then often a spawned process in the tempdb. I decided that it would be beneficial to leave that information available. I then added the SubProcessID field. This is the first area where I have one of those SQL 2005 additional features being employed. I am using the ROW_NUMBER() function to generate SubProcessIDs.
The next SQL 2005 feature that I used was for the QueryText field. This uses the FOR XML PATH feature. By using the For XML PATH, I can display the QueryText in its original format and make it easy to read. To help ensure that the text is readable without mis-interpretation of certain characters (>, <, etc), I am using the statement "processing-instruction(definition)." This tells XML to allow documents to allow processing instructions for applications. In addition to this instruction being used with the XML statement, it is also necessary to use the "TYPE" keyword in the FOR XML Path command.
The FOR XML PATH method, that I just described, was used for the BlockingText field, but with a twist. For the BlockingText field, I implemented a subquery that uses a CROSS APPLY. This CROSS APPLY is essential in helping to retrieve the actual text of the blocking query. The CROSS APPLY helps me to take a sql_handle and pass it to the sys.dm_exec_sql_text function and get the text of the query being executed.
If you want to learn more about the FOR XML Path statement that I have demonstrated, I recommend you read this blog post by Brad Schulz. He has a few entries on the topic. He also demonstrates a few examples and describes the statement quite well.
The final bit about this procedure involves the input parameters. I chose to use TinyInts for these fields to permit a value of 0, 1, or NULL. I catch this in the where clause using case statements.
When I execute this query, I will get a resultset similar to sp_who2. As has been discussed, there will be the additional fields. The QueryText field will show up as a hyperlink. If you click the hyperlink for the process in in question, a new tab with the query text will open and display the query text. This is illustrated in the following images.
First we have the output from the query here:
From here, if I click on the QueryText column that correlates to SPID 63, I will get a new tab with the query in original format. This is shown in the following image.
When you compare this output to the original query you will see that it is the same and (in the case of well formatted code) is easier to read. Being able to quickly see the query and understand it will aid in troubleshooting performance problems as well as finding the problematic query(ies) causing those performance issues.
By trapping the actual query text for each connection, there is a cost incurred. To join this information to the sp_who2 information, I see a penalty of about 200 ms. However, to try and trap that information from DBCC InputBuffer, you just cost yourself far more than 200 ms (based on my testing). I like the tradeoff - I know I can't locate an offending spid and type the command in less than 200 ms.
Conclusion
The information provided by sp_who2 is good information in the event of troubleshooting. The procedure has become a bit outdated and there are alternative means to gaining the same information. One will be more efficient in troubleshooting when using the alternative methods. I have shown one such alternative. There are several alternatives available on the web, that provide better information and faster than the traditional methods that involve using sp_who2. Eventually the day will come that sp_who2 will also be rewritten or retired. Be prepared for that and you will be ahead of the game.
The script is available for download from the Resource Files section.
References
The following references were made.
- Aaron Bertrand: How do I mimic sp_who2...
- Kalen Delaney: Why I Still Need Sysprocesses
- Paul Randal: Nested Transactions are Real
- Brad Schulz: XML PATHS of Glory
- MSDN: Mapping System Tables to System Views
