[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.
The session id of the request (from sys.dm_exec_sessions)
The request status (from sys.dm_exec_requests)
The command being executed (from sys.dm_exec_requests)
Summary of the waiting type, resource and duration (from sys.dm_os_waiting_tasks)
Database name, this column will only show data if running with @lightweight parameter =0 (from dm_exec_plan_attributes)
TSQL query from cross applying dm_exec_sql_text
When SQLQuery column shows FETCH API_CURSOR, this column should have the original statement
Wait duration (from sys.dm_os_waiting_tasks)
The blocking session if you have a blocking chain, see blockChain column
Shows the blocking chain using the format <sessionID>--><blockedSessionID>. 0 means there's no blocking
Total elapsed time in ms
Physical reads converted to KB
Logical writes in KB
Logical reads in KB
Granted memory in KB
CPU time in ms
The host name (the server originating the request)
The program name
Query plan in XML. this column will only show data if running with @lightweight parameter =0 (from dm_exec_plan_attributes)
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
Enjoy your performance troubleshooting.