SP_What is smarter than SP_Who2

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

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/

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…