Get Current Activity in SQL Server instance
[Update: Version 2.0 uploaded with some bug fixes]
When troubleshooting customer's performance issue that's happening at the moment, we have many tools out of the box. The good old SQL traces that are not needed for the majority of the cases and if it's needed then my first option would ne XE if we are on 2012. Also there are many DMV that we can use and these are the building blocks that SQL Server Management Studio Activity Monitor is built on. However to get the best response and the result sets that you are only interested in you need to use your own script.
I used to write an ad-hoc script every time I'm troubleshooting something based on the situation. There are some great scripts online that covers what I need but I wanted to have my own implementation as simple and function-based so one can use it with filters easily. If you are looking for more comprehensive script, I'd refer to Adam Machanic's whoisactive stored procedure. I don't have much experience using it but he's the first one who used the idea of printing the TSQL code as XML –as far as I know- so will be easier to view in SSMS by clicking on it. I'm using the same idea as well.
Eventually I wrote the function dbo.GetCurrentActivity. I'm using it for few months now and if you are a customer whom I promised that it will be uploaded to my blog soon, please accept my apology and find the details below J
The function will show only the running/runnable or suspended requests. If we have a session that does nothing then it will not be seen by this function.
The function make it easy for these scenarios
-Waiting on any waiting type as it shows the waiting summary column
-Blocking chain as it shows the complete chain so you can get the root blocker
-It shows the original TSQL when having FETCH API_CURSOR operation
The output of the function is detailed in the table below.
session_id SMALLINT |
The session id of the request (from sys.dm_exec_sessions) |
status NVARCHAR(60) |
The request status (from sys.dm_exec_requests) |
command NVARCHAR(64) |
The command being executed (from sys.dm_exec_requests) |
Waiting_summary NVARCHAR(200) |
Summary of the waiting type, resource and duration (from sys.dm_os_waiting_tasks) |
database_name NVARCHAR(128) |
Database name, this column will only show data if running with @lightweight parameter =0 (from dm_exec_plan_attributes) |
SQLQuery XML |
TSQL query from cross applying dm_exec_sql_text |
CursorQuery XML |
When SQLQuery column shows FETCH API_CURSOR, this column should have the original statement |
wait_duration_ms BIGINT |
Wait duration (from sys.dm_os_waiting_tasks) |
blocking_session_id SMALLINT |
The blocking session if you have a blocking chain, see blockChain column |
BlockChain VARCHAR(200) |
Shows the blocking chain using the format <sessionID>--><blockedSessionID>. 0 means there's no blocking |
elapsed_time_ms BIGINT |
Total elapsed time in ms |
physical_reads_KB BIGINT |
Physical reads converted to KB |
logical_writes_KB BIGINT |
Logical writes in KB |
logical_reads_KB BIGINT |
Logical reads in KB |
granted_query_memory_KB INT |
Granted memory in KB |
cpu_time_ms INT |
CPU time in ms |
host_name NVARCHAR(256) |
The host name (the server originating the request) |
program_name NVARCHAR(256) |
The program name |
query_plan XML |
Query plan in XML. this column will only show data if running with @lightweight parameter =0 (from dm_exec_plan_attributes) |
fn_version VARCHAR(10) |
To keep track of the current version. If you faced any issues with the function please reply with the version number you have. |
To setup the function, execute the script file from the location below. The function will be installed by default on the master database. Then from any other database you can call
SELECT * FROM master.dbo.GetCurrentActivity(1); --with lightweight parameter =1, quick execution
SELECT * FROM master.dbo.GetCurrentActivity(0); --with lightweight parameter =0, slower execution but more details.
The complete script can be found here
This blog post is cross posted on
https://blogs.msdn.com/b/mosharaf/
Enjoy your performance troubleshooting.
~Mohamed