SP_What is smarter than SP_Who2!

We’ve seen many DBAs scratch their head to find out who is running what in SQL Server. When a user complains about the database performance issue, the first thing DBAs do is run SP_Who or SP_Who2 to see what may be happening in SQL Server. Since the information returned is limited, DBAs use various scripts from other websites to add more misery to the problem. We recommend using SP_What instead of SP_Who or SP_Who2 for better productivity.

Start using SP_What instead of SP_Who2

So to find out what is happening in SQL Server, we would like to share the reason behind writing this best practice article. Last week one of our customers from (Lone Star State) asked us to help them with writing a lightweight smarter version of SP_Who2 which will simply show what (SP_What :-)) is happening in SQL Server but better than SP_Who2. We decided to call the new script as SP_What (What are you doing SQL Server?). We took the customers question “What is happening in SQL Server” and reviewed the output of both SP_Who and SP_Who2 and decided to include as many details as possible in SP_What that would help the customers answer what is happening in SQL Server. On our in-house servers when tested, we felt SP_What is smarter than SP_Who2.

As always we make sure that the scripts provided by SQLOPS are lightweight and does the work as expected and most importantly help our customers with their day-to-day complex database operations. SP_What is no exception!

SQL Server Best Practices Script provided by HTTPS://SQLOPS.COM (The Most Advanced Risk and Health Audit for Production Databases)

CREATE PROCEDURE SP_What AS
SET NOCOUNT ON
--Credits: https://sqlops.com 
--The Most Advanced Risk and Health Audit for Production Database Server 
--Only $1999 per Production Server
--Schedule production database audit by 
--emailing us at support@sqlops.com BEGIN
SELECT 
@@servername AS 'SERVERNAME',
DB_NAME(SQLOPS_DMExecRequests.database_id) as 'DBNAME',
SQLOPS_DMExecSessions.login_name as 'LOGIN_NAME',
SQLOPS_DMExecSessions.[HOST_NAME] 'MACHINE_NAME',
OBJECT_NAME(SQLOPS_DMExecSessionst.objectid, 
SQLOPS_DMExecRequests.database_id) 
AS 'OBJECT_NAME',
SQLOPS_DMExecRequests.command as 'OPERATION',
SQLOPS_DMExecSessions.session_id as 'SPID' ,
SQLOPS_DMExecSessions.status as 'SPID_STATUS',
SQLOPS_DMExecRequests.blocking_session_id as 'SPID_BEING_BLOCKED',
SQLOPS_DMExecSessions.cpu_time as 'CPU_USAGE_IN_MS',
SQLOPS_DMExecSessions.reads as 'TOTAL_READS',
SQLOPS_DMExecSessions.writes as 'TOTAL_WRITES',
SQLOPS_DMExecConnections.last_write as 'LAST_WRITE',
SQLOPS_DMExecSessions.[program_name] as 'APPLICATION_NAME',
SQLOPS_DMExecRequests.wait_type as 'TYPE_OF_WAIT',
SQLOPS_DMExecRequests.wait_time as 'WAITING_TIME',
SQLOPS_DMExecRequests.last_wait_type as 'PREVIOUS_WAIT_TYPE',
SQLOPS_DMExecRequests.wait_resource as 'WAITING_ON_RESOURCE',
CASE SQLOPS_DMExecSessions.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS 'ISOLATION_LEVEL',
SUBSTRING(SQLOPS_DMExecSessionst.text, SQLOPS_DMExecRequests.statement_start_offset / 2,
( CASE WHEN SQLOPS_DMExecRequests.statement_end_offset = -1
THEN DATALENGTH(SQLOPS_DMExecSessionst.text)
ELSE SQLOPS_DMExecRequests.statement_end_offset
END - SQLOPS_DMExecRequests.statement_start_offset ) / 2)
AS 'EXECUTING_STATEMENT',
SQLOPS_DMExecQueryPlan.query_plan as 'EXECUTION_PLAN',
'Script provided by: HTTPS://SQLOPS.COM - 
THE MOST ADVANCED RISK AND HEALTH AUDIT FOR PRODUCTION DATABASES' 
as 'CODE_PROVIDER'
FROM sys.dm_exec_sessions SQLOPS_DMExecSessions
LEFT JOIN sys.dm_exec_requests SQLOPS_DMExecRequests
ON SQLOPS_DMExecSessions.session_id = SQLOPS_DMExecRequests.session_id
LEFT JOIN sys.dm_exec_connections SQLOPS_DMExecConnections
ON SQLOPS_DMExecSessions.session_id = SQLOPS_DMExecConnections.session_id
CROSS APPLY sys.dm_exec_sql_text(SQLOPS_DMExecRequests.sql_handle) SQLOPS_DMExecSessionst
CROSS APPLY sys.dm_exec_query_plan(SQLOPS_DMExecRequests.plan_handle) SQLOPS_DMExecQueryPlan
WHERE SQLOPS_DMExecSessions.session_id <> @@SPID
ORDER BY SQLOPS_DMExecSessions.session_id
END
GO

SQL Server Risk and Health Assessment for Production Databases

Note: Above practice is just one of the several thousands of audit checks performed by SQLOPS Risk and Health Assessment for Production Databases. Your investment is worth every penny! 100% risk-free with the money back guarantee.

Credits: https://sqlops.com/sql-server-risk-assessment/