sp_whopro 

SQL Server Activity Monitoring and Logging Stored Procedure

If you have used the sp_who or sp_who2 script before, you will find sp_whopro very helpful, sp_whopro is versatile and easy to use.

Download sp_whopro

You can also find info about the script in our sp_whopro videos  (Youtube).

sp_whopro lets you monitor the current activity of a SQL Server Instance and at the same time allows you to save the activity to database for later analysis. You can collect data related to  CPU, IO, MEMORY, NETWORK, WAIT, BLOCKING, LOCK, TRANSACTION, TEMPDB, CURSOR, SQL, PLAN, STATS, QUERY_PROFILE, SESSION, CONNECTION, REQUEST, TASK, WORKER and THREAD.

The sp_whopro script is customized for every SQL Server Version and Service Pack Microsoft released from SQL Server 2005 SP4 to SQL Server 2014. You can import current activity from many SQL Server Instances into a single central instance, server name and ctime, the data collection time is part of the collection.

 

--Example parameters to monitor waits, blocking and corresponding locks:
exec sp_whopro 'active, blocking','sql, wait, block, locks, limited'
go
--Example parameters to monitor waits, blocking and corresponding locks and to save the data to current database:
exec sp_whopro 'active, blocking, save','sql, wait, block, locks, limited'
go
--Example parameters to monitor waits, blocking and corresponding locks and to save the data to ‘sp_whopro_db’ database in a SQL Agent job without output:
exec sp_whopro 'active, blocking, save, nooutput','sql, wait, block, locks, limited',null,'sp_whopro_db'
go
--Example parameters to retrieve saved data from ‘sp_whopro_db’ database:
exec sp_whopro null,'sql, wait, block, locks, limited',null,'sp_whopro_db','2014-10-16 08:24:13.270','2014-10-18 08:24:13.270'
go
--Example parameters to output the select statement used to retrieve saved data from ‘sp_whopro_db’ database:
exec sp_whopro 'nooutput','sql, wait, block, locks, limited',null,'sp_whopro_db','2014-10-16 08:24:13.270','2014-10-18 08:24:13.270'
go

Register for the 3-Day-SQL Server Performance Monitoring & Tuning Hands-on Workshop in Munich! Or check out other Workshop Locations~

Credits

We would like to thank everyone who took the time to test the beta version and give us feedback; We would especially like to thank Tatjana Petrovic, Kapsch TrafficCom, Austria, for her efforts. 

   Subscribe to our Newsletter

VISIT ALSO OUR SISTER PAGES