sp_whopro™ SQL Server Activity Monitoring and Logging Stored Procedure


You can download the .zip file (about 50 KB) or the .txt file.

More info on the script can be found in our sp_whopro videos (Youtube).

In order to stay up-to-date on new sp_whopro script releases, subscribe to our Newsletter

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

Consulting Offer: Engage us for onsite or remote SQL Server Consulting (4 hours or more) and receive a 1 Year Complementary sp_whopro Enterprise License and a 1 Year Complementary SQLTest Professional License!

Version*

.zip(~50 KB)

.txt(~512 KB)

sp_whopro for SQL Azure V12 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2014 SP1 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2014 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2012 SP2 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2012 SP1 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2012 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 R2 SP3 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 R2 SP2 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 R2 SP1 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 R2 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 SP4 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 SP3 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 SP2 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 SP1 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2008 102 By downloading you agree to the EULA
sp_whopro for SQL Server 2005 SP4 102 By downloading you agree to the EULA
sp_whopro script header

/*
Copyright: 2013-2015 Ramesh Meyyappan, SQL Consulting GmbH, Munich, Germany, http://www.sqlworkshops.com.
The sp_whopro(TM) script is registered with US Copyright Office, Registration Number: TXu001920835.
Please email your comments, feedback and suggestions to Ramesh @ mailto:rmeyyappan@sqlworkshops.com, keep yourself up to date by subscribing to our newsletter at
https://newsletter.sqlworkshops.com/sp_whopro and use our support forum at http://www.sqldownload.com/forum/sp_whopro to check for answers and post your questions.
Connect with me in LinkedIn: http://de.linkedin.com/in/rmeyyappan, on twitter https://twitter.com/SQLWorkshops.
If you like sp_whopro, tell your friends and colleagues. Feel free to share the links in your blog and social media.
You acknowledge upon downloading or using the sp_whopro script that you have reviewed and agreed to all of the terms and conditions set forth in the
EULA (http://www.sqldownload.com/eula). If You do not agree with all of these terms and conditions, do not download or use sp_whopro. If you have
already downloaded the sp_whopro script and do not agree to the terms and conditions, please delete the sp_whopro script and immediately discontinue its usage.
You can download and use the sp_whopr(TM) script in accordance with the below described license types as long as the Copyright, License, About Us, rest of
the header information and the entire script remains intact without any alteration or modification; furthermore, sp_whopro must not be sold, re-licensed,
transferred or otherwise re-distributed, neither wholly nor partly, without SQL Consulting GmbH's written permission.
The sp_whopro script is provided on an "as is" basis without warranty of any kind, either expressed or implied.

License: The sp_whopro(TM) script has the following license model: "Free Community License", "Enterprise License", "Service Provider License" and
"Solution Provider License"; Please read the EULA for additional information @ www.sqldownload.com/eula.
"Free Community License" can be used for internal purposes (in any number of servers, including production servers), except in the following cases where you need an
"Enterprise License": a.) if you would like to download or deploy the sp_whopro script for other users in your company or organization, b.) if you want to engage a
third party consultant to analyze the collected data, c.) if you want to use a software, script or computer program from a third party or external consultant to process
the collected data or d.) if you wish to obtain email support.
"Free Community License" can also be used for automated processing of the data collected by the sp_whopro script, for analysis purposes or in order to set up alerts.

If you are a consultant or a service provider and wish to use the sp_whopro script as part of your services to a third party, you require a "Service Provider License".
If you are a software developer or a solution provider and wish to use the sp_whopro script in your products, wholly or partly, you require a "Solution Provider License".
The cost of the "Enterprise License" is $45/ year. The "Enterprise License" grants simultaneous access to all computers on one physical site/ geographic location.
The "Free Community License" is incompatible with "Enterprise License", "Service Provider License", "Solution Provider License" or with any third party usage.
A list of Licensed Service Providers and Solution Providers can be found at http://www.sqlworkshops.com/ListofLicensedServiceSolutionTrainingProviders.
Please contact us at mailto:license@sqlworkshops.com to acquire the appropriate license.
About Us:
We are a consulting company, SQL Consulting GmbH, based in Munich, Germany. We provide onsite and offsite SQL Server Performance Tuning and Troubleshooting
Consulting. We can prepare SQL Server Performance, Load, Stress or Unit Test based on your workload. We conduct SQL Server Health Check and provide a list of
recommendations to improve performance for our customers. We provide SQL Server Performance Monitoring and Tuning Hands-on Training for developers and DBAs.
Please contact us at mailto:support@sqlworkshops.com or visit http://www.sqlworkshops.com if you are interested in any of our services. You will obtain a
complementary 1 Year "Enterprise License" if you engage us for SQL Server Consulting (4 hours or more). For customer feedback from previous engagements:
http://www.sqlworkshops.com/feedback, workshop agenda: http://www.sqlworkshops.com/agenda, workshop schedule: http://www.sqlworkshops.com/schedule.
Our Mission: Empower customers to fully realize the performance potential of Microsoft SQL Server without increasing the total cost of ownership (TCO) and
achieve high customer satisfaction in every consulting engagement and workshop delivery.
Download SQLTest, our SQL Server Performance, Load, Stress and Unit Test Tool from http://www.sqltest.org
Check out our videos on sp_whopro, SQL Server and SQLTest @ http://www.sqlvideo.com
For usage help: 'exec sp_whopro 'help'', for documentation refer to: http://www.sqldownload.com/documentation
The stored procedure creation script is custom generated based on SQL Server Version and Service Pack. First execution of sp_whopro stored procedure may be slow, subsequent executions should be fast due to caching. To reduce execution time and disk space usage with 'SAVE' option, it is not recommended to collect 'ALL' columns. Dynamic SQL statements are only used in the following few cases: to collect DBCC INPUTBUFFER of sessions and requests, to resolve object and index names for locks, wait_resource and resource_description info, to resolve schema name, to create and to alter table and to save sp_whopro data with 'SAVE' option, to select sp_whopro result based on custom column set and custom order by clause. sp_whopro parameters are not directly used in dynamic SQL statements to avoid SQL Injection attacks. Short variable names are used to reduce the size of the stored procedure. With version independent script generator, sp_whopro stored procedure is easy to enhance. If you have additional inputs for making sp_whopro more useful or have special needs, let me know. Thanks for using sp_whopro.
This release of sp_whopro(TM) is for Microsoft SQL Server 2014 (SP1).
sp_whopro(TM) version 102, for latest release of sp_whopro please check http://www.sqldownload.com/download. Build id: 98F52069-BEBA-08DE-05C4-CC93DB967AB6 ChangeLog: http://www.sqldownload.com/changelog.
*/
sp_whopro help

