Keeping a database running smoothly and efficiently is essential for any organization. The built-in stored procedure, sp_who2, is a powerful tool that can be tailored to meet your specific needs, providing valuable insights into SQL Server performance. In this blog post, you’ll discover how to modify sp_who2 to focus on key session data by using a temp table, which can help streamline the monitoring process and make it more targeted, especially for DBAs. By fine-tuning the results, you’ll gain a deeper understanding of your system’s performance, enabling you to quickly pinpoint and address any issues or resource-intensive operations. Dive into this post to learn how customizing sp_who2 can transform your approach to database management and don’t forget to share your successes in the comments!
/*Check if the temporary table #sp_who2_temp exists in the tempdb database.
If it does, drop the table to start with a clean slate*/
IF OBJECT_ID('tempdb..#sp_who2_temp','u') IS NOT NULL
DROP TABLE tempdb..#sp_who2_temp
GO
/*Create a temporary table #sp_who2_temp with the same columns as the
sp_who2 stored procedure output to store the results of sp_who2*/
CREATE TABLE tempdb..#sp_who2_temp
(
SPID INT,
[Status] NVARCHAR(255),
[Login] NVARCHAR(255),
[HostName] NVARCHAR(255),
[BlkBy] NVARCHAR(255),
[DBName] NVARCHAR(255),
[Command] NVARCHAR(255),
[CPUTime] INT,
[DiskIO] INT,
[LastBatch] NVARCHAR(255),
[ProgramName] NVARCHAR(255),
[SPID2] INT,
[RequestedID] INT
)
/*Insert the results of the sp_who2 stored procedure into the temporary table*/
INSERT INTO tempdb..#sp_who2_temp
EXEC sp_who2
GO
/*Select the desired columns from the #sp_who2_temp table and join it with the SYS.SYSPROCESSES system table to get the sql_handle.
Use the SYS.DM_EXEC_SQL_TEXT function with an OUTER APPLY to fetch the SQL text for each process*/
SELECT
spt.spid,
spt.[status],
spt.[login],
spt.hostname,
spt.blkby,
spt.dbname,
spt.command,
spt.cputime,
spt.diskio,
spt.lastbatch,
spt.programname,
cast('<?codeblock --> '+ stext.text + ' --?>' AS XML) AS current_running_block,
spt.spid2,
spt.requestedid
FROM tempdb..#sp_who2_temp spt
INNER JOIN SYS.SYSPROCESSES spro
ON spro.spid = spt.spid
OUTER APPLY SYS.DM_EXEC_SQL_TEXT(spro.sql_handle) AS stext
WHERE
dbname = 'YourDatabaseName'
--AND sp2.[status] in('running','suspended','runnable')
ORDER BY
1
/*Drop the temporary table when you're done*/
--DROP TABLE tempdb..#sp_who2_temp
