sp_WhoBetter

Another weapon in the arsenal of the SQL Server DBA. When tickets about slow performance pour in, when C-level execs buzz your cell about reports taking “a lot longer than normal” to render… being a quick draw on the prod SQL Server is what earns your keep.

sp_WhoBetter to the rescue. It’s a light-weight shortcut-worthy proc to give you instant insight into what’s running on your SQL Sever.

What the heck is running on my SQL Server right now?

sp_WhoBetter is indeed a nod to Nobody Does It Better – The Spy Who Loved Me… have to keep the spy theme running here– just a like a bad query plan filling up your tran log.

To be fair, there are certainly some great scripts out there for this purpose including Adam Mechanic’s sp_whoisactive that I often use for the cool utility of being able to record captures to a table. However, on the busiest of busy SQL Servers I find that many sp_who replacements can lag or get blocked when in trouble. Since sp_WhoBetter is focused on best bang for a light touch, it typically can instantly get you critical data when others cannot. I also like that it can tell me what SQL Agent job (if any) is running a query I’m interrogating as a bad actor. Enjoy!

[thrive_lead_lock id=’660′]

USE [master]
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.sp_WhoBetter') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
     DROP PROC dbo.sp_WhoBetter     
GO
/*=========================================================================================================
	Procedure:  sp_WhoBetter 
 ==========================================================================================================
 DESCRIPTION: A comprehensive replacement for sp_Who and sp_Who2.  Returns more effective info for the task.  

 FOR Pre-MSSQL 2016 instances, comment out lines around DOP (currently 78 and 121)
  
 INPUT:	@spid INT  --OPTIONAL  
		@blocked BIT --OPTIONAL  
  
 OUTPUT: RECORDSET  

 SAMPLE CALLS:	EXEC sp_WhoBetter --> Returns all active, non-sleeping, processes
				EXEC sp_WhoBetter @spid = 122  --> Returns only results for the spid 122  
				EXEC sp_WhoBetter @blocked = 1  --> Returns only processes that are blocking or blocked   
				EXEC sp_WhoBetter @show_sleepers = 1  --> Also show spids that are marked as sleeping   

For more insight visit:  https://jamesbond.consulting/sp-WhoBetter

PROVIDED BY:
James Bond Consulting (JBC)
Written by: James Bond
(c) 2020, https://jamesbond.consulting. All rights reserved.
email: [email protected]

You may alter this script (the "Software") for your own in-house purposes. You may also republish or blog 
code including without limitation the rights to use, copy, modify, merge, distribute, sublicense, and/or 
sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject 
to the following conditions:

§ The above copyright notice and this permission notice shall be included in all copies or substantial 
portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT 
LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. 
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE 
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

=============================================================================================================*/ 
CREATE PROCEDURE [dbo].[sp_WhoBetter]     
@spid INT = 0 ,  
@blocked BIT = 0 ,
@show_sleepers BIT = 0    
    
AS  
  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
 
;WITH Blocking AS (SELECT blocked FROM sys.sysprocesses WHERE blocked <> 0)
  SELECT DISTINCT 
	SessionID			= sp.spid,  
	ObjectName			= OBJECT_NAME(qt.ObjectID),  	
	BlockedBy			= ISNULL(blocking_session_id,0), 
	Executing			= CASE WHEN er.statement_start_offset IS NULL THEN qt.text ELSE
							SUBSTRING
							(
								qt.text,
								er.statement_start_offset/2,
								(CASE WHEN er.statement_end_offset = -1
									THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
									ELSE er.statement_end_offset
									END - er.statement_start_offset)/2
							) END,  

	[Database]			= DB_Name(sp.[dbid]),
    ElapsedMS			= FORMAT(er.total_elapsed_time, '#,0'),
    CPU					= FORMAT(er.cpu_time, '#,0'),
    IOReads				= FORMAT(er.logical_reads + er.reads, '#,0'),
    IOWrites			= FORMAT(er.writes, '#,0'),         
	GrantMemKB			= FORMAT(mg.granted_memory_kb, '#,0'),
	UsedMemKB			= FORMAT(mg.used_memory_kb, '#,0'),
	MemPercent			= FORMAT(100.0 * mg.used_memory_kb / mg.granted_memory_kb, '0.0'),
	QueryCost			= FORMAT(mg.query_cost, '#,0'),
	DOP					= er.dop, 
	PrlWrkrs			= er.parallel_worker_count,
    [Status]			= sp.[status],
    [Login]				= sp.loginame,
    Host				= sp.[hostname],  
	ProgramName			= sp.[program_name],  
	SQLAgentJob			= j.[name],  
	LastWaitType		= COALESCE([last_wait_type],[lastwaittype]),  
	Command				= COALESCE(er.command,cmd),   
	TransactionIsolation =
							CASE ses.transaction_isolation_level
								WHEN 0 THEN 'Unspecified'
								WHEN 1 THEN 'Read Uncommitted'
								WHEN 2 THEN 'Read Committed'
								WHEN 3 THEN 'Repeatable'
								WHEN 4 THEN 'Serializable'
								WHEN 5 THEN 'Snapshot'
							END,
	LoginTime			= sp.login_time, 
	ClientAddress		= con.client_net_address,
    [Authentication]    = con.auth_scheme,
	OpenTransactions	= sp.open_tran, 
	CaptureTime			= GETDATE()
 FROM sys.sysprocesses sp
	LEFT JOIN sys.dm_exec_requests er --map to active spids
		ON sp.spid = er.session_id
	LEFT JOIN sys.dm_exec_sessions ses
		ON ses.session_id = er.session_id
	LEFT JOIN sys.dm_exec_connections con
		ON con.session_id = ses.session_id
		AND con.parent_connection_id IS NULL -- not as interested in MARS and/or Connection Pooling info.
    LEFT JOIN sys.dm_exec_query_memory_grants mg 
		ON er.session_id = mg.session_id
	LEFT JOIN msdb.dbo.sysjobs j 
		ON master.dbo.fn_varbintohexstr(j.job_id) = SUBSTRING(sp.[program_name],30,34)
	LEFT JOIN Blocking b
		ON b.blocked = sp.spid
	OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS qt	
  WHERE 
	(@spid = 0 OR er.session_id = @spid)  
	AND (@blocked = 0 OR (blocking_session_id <> 0 OR sp.spid = b.blocked)) -- only interested in blocking chains?
	AND sp.spid > 50 --skip system reserved spids   
	AND (@show_sleepers = 1 OR sp.[status] NOT IN ('sleeping','dormant','background') OR (sp.spid = b.blocked))  -- usually, only show sleepers if they are also blockers
	AND (er.DOP = 1 OR qt.text IS NOT NULL) --only show one row for parallel operations
	AND sp.spid != @@SPID;

[/thrive_lead_lock]