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.
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
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.