/* For latest build of sp_whopro, help and documentation, refer to http://www.sqldownload.com
sp_whopro [@sessions =] 'option[, option]' | NULL, [@columns =] 'option[, option]' | 'column[, column]' | NULL, [@order_by =] 'column[, column]' | 'help' | NULL, [@save_to_database =] 'database_name[.schema_name]' | NULL, [@datetime = OUTPUT] 'ctime' | NULL, [@datetime_to =] 'ctime' | NULL, [@execution_time_report_threshold =] milliseconds | NULL
[@sessions =] 'option[, option]' | NULL
The following options are a comma separated list, can be used to filter sessions as shown below:
Example ACTIVE, SAVE
ACTIVE
Include Active sessions.
NULL
Include Active and Blocking sessions.
WAIT
Include Waiting sessions.
BLOCK
Include Blocked and Blocking sessions.
BLOCKED
Include Blocked sessions.
BLOCKING
Include Blocking sessions.
CURSOR
Sessions that have open cursors.
SP_WHOPRO
Include sp_whopro's own session.
ALL
Include All sessions.
SAVE
Save output.
NODDL
To not to perform DDL statements like create or alter table part of 'SAVE' option,
can reduce execution time, missing tables or columns will lead to errors.
NOOUTPUT
To not to display results, to be used in combination with 'SAVE' option when run as a job or
to get the select statement when @datetime is supplied.
When 'not in', '>', '<', '=' or 'not like' is specified include All sessions
The following 'in' / 'not in' options are a comma separated list, can be used to filter sessions as shown below:
Example session_id IN (57, 58)
Example database_name IN ('SQLDev', 'SQLTest'), session_id NOT IN (57, 58)
session_id [IN | NOT IN] (?, [?])
List of session ids from sys.dm_exec_sessions.
is_user_process [IN | NOT IN] (?, [?])
Is user process from sys.dm_exec_sessions, 0 for system sessions, 1 for user sessions.
database_id [IN | NOT IN] (?, [?])
List of database ids from sys.dm_exec_sessions.
database_name [IN | NOT IN] ('?', ['?'])
List of database names from sys.dm_exec_sessions.
status [IN | NOT IN] ('?', ['?'])
List of requests status from sys.dm_exec_requests.
sql_handle [IN | NOT IN] (?, [?])
List of SQL handles from sys.dm_exec_requests.
plan_handle [IN | NOT IN] (?, [?])
List of Plan handles from sys.dm_exec_requests.
query_hash [IN | NOT IN] (?, [?])
List of query hash from sys.dm_exec_requests.
query_plan_hash [IN | NOT IN] (?, [?])
List of query plan hash from sys.dm_exec_requests.
Additional in / not in options: host_process_id (from sys.dm_exec_sessions), context_info (from sys.dm_exec_sessions),
transaction_isolation_level (from sys.dm_exec_sessions ), group_id (from sys.dm_exec_sessions), node_affinity (from
sys.dm_exec_connections), connection_id (from sys.dm_exec_connections), most_recent_sql_handle (from sys.dm_exec_connections),
request_id (from sys.dm_exec_requests), command (from sys.dm_exec_requests), statement_start_offset (from sys.dm_exec_requests),
statement_end_offset (from sys.dm_exec_requests), blocking_session_id (from sys.dm_exec_requests), transaction_id (from
sys.dm_exec_requests), scheduler_id (from sys.dm_exec_requests), executing_managed_code (from sys.dm_exec_requests).
The following '>' / '<' / '=' range options can be used to filter sessions as shown below:
Example wait > 1000, wait_time < 20000
Example total_elapsed_time > 10000, cpu_time > 1000
wait_time [> | < | =] ?
Wait time from sys.dm_exec_requests.
cpu_time [> | < | =] ?
CPU time from sys.dm_exec_requests.
total_elapsed_time [> | < | =] ?
Total elapsed time (duration) from sys.dm_exec_requests.
reads [> | < | =] ?
Reads from sys.dm_exec_requests.
logical_reads [> | < | =] ?
Logical Reads from sys.dm_exec_requests.
Additional range options: login_time (from sys.dm_exec_sessions), open_transaction_count (from sys.dm_exec_sessions),
num_reads (from sys.dm_exec_connections), num_writes (from sys.dm_exec_connections), writes (from sys.dm_exec_requests),
row_count (from sys.dm_exec_requests), nest_level (from sys.dm_exec_requests), granted_query_memory (from sys.dm_exec_requests).
The following 'like' / 'not like' options can be used to filter sessions as shown below:
Example wait_type LIKE 'LCK%'
Example program_name LIKE 'SQLTest%', wait_type NOT LIKE 'ASYNC_NETWORK_IO'
host_name [LIKE | NOT LIKE ] '?'
Host Name from sys.dm_exec_sessions.
program_name [LIKE | NOT LIKE ] '?'
Program Name from sys.dm_exec_sessions.
login_name [LIKE | NOT LIKE ] '?'
Login Name from sys.dm_exec_sessions.
wait_type [LIKE | NOT LIKE ] '?'
Wait type from sys.dm_exec_requests.
wait_resource [LIKE | NOT LIKE ] '?'
Wait resource from sys.dm_exec_requests.
Additional like / not like options: nt_domain (from sys.dm_exec_sessions), nt_user_name (from sys.dm_exec_sessions),
[@columns =] 'option[, option]' | 'column[, column]' | NULL
Example login_name, CPU, WAIT, DETAILED
Example r.cpu_time r.total_elapsed_time, wait_type, wait_time
The following options are a comma separated list, can be used to choose output columns as shown below:
LIMITED
Outputs a small set of columns.
DEFAULT
Outputs medium set of columns.
NULL
Outputs medium set of columns.
DETAILED
Outputs large set of columns when specified alone otherwise outputs large set of column associated to options chosen.
ALL
Outputs all columns, it is recommended to avoid 'ALL' option, to reduce execution time and storage overhead.
CPU | CPU_DETAILED | PROCESSOR | PROCESSOR_DETAILED
Outputs CPU related columns.
IO | IO_DETAILED | DISK | DISK_DETAILED | STORAGE | STORAGE_DETAILED
Outputs IO related columns.
MEMORY | MEMORY_DETAILED
Outputs memory related columns.
NETWORK | NETWORK_DETAILED
Outputs network related columns.
WAIT | WAIT_DETAILED | WAITS | WAITS_DETAILED
Outputs wait related columns.
BLOCK | BLOCK_DETAILED | BLOCKED | BLOCKED_DETAILED | BLOCKING | BLOCKING_DETAILED
Outputs blocking related columns.
LOCK | LOCK_DETAILED | LOCKS | LOCKS_DETAILED
Outputs lock related columns for blocked and blocking sessions.
ALLLOCKS | ALLLOCKS_DETAILED
Outputs lock related columns for all sessions.
TRAN | TRAN_DETAILED | TRANS | TRANS_DETAILED | TRANSACTION | TRANSACTION_DETAILED | TRANSACTIONS | TRANSACTIONS_DETAILED
Outputs transaction related columns.
SQL | SQL_DETAILED
Outputs sql text related columns.
SQLASTEXT
Outputs selected SQL text columns as text instead of XML. Without this option
selected SQL text columns are output as XML, non-printable characters are replaced with [nchar(?)]
PLAN | PLAN_DETAILED | PLANS | PLANS_DETAILED
Outputs execution plan related columns.
QUERY_PROFILES | QUERY_PROFILES_DETAILED | QUERY_PROFILE | QUERY_PROFILE_DETAILED
Outputs query profile related columns.
STATS | STATS_DETAILED | STATISTICS | STATISTICS_DETAILED
Outputs query, procedure and trigger statistics related columns.
TEMPDB | TEMPDB_DETAILED
Outputs tempdb related columns.
CURSOR | CURSOR_DETAILED | CURSORS | CURSORS_DETAILED
Outputs cursor related columns.
TASK | TASK_DETAILED | TASKS | TASKS_DETAILED
Outputs task related columns.
WORKER | WORKER_DETAILED | WORKERS | WORKERS_DETAILED
Outputs worker related columns.
THREAD | THREAD_DETAILED | THREADS,THREADS_DETAILED
Outputs thread related columns.
SESSION | SESSION_DETAILED | SESSIONS | SESSIONS_DETAILED
Outputs session related columns.
CONNECTION | CONNECTION_DETAILED | CONNECTIONS | CONNECTIONS_DETAILED
Outputs connection related columns.
REQUEST | REQUEST_DETAILED | REQUESTS | REQUESTS_DETAILED
Outputs request related columns.
PLAN_ATTRIBUTE | PLAN_ATTRIBUTE_DETAILED | PLAN_ATTRIBUTES | PLAN_ATTRIBUTES_DETAILED
Outputs plan attribute related columns.
INPUTPARAMETER | INPUTPARAMETERS
Outputs @sessions, @columns and @order_by parameters.
CUSTOM | CUSTOM_DETAILED
Outputs custom columns, contact Ramesh Meyyappan at mailto:rmeyyappan@sqlworkshops.com for details.
input_buffer
Outputs input buffer for requests from sys.dm_exec_requests based on session_id and request_id,
for sessions from sys.dm_exec_sessions based on session_id.
s.input_buffer
Outputs input buffer for sessions from sys.dm_exec_sessions based on session_id.
r.input_buffer
Outputs input buffer for requests from sys.dm_exec_requests based on session_id and request_id.
text_object_name
Outputs object name for requests from sys.dm_exec_query_text based on sys.dm_exec_requests's sql_handle,
for connections from sys.dm_exec_query_text based on sys.dm_exec_connections's most_recent_sql_handle.
text
Outputs text for requests from sys.dm_exec_query_text based on sys.dm_exec_requests's sql_handle,
for connections from sys.dm_exec_query_text based on sys.dm_exec_connections's most_recent_sql_handle.
c.text_object_name
Outputs object name for connections from sys.dm_exec_query_text based on sys.dm_exec_connections's most_recent_sql_handle.
c.text
Outputs text for connections from sys.dm_exec_query_text based on sys.dm_exec_connections's most_recent_sql_handle.
r.text_object_name
Outputs object name for requests from sys.dm_exec_query_text based on sys.dm_exec_requests's sql_handle.
r.text
Outputs text for requests from sys.dm_exec_query_text based on sys.dm_exec_requests's sql_handle.
statement_object_name, r.statement_object_name
Outputs object name from sys.dm_exec_query_text based on sys.dm_exec_requests's sql_handle.
statement, r.statement
Outputs statement, substring of text, for requests from sys.dm_exec_query_text based on sys.dm_exec_requests's sql_handle,
statement_start_offset and statement_end_offset.
query_plan, r.query_plan
Outputs query plan for requests from sys.dm_exec_query_plan based on sys.dm_exec_requests's plan_handle.
query_plan_checksum, r.query_plan_checksum
Outputs query plan checksum, based on query plan, used while saving to avoid duplicate entries.
text_query_plan, r.text_query_plan
Outputs text query plan as xml for requests from sys.dm_exec_text_query_plan based on sys.dm_exec_requests's plan_handle,
statement_start_offset and statement_end_offset.
text_query_plan_as_text, r.text_query_plan_as_text
Outputs text query plan as text for requests from sys.dm_exec_text_query_plan based on sys.dm_exec_requests's plan_handle,
statement_start_offset and statement_end_offset. To avoid error 6335, XML datatype instance has too many levels of nested nodes.
Maximum allowed depth is 128 levels.
full_text_query_plan, r.full_text_query_plan
Outputs complete text query plan as xml for requests from sys.dm_exec_text_query_plan based on sys.dm_exec_requests's plan_handle
with statement_start_offset as 0 and statement_end_offset -1.
full_text_query_plan_as_text, r.full_text_query_plan_as_text
Outputs complete text query plan as text for requests from sys.dm_exec_text_query_plan based on sys.dm_exec_requests's plan_handle
with statement_start_offset as 0 and statement_end_offset -1. To avoid error 6335, XML datatype instance has too many levels of nested nodes.
Maximum allowed depth is 128 levels.
plan_attributes_info, r.plan_attributes_info
Outputs plan attributes for requests from sys.dm_exec_query_plan based on sys.dm_exec_requests's plan_handle.
ctime, s.ctime, c.ctime, r.ctime
Outputs collection time, available with 'SAVE' option or when @datetime is not NULL.
servername, s.servername, c.servername, r.servername
Outputs server name where data was collected, available with 'SAVE' option or when @datetime is not NULL.
The following columns are a comma separated list, can be used to choose output columns (SSMS might truncate the below output to 256 characters with 'Results to Text', use 'Results to Grid' instead):
session_id, login_time, host_name, program_name, host_process_id, client_version, client_interface_name, security_id, login_name, nt_domain, nt_user_name, status, context_info, cpu_time, memory_usage, total_scheduled_time, total_elapsed_time, endpoint_id, last_request_start_time, last_request_end_time, reads, writes, logical_reads, is_user_process, text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_isolation_level, lock_timeout, deadlock_priority, row_count, prev_error, original_security_id, original_login_name, last_successful_logon, last_unsuccessful_logon, unsuccessful_logons, group_id, database_id, authenticating_database_id, open_transaction_count, session_id, most_recent_session_id, connect_time, net_transport, protocol_type, protocol_version, endpoint_id, encrypt_option, auth_scheme, node_affinity, num_reads, num_writes, last_read, last_write, net_packet_size, client_net_address, client_tcp_port, local_net_address, local_tcp_port, connection_id, parent_connection_id, most_recent_sql_handle, session_id, request_id, start_time, status, command, sql_handle, statement_start_offset, statement_end_offset, plan_handle, database_id, user_id, connection_id, blocking_session_id, wait_type, wait_time, last_wait_type, wait_resource, open_transaction_count, open_resultset_count, transaction_id, context_info, percent_complete, estimated_completion_time, cpu_time, total_elapsed_time, scheduler_id, task_address, reads, writes, logical_reads, text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_isolation_level, lock_timeout, deadlock_priority, row_count, prev_error, nest_level, granted_query_memory, executing_managed_code, group_id, query_hash, query_plan_hash, statement_sql_handle, statement_context_id, session_id, job_info, is_blocker, is_head_blocker, blocked_session_ids, input_buffer_checksum, input_buffer_valid_flag, lock_count, lock_owner_id, locks_info, transaction_begin_time, transaction_log_record_count, transaction_log_bytes_used, transaction_info, session_tempdb_usage_kb, session_max_tempdb_usage_kb, session_tempdb_usage_info, cursor_count, max_cursor_dormant_duration, cursors_info, waiting_tasks_wait_types, waiting_tasks_max_wait_duration_ms, waiting_tasks_blocking_session_ids, waiting_tasks_valid_flag, waiting_tasks_info, service_broker_info, sp_whopro_inputparameters, custom_info, session_id, connection_id, query_stats_info, procedure_stats_info, trigger_stats_info, session_id, request_id, connection_id, is_blocked, is_dead_locked, blocker_session_ids, head_blocker_session_ids, input_buffer_checksum, input_buffer_valid_flag, wait_resource_info, lock_count, lock_owner_id, locks_info, transaction_begin_time, transaction_info, task_tempdb_usage_kb, task_max_tempdb_usage_kb, task_tempdb_usage_info, query_memory_grants_requested_memory_kb, query_memory_grants_granted_memory_kb, query_memory_grants_used_memory_kb, query_memory_grants_max_used_memory_kb, query_memory_grants_wait_time_ms, query_memory_grants_ideal_memory_kb, query_memory_grants_info, query_profiles_cpu_time_ms, query_profiles_logical_read_count, query_profiles_physical_read_count, query_profiles_read_ahead_count, query_profiles_write_page_count, query_profiles_info, query_stats_info, procedure_stats_info, trigger_stats_info, cached_plans_info, tasks_count, tasks_context_switches, tasks_pending_io, tasks_io_size_kb, read_bytes_per_second, tasks_ios_per_second, tasks_info, workers_info, threads_info, query_plan_text_checksum, custom_info
s.session_id, s.login_time, s.host_name, s.program_name, s.host_process_id, s.client_version, s.client_interface_name, s.security_id, s.login_name, s.nt_domain, s.nt_user_name, s.status, s.context_info, s.cpu_time, s.memory_usage, s.total_scheduled_time, s.total_elapsed_time, s.endpoint_id, s.last_request_start_time, s.last_request_end_time, s.reads, s.writes, s.logical_reads, s.is_user_process, s.text_size, s.language, s.date_format, s.date_first, s.quoted_identifier, s.arithabort, s.ansi_null_dflt_on, s.ansi_defaults, s.ansi_warnings, s.ansi_padding, s.ansi_nulls, s.concat_null_yields_null, s.transaction_isolation_level, s.lock_timeout, s.deadlock_priority, s.row_count, s.prev_error, s.original_security_id, s.original_login_name, s.last_successful_logon, s.last_unsuccessful_logon, s.unsuccessful_logons, s.group_id, s.database_id, s.authenticating_database_id, s.open_transaction_count
c.session_id, c.most_recent_session_id, c.connect_time, c.net_transport, c.protocol_type, c.protocol_version, c.endpoint_id, c.encrypt_option, c.auth_scheme, c.node_affinity, c.num_reads, c.num_writes, c.last_read, c.last_write, c.net_packet_size, c.client_net_address, c.client_tcp_port, c.local_net_address, c.local_tcp_port, c.connection_id, c.parent_connection_id, c.most_recent_sql_handle
r.session_id, r.request_id, r.start_time, r.status, r.command, r.sql_handle, r.statement_start_offset, r.statement_end_offset, r.plan_handle, r.database_id, r.user_id, r.connection_id, r.blocking_session_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.open_transaction_count, r.open_resultset_count, r.transaction_id, r.context_info, r.percent_complete, r.estimated_completion_time, r.cpu_time, r.total_elapsed_time, r.scheduler_id, r.task_address, r.reads, r.writes, r.logical_reads, r.text_size, r.language, r.date_format, r.date_first, r.quoted_identifier, r.arithabort, r.ansi_null_dflt_on, r.ansi_defaults, r.ansi_warnings, r.ansi_padding, r.ansi_nulls, r.concat_null_yields_null, r.transaction_isolation_level, r.lock_timeout, r.deadlock_priority, r.row_count, r.prev_error, r.nest_level, r.granted_query_memory, r.executing_managed_code, r.group_id, r.query_hash, r.query_plan_hash, r.statement_sql_handle, r.statement_context_id
s.job_info, s.is_blocker, s.is_head_blocker, s.blocked_session_ids, s.input_buffer_checksum, s.input_buffer_valid_flag, s.lock_count, s.lock_owner_id, s.locks_info, s.transaction_begin_time, s.transaction_log_record_count, s.transaction_log_bytes_used, s.transaction_info, s.session_tempdb_usage_kb, s.session_max_tempdb_usage_kb, s.session_tempdb_usage_info, s.cursor_count, s.max_cursor_dormant_duration, s.cursors_info, s.waiting_tasks_wait_types, s.waiting_tasks_max_wait_duration_ms, s.waiting_tasks_blocking_session_ids, s.waiting_tasks_valid_flag, s.waiting_tasks_info, s.service_broker_info, s.sp_whopro_inputparameters, s.custom_info
c.query_stats_info, c.procedure_stats_info, c.trigger_stats_info
r.is_blocked, r.is_dead_locked, r.blocker_session_ids, r.head_blocker_session_ids, r.input_buffer_checksum, r.input_buffer_valid_flag, r.wait_resource_info, r.lock_count, r.lock_owner_id, r.locks_info, r.transaction_begin_time, r.transaction_info, r.task_tempdb_usage_kb, r.task_max_tempdb_usage_kb, r.task_tempdb_usage_info, r.query_memory_grants_requested_memory_kb, r.query_memory_grants_granted_memory_kb, r.query_memory_grants_used_memory_kb, r.query_memory_grants_max_used_memory_kb, r.query_memory_grants_wait_time_ms, r.query_memory_grants_ideal_memory_kb, r.query_memory_grants_info, r.query_profiles_cpu_time_ms, r.query_profiles_logical_read_count, r.query_profiles_physical_read_count, r.query_profiles_read_ahead_count, r.query_profiles_write_page_count, r.query_profiles_info, r.query_stats_info, r.procedure_stats_info, r.trigger_stats_info, r.cached_plans_info, r.tasks_count, r.tasks_context_switches, r.tasks_pending_io, r.tasks_io_size_kb, r.read_bytes_per_second, r.tasks_ios_per_second, r.tasks_info, r.workers_info, r.threads_info, r.query_plan_text_checksum, r.custom_info
[@order_by =] 'column[, column]' | 'help' | NULL
When NULL, if @sessions parameter includes 'SAVE' option, ordered by s.ctime, s.session_id else ordered by s.session_id.
When 'help', outputs source select statement to help choose column aliases for use in order by.
When not NULL, ordered by column(s) specified
Comma separated list of column aliases, column[, column].
Can contain any column aliases that are present in select clause, expressions are not allowed to prevent SQL Injection attacks.
Example r.cpu_time desc
Example s.login_name
Example rs.database_id
Example rs.database_name, s.login_name
[@save_to_database =] 'database_name[.schema_name]' | NULL
Can be used to save and retrieve data from a specific database and optionally specific schema:
When NULL, saved in current database in dbo schema.
When not NULL, saved or retrieve from specified database and optionally specified schema
When enclosed with delimiter '[' and ']', can contain special characters
Example @save_to_database = 'sp_whopro_db'
Example @save_to_database = 'sp_whopro_db.sqlws'
Example @save_to_database = '[sp whopro db]'
Example @save_to_database = '[sp whopro db].[sql ws]'
[@datetime = OUTPUT] 'ctime' | NULL
When NULL and @sessions parameter includes 'SAVE' option, outputs ctime, the collection time.
When not NULL, returns previously saved data where ctime = @datetime
Example @datetime = 2015-09-15 09:00:00.000
Returns previously saved data where ctime = 2015-09-15 09:00:00.000.
Can be combined with @columns parameter to choose columns displayed.
When @sessions parameter includes 'NOOUTPUT' option, outputs the select statement instead of the rseults.
[@datetime_to =] 'ctime' | NULL
When not NULL, returns previously saved data where ctime between @datetime and @datetime_to
Example @datetime_to = 2015-09-17 12:00:00.000
Returns previously saved data where ctime between 2015-09-15 09:00:00.000 and 2015-09-17 12:00:00.000.
[@execution_time_report_threshold =] milliseconds | NULL
When not NULL, prints sp_whopro internal statements that exceeds the @execution_time_report_threshold value,
this is to trace long executing sp_whopro internal queries
When NULL, @execution_time_report_threshold is 250 milliseconds
Example @execution_time_report_threshold = 100
Prints sp_whopro internal statements that exceeds 100 milliseconds execution time.
Usage Examples:
--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'
--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

Deleting old data:
Tables sqslws_dm_exec_requests, sqslws_dm_exec_connections and sqslws_dm_exec_sessions have ctime, the collection time column that can be used for data purging.
Tables sqslws_input_buffer, sqslws_dm_exec_sql_text, sqslws_dm_exec_query_plan, sqslws_dm_exec_text_query_plan, sqslws_dm_exec_query_plan_checksum and sqslws_dm_exec_plan_attributes have ctime, the collection time, in addition they have rtime, the last reference time and rcount, the reference count column that can be used for data purging.

*/

SQLTest- SQL Server Performance, Load, Stress & Unit Test Tool

Download SQLTest

More info on SQLTest

Extended EventsScript Generator

SQL Server Consulting Services | SQL Server Performance Tuning Workshops | Feedback | SQL Server Videos