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.
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
Attend one of our upcoming SQL Server Performance Tuning Hands-on Workshops and receive a complementary 1 Year SQLTest Professional License!
VISIT ALSO OUR SISTER PAGES
