Export SQL Activity Monitor Data
I know its been forever since I’ve written anything, but that’s life in the fast lane I guess. But I recently ran into something that I wanted to share. I’ve been working with a client trying to get some issues resolved with one of their applications and at one point they asked me to gather a snapshot of all the locks and activities going on with the server at the time the application failed. Now SQL 2005 does have this great Activity Monitor, however it doesn’t include any ability to export the report. In the activity monitor you can also take a look at the last SQL statement that a particular SPID ran if there is one. So I wanted to find a way to export all that data into an Excel Document to get a nice snapshot of the processes that are running and the locks, wait times, statements, etc that would help me figure out what is going on with this server. So after a couple days worth of digging and trying to dust of my T-SQL skills I think I’ve managed to do exactly that. I wanted to share this with everyone because it’s not a client specific query and if you find your self in need actually grabbing this information to analyze later I think this might help.
USE MASTER GO --Create a temporary table with the information we need SELECT [Process ID] = p.spid , [IsSystemProcess] = case when s.is_user_process = 1 then 0 else 1 end , [User] = p.loginame , [Database] = ISNULL(db_name(p.dbid),N'') , [Status] = p.status , [Open Transactions] = p.open_tran , [Command] = p.cmd , [Application] = p.program_name , [Wait Time] = p.waittime , [Wait Type] = case when p.waittype = 0 then N'' else p.lastwaittype end , [Wait Resource] = case when p.waittype = 0 then N'' else p.waitresource end , [CPU] = p.cpu , [Physical IO] = p.physical_io , [Memory Usage] = p.memusage , [Login Time] = p.login_time , [Last Batch] = p.last_batch , [Host] = p.hostname , [Net Library] = p.net_library , [Net Address] = p.net_address , [Blocked By] = p.blocked , [Execution Context ID] = p.ecid , [ID] = IDENTITY(int, 1, 1) , (SELECT sql_handle from sys.sysprocesses where spid = p.spid) AS [Last Statement] INTO #snapshot_processinfo FROM master.dbo.sysprocesses p, master.sys.dm_exec_sessions s WITH (NOLOCK) WHERE p.spid = s.session_id ORDER BY p.spid GO --Query the table and format it. SELECT RTRIM([Process ID]) AS [Process ID] , RTRIM([IsSystemProcess]) AS [IsSystemProcess] , RTRIM([User]) AS [User] , RTRIM([Database]) AS [Database] , RTRIM([Status]) AS [Status] , RTRIM([Open Transactions]) AS [Open Transactions] , RTRIM([Command]) AS [Command] , RTRIM([Application]) AS [Application] , RTRIM([Wait Time]) AS [Wait Time] , RTRIM([Wait Type]) AS [Wait Type] , RTRIM([Wait Resource]) AS [Wait Resource] , RTRIM([CPU]) AS [CPU] , RTRIM([Physical IO]) AS [Physical IO] , RTRIM([Memory Usage]) AS [Memory Usage] , RTRIM([Login Time]) AS [Login Time] , RTRIM([Last Batch]) AS [Last Batch] , RTRIM([Host]) AS [Host] , RTRIM([Net Library]) AS [Net Library] , RTRIM([Net Address]) AS [Net Address] , RTRIM([Blocked By]) AS [Blocked By] , RTRIM([Execution Context ID]) AS [Execution Context ID] , RTRIM([ID]) AS [ID] , (SELECT text FROM sys.dm_exec_sql_text([Last Statement])) AS [Last Statement] FROM #snapshot_processinfo GO --Cleanup that temporary table DROP TABLE #snapshot_processinfo
Once you’ve run the code in a SQL Query Window just right click the top right corner of the results window and click copy. Then just paste the data in Excel and format as Table or Create as list to sort through and analyze the data at your discretion.
The code found above is fairly straight forward and I hope it will hopefully help you find what you are looking for. Maybe some day Microsoft will actually add an export button to that Activity Monitor. 
Filed under: .computers, .howto
